Design a locking granularity level

May include but is not limited to: locking hints, memory consumption

SQL Server has a multigranural locking feature, which lets you lock resources on different levels. Here’s a brief table of them:

Resource Description
RID A row identifier to lock rows within a heap.
KEY A row lock within an index.
PAGE A whole page locked in the database (8 KB).
EXTENT 8 continuous pages.
HoBT A heap or B-tree, depending on the existence of a clustered index.
TABLE An entire table locked down.
FILE A whole database file.
APPLICATION An application-specified resource.
METADATA Locks in metadata.
ALLOCATION_UNIT An allocation unit.
DATABASE An entire database.

 

As common sense suggest there’s a certain trade-off involved when you deal with locks. You can choose between memory consumption and performance. The more granular level of locking – the more memory used to maintain your locks, but the higher traffic available in your database.

For example, you can lock down several rows in a table, and provide access for other rows. This obviously needs more memory to implement, but other rows can be updated in your table when needed. On the other edge, you can lock down the whole database for one operation – minimal memory, minimal performance. The database engine is certainly smart enough to figure out what and how to lock, but if you need so, you can give locking hints to it. But before we check out these hints, let’s see the isolation levels available to use:

  • READ UNCOMMITED: the lowest level, allows any evil, including nonrepeatable reads, dirty reads and phantom rows. No locks are maintained.
  • READ COMMITED:  data can be changed during a transaction, so nonrepeatable reads and phantom rows can occur. The default level.
  • REPEATABLE READ: allows phantom rows(inserts in an uncommitted state).
  • SERIALIZABLE: places a range lock on the data, preventing any modification on it. Any read during the transaction returns the state before the transaction was run, or after that.

Now that you see what we are dealing with, here’s a list of the locking hints:

  • HOLDLOCK: holds a shared lock until the end of the given transaction, instead of releasing the resources as soon as they aren’t needed. An equivalent of SERIALIZABLE.
  • NOLOCK: only usable in a SELECT statement, allowing dirty reads, the same level of READUNCOMMITED.
  • PAGLOCK: use page locks where a single table lock would be taken.
  • READCOMMITED: the default isolation level – running READ COMMITED.
  • READPAST: applies to SELECT statements, skipping locked rows in a read (only row-level locks).
  • REPEATABLEREAD: a lock level equivalent to the REPEATABLE READ level.
  • ROWLOCK: use row level locks instead of table or page level locks.
  • TABLOCK: use a table-level lock instead of more granular locks.
  • TABLOCKX: use an exclusive lock on the table, preventing readings and writings.
  • UPDLOCK: use update locks instead of shared locks. Allowing reads while making sure to update it later without it being changed.
  • XLOCK: use an exclusive lock on all data included in the statement. You can use it along with TABLOCK or PAGLOCK.

One thought on “Design a locking granularity level

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s