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:
|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.