Lock and Latch Compatibility Articles

Here is a good article describing what lock compatibility is all about as well as a few charts for reference:

TechNET: Lock Compatibility

Here is a related article on lock modes:

TechNet: Lock Modes

What is the difference between a lock and a latch?

A latch in SQL Server is mechanism used to coordinate the physical control of an object for execution of multiple threads within a database. A lock is used at a logical level to achieve the required isolation based on the chosen isolation level of the transaction. A user can influence locks indirectly – i.e. through setting the isolation level, or through the various lock hints that are available in SQL Server. A latch can’t be influenced or controlled directly. There are no such thing as latch hints in SQL Server, and there is also no latch isolation level available.

Another description I read at says this:

“SQL Server locks, discussed in the article All about locking in SQL Server, which is applied on data for the duration of the logical operation to preserve logical transaction consistency. SQL Server latches, however, are a special type of low-level system locks which are held as long as the physical operation lasts on the memory page in order to protect memory consistency”

Views – 822

Leave a Reply