Lock Modes
A lock has a mode that determines its power-whether it prevents other users from reading or changing the locked resource.
The six lock modes are as follows.
Note: In error messages a lock mode can be displayed as a number, which is shown in parentheses in the following list.
X
Exclusive locks can be read or write (6)
Only one transaction can hold an exclusive lock on a resource at a given time. A user of this lock is called a writer.
U
Update locks (5)
Only one transaction can hold an update lock on a resource at any given time. Update lock protocols are used to increase concurrency and reduce the risk of deadlocks
For rows and pages that have not been updated within the transaction, update locks can be converted to shared locks.
S
Shared locks or read locks (3)
Multiple transactions can hold shared locks on the same resource at the same time. No transaction can update a resource with a shared lock. A user of this lock is called a reader.
IX, IS
Intent exclusive (2) and intent shared locks (1)
The locking system grants an intent exclusive (IX) or intent shared (IS) lock at the table level before it grants an exclusive (X) or shared (S) lock on a page in a table. An intent lock on a table indicates a finer level of lock granularity (that pages in the table are being accessed). An IX lock at table level indicates that pages have been updated, a IS lock at table level indicates that pages have been read.
SIX
Shared intent exclusive locks (4)
SIX ("shared with intent to update") locks can be considered as combination of S (shared) locks and IX (intent exclusive) locks. SIX locks are used in table locking strategies to minimize the extent of exclusive locking requirements.
N
Null locks (0)
Null locks do not block any other lock, but preserve the contents of the lock value block or preserve the locking structures for further use.
Last modified date: 11/28/2023