Locking in SQL Server 2005

Depending on the Transaction Isolation Level set for your transaction, your queries will request various locks in order to ensure the correct consistency behaviors. I'd like to go over some of the different types of locks that may be issued as well as explain some of their differences.

Shared Locks
Shared locks are acquired automatically when data is read. Shared locks can be held on a table, page, index key, or individual row. Many processes can hold shared locks on the same data, but no process can acquire an exclusive lock on data that has a shared lock on it (unless it is the only process holding a shared lock). Normally shared locks are released as soon as the data is read, however this can be changed via query hints or depending on the isolation level

Exclusive Locks
Exclusive locks are acquired automatically when data is modified with an insert, update or delete operation. Only one process at a time can hold an exclusive lock on a particular data resource, no other locks of any kind can be acquired once an exclusive lock is held. Exclusive locks are held until the end of the transaction: this means that changed data is not available to any other process until the current transaction commits or rollsback

Update Locks
Update locks are acquired when the server executes a data modification operation but first needs to search the table to find the resource that will be modified. An update lock is not sufficient to allow you to change the data; all modifications require that the data resource being modified have an exclusive lock. An update lock acts as a serialization gate to queue future requests for the exclusive lock (many processes can hold shared locks for a resource but only one process can hold an update lock). As long as a process holds an update lock on a resource no other process can acquire an update lock or an exclusive lock for that resource; instead, another process requesting an update or exclusive lock for the same resource must wait. The process holding the update lock can convert in into an exclusive lock on the resource because the update lock prevents lock incompatibility with any other processes. Update locks can also be known as “intent-to-update-locks.” The reason this is important is because serializing access for the exclusive lock lets you avoid conversion deadlocks. Update locks are held until the end of the transaction or until they are converted into an exclusive lock.

Intent Locks
Intent locks are not a separate mode of locking; they are a qualifier to the modes mentioned above: you can have intent shared locks, intent exclusive locks, and intent update locks. Because SQL Server can acquire locks at different levels of granularity, a mechanism is required to indicate that a component of a resource is already locked.

Special Lock Modes (Schema stability locks, schema modification locks, bulk update locks)
When queries are compiled, schema stability locks prevent other processes from acquiring schema modification locks, which are taken when a table’s structure is being modified. Bulk insert locks are acquired during various bulk inserts such as BULK INSERT or by using the TABLOCK hint. Requesting this special bulk update table lock does not necessarily mean it will be granted; if other processes already hold locks on the table, or if the table has any indexes, a bulk update lock cannot be granted. If multiple connections have requested and received a bulk update lock they can perform parallel loads into the same table. Unlike exclusive locks, bulk update locks do not conflict with each other, so concurrent inserts by multiple connections is supported.

Conversion Locks
Conversion locks cannot be directed requested by SQL server but are the result of a conversion from one mode to another, in SQL Server 2005, these consist of SIX, SIU, UIX. The most common of which is the SIX, which occurs if a transaction holding a shared lock on a resource and later an IX lock is needed. The lock mode would be indicated as SIX.

Key Locks
For certain isolation levels (Read Committed, Repeatable Read, or Snapshot) SQL Server tries to lock the actual index keys accessed while processing the query. With a table that has a clustered index, the data rows are the lead level of the index, and you will see key locks acquired. If the table is a heap, you might see key locks for the non-clustered indexes and row locks for the actual data.

Key Range Locks
Additonal lock modes – called key range locks, are taken only in the Serializable isolation level for locking ranges of data. There are 9 times of key-range locks, and each as a two part name: the first part indicates the type of lock on the range of data between adjacent index keys, and the second part indicates the type of lock on the key itself. Most of which are very rare and/or transient. These types of key range locks are:

• RangeS-S –Shared lock on the range between keys (shared lock on the key at the end of the range)
• RangeS-U –Shared lock on the range between the keys (update lock on the key at the end of the range)
• RangeIn-Null – Exclusive lock to prevent inserts on the range between keys; no lock on the keys themselves
• RangeX-X – Exclsuive lock on the range between keys; exclusive lock on the key at the end of the range
• RangeIn-S – Conversion: S + RangeIn-Null
• RangeIn-U – Conversion: U + RangeIn-Null
• RangeIn-X – Conversion: X + RangeIn-Null
• RangeX-S – Conversion: RangeIn-Null + RangeS-S
• RangeX-U – Conversion: RangeIn-Null + RangeS-U