Lock Compatibility

This is as most of my post are expressly aimed at helping me remember  some thing that I need to refer to a lot. The following table show the SQL Server lock matrix which can be found here in picture form (what were Microsoft thinking). I believe that in this form the content is easier to read and digest; but this could become stale. I would recommend that you check the original site MSDN content if you are using a version of SQL server other that 2008R2 2012 or 2014. However I will endeavor to keep it this post accurate over future SQL server releases.

The below is accurate at the time of writing and applies to the following versions of SQL Server : 2008R2, 2012, 2014.

Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. For example, no lock modes are compatible with exclusive locks. While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. However, other transactions cannot acquire an exclusive lock until the shared lock has been released.

 NLSCH-SSCH-MSUXISIUIXSIUSIXUIXBURS-SRS-URI-NRI-SRI-URI-XRX-SRX-URX-N
No Lock (NL)NNNNNNNNNNNNNNNNNNNNNN
Schema Stability Locks (SCH-S)NNNNNNNNNNNNNIIIIIIIII
Schema Modification Locks (SCH-M)NCCCCCCCCCCCCIIIIIIIII
Shared (S)NNCNNCNNCNCCCNNNNNCNNC
Update (U)NNCNCCNCCCCCCNCNNCCNCC
Exclusive (X)NNCCCCCCCCCCCIIIIIIIII
Intent Shared (IS)NNCNNCNNNNNNCIIIIIIIII
Intenet Update (IU)NNCNCCNNNNNCCIIIIIIIII
Intent Exclusive (IX)NNCCCCNNNCCCCIIIIIIIII
Shared with Intent Update (SIU)NNCNCCNNCNCCCIIIIIIIII
Shared with Intent Exclusive (SIX)NNCCCCNNCCCCCIIIIIIIII
Update with Intent Exclusive (UIX)NNCCCCNCCCCCCIIIIIIIII
Bulk Update (BU)NNCCCCCCCCCCNIIIIIIIII
Shared Range-Shared (RS-S)NIINNCIIIIIIINNCCCCCCC
Shared Range-Update (RS-U)NIINCCIIIIIIINCCCCCCCC
Insert Range-Null (RI-N)NIINNNIIIIIIICCNNNNCCC
Insert Range-Shared (RI-S)NIINNCIIIIIIICCNNNCCCC
Insert Range-Update (RI-U)NIINCCIIIIIIICCNNCCCCC
Insert Range -Exclusive (RI-X)NIICCCIIIIIIICCNCCCCCC
Exclusive Range-Shared (RX-S)NIINNCIIIIIIICCCCCCCCC
Exclusive Range-Update (RX-U)NIINCCIIIIIIICCCCCCCCC
Exclusive Range-Exclusive (RX-X)NIICCCIIIIIIICCCCCCCCC
Key
C: Conflict
NC: No Conflict
I: Ilegal