Locks

Sql server uses different locks to manage database concurrency.

Shared locks allow a resource to be read by multiple concurrent transactions, but do not allow data to be modified while the shared lock exists on the resource.

Update locks are used to eliminate a dead lock scenario. With this, only one transaction at a time can obtain update lock on a resource. When the actual update occurs update lock will be converted to exclusive lock.

Exclusive locks are used to prevent concurrent access to a resource by multiple transactions. Only the transaction with the exclusive lock can read or modify the data. This type of lock is used when you are modifying data during an update operation. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level.

Intent locks are used by sql server to indicate that a lock is being acquired for some resources further down in the hierarchy. A shared intent lock on a table indicates that a transaction plans to acquire a shared lock on rows or pages within the table. This prevents another transaction from placing an exclusive lock on the table containing the page or rowlocks. The types of intent locks are 1) intent shared 2) intent exclusive 3) shared with intent

Schema locks are used to control concurrency for a table and database alterations. Sch-m locks are established for DDL commands. Sch-s locks are used for operations such as compiling a query. They prevent schema changes from being made.

Bulk update locks are used to allow multiple processes to copy data in bulk into a table concurrently while preventing access to the table by any other process that is not copying bulk data into it. This lock is used when you employ the TABLOCK hint for bulk copy operations or when you set the ‘Table lock on bulk load’ option with the sp_tableoption stored procedure. 

Here's a little explanation of the three columns from sys.dm_tran_locks used in the examples:

resource_type	This tells us what resource in the database the locks are being taken on. It can be one of these values: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, ALLOCATION_UNIT.
request_mode	This tells us the mode of our lock.
resource_description	This shows a brief description of the resource. Usually holds the id of the page, object, file, row, etc. It isn't populated for every type of lock

The filter on resource_type <> 'DATABASE' just means that we don't want to see general shared locks taken on databases. These are always present.

Key - Range locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

• RangeX-X - exclusive lock on the interval between the keys and exclusive lock on the last key in the range
• RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

Observations on locks

UPDATE emp SET empname=UPPER(empname) WHERE empid=1

KEY                   X

OBJECT             IX

PAGE                IX

-----------------------------------------------------------------------

SELECT * FROM emp (REPEATABLEREAD)

KEY                   S

OBJECT             IS

PAGE                IS

-----------------------------------------------------------------------------

SELECT * FROM emp (READCOMMITTED) --& (READUNCOMMITTED) & (SNAPSHOT)

DATABASE         S

-------------------------------------------------------------------------------

SELECT * FROM emp (SERIALIZABLE)

KEY                   RangeS-S

OBJECT             IS

PAGE                IS