Understanding SQL Server Lock Modes

By: Dusan Petkovic


Lock modes specify different kinds of locks. The choice of which lock mode to apply depends on the resource that needs to be locked. The following three lock types are used for row- and page-level locking:

  • Shared (S)
  • Exclusive (X)
  • Update (U)

NOTE

The following discussion concerns the pessimistic concurrency model. The optimistic concurrency model is handled using row versioning. 

A shared lock reserves a resource (page or row) for reading only. Other processes cannot modify the locked resource while the lock remains. On the other hand, several processes can hold a shared lock for a resource at the same time—that is, several processes can read the resource locked with the shared lock.

An exclusive lock reserves a page or row for the exclusive use of a single transaction. It is used for DML statements (INSERT, UPDATE, and DELETE) that modify the resource. An exclusive lock cannot be set if some other process holds a shared or exclusive lock on the resource—that is, there can be only one exclusive lock for a resource. Once an exclusive lock is set for the page (or row), no other lock can be placed on the same resource.

NOTE

The database system automatically chooses the appropriate lock mode according to the operation type (read or write).

An update lock can be placed only if no other update or exclusive lock exists. On the other hand, it can be placed on objects that already have shared locks. (In this case, the update lock acquires another shared lock on the same object.) If a transaction that modifies the object is committed, the update lock is changed to an exclusive lock if there are no other locks on the object. There can be only one update lock for an object.

NOTE

Update locks prevent certain common types of deadlocks. (Deadlocks are described at the end of this section.)

Table 1 shows the compatibility matrix for shared, exclusive, and update locks. The matrix is interpreted as follows: suppose transaction T1 holds a lock as specified in the first column of the matrix, and suppose some other transaction, T2, requests a lock as specified in the corresponding column heading. In this case, “yes” indicates that a lock of T2 is possible, whereas “no” indicates a conflict with the existing lock.

NOTE

The Database Engine also supports other lock forms, such as latches and spinlocks. The description of these lock forms can be found in Books Online.

At the table level, there are five different types of locks:

  •     Shared (S)
  •     Exclusive (X)
  •     Intent shared (IS)
  •     Intent exclusive (IX)
  •     Shared with intent exclusive (SIX)

Shared and exclusive locks correspond to the row-level (or page-level) locks with the same names. Generally, an intent lock shows an intention to lock the next-lower resource in the hierarchy of the database objects. Therefore, intent locks are placed at a level in the object hierarchy above that which the process intends to lock. This is an efficient way to tell whether such locks will be possible, and it prevents other processes from locking the higher level before the desired locks can be attained.

 

0405_001

 

Table 1 Compatibility Matrix for Shared, Exclusive, and Update Locks

Table 2 shows the compatibility matrix for all kinds of table locks. The matrix is interpreted exactly as the matrix in Table 1.

 

0406_001

Table 2 Compatibility Matrix for All Kinds of Table Locks

 

 

 

 

Leave a Reply