Locking in SQL Server: Detailed Overview

In a multi-user environment, it is essential to maintain truncation concurrency. These locks are in-memory structures of 96 bytes in size. Their role is to maintain data integrity, consistency, concurrency control for each transaction. SQL Server follows the ACID test for each transaction.

• Atomicity: This property ensures that a transaction involving two or more processes is committed fully, or none of the processes is committed.
• Consistency: It gives you a guarantee about the committed transaction state. A transaction should either create a new state of data or return to the existing (before the transaction) state.
• Isolation: It indicates that transactions are isolated from each other. If a transaction is running and it did not commit data, it is isolated from other transactions.
• Durability: The durability ensures your committed data is never lost. It prevents power and operating system failure, or other software-induced errors.

To ensure ACID properties, SQL Server imposes different kinds of locks on the objects. In this case, other transactions need to wait until the lock is released.

Locking Modes in SQL Server

SQL Server uses the following locking modes for each transaction.

• Shared locks:
  • In this lock, SQL Server enables other sessions to perform the selected operations for reading data. However, it prevents updates until the lock is active.
  • Multiple transactions can impose a shared lock at the same time on a row or page.
  • It is a common lock that you see on your database objects.

In the following T-SQL, we retrieve the customer record for a specific customer ID. Further, we use dynamic management view sys.dm_tran_locks to check the existing locks.

BEGIN TRAN
SELECT * FROM [SalesLT].[Customer] WITH (HOLDLOCK)
WHERE CustomerID=1
    
SELECT resource_type, request_mode, resource_description
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'

ROLLBACK

As shown below, it has a shared lock on the given resource id (8194443284a0):

• Exclusive (X) locks:
  • SQL Server uses exclusive lock (X) for DML operations (Delete, Insert or Update), requiring modifying a row or page data.
  • It prevents other uses from accessing the resource until a lock is placed.
  • SQL Server can have only one exclusive lock on a page or row for a transaction.

In this example, we want to update records for customer id 1. Therefore, SQL Server requires an exclusive lock on the resource. No other transaction can acquire the exclusive lock on this resource until the transaction is completed.

BEGIN TRAN
UPDATE [SalesLT].[Customer] 
SET Suffix='Mr.'  
WHERE CustomerID=1
    
SELECT resource_type, request_mode, resource_description
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'

ROLLBACK

• Update (U) locks:
  • The Update lock is similar to an exclusive lock. It can be placed on a record having a shared lock.
  • The update lock places another shared lock on a specific row. Once it can modify the records, SQL Server converts the Update lock to an Exclusive lock.
  • SQL Server cannot place a shared lock on a resource with an Update lock.
  • You can also use WITH UPDLOCK for forcing an update lock.

The following example shows an update lock on the resource id (8194443284a0):

BEGIN TRAN
SELECT * FROM [SalesLT].[Customer] WITH (UPDLOCK)
WHERE CustomerID=1
    
SELECT resource_type, request_mode, resource_description
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'

ROLLBACK

• Intent locks:
  • Its purpose is to inform a transaction about its intention to acquire a lock. It occurs when a transaction requires a shared or exclusive lock on the resources lower in the hierarchy.
  • The transaction does not allow other transactions to get an exclusive lock on the table using an intent lock.
  • Types of intent locks are below.
    • Intent Shared (IS) lock: It indicates SQL Server intention to read lower hierarchy resources by acquiring shared lock individually on those lower hierarchy resources.
    • Intent Exclusive (IX) lock: It indicates SQL Server intention to modify lower hierarchy resources by obtaining an exclusive lock on those lower hierarchy resources.
    • An intent Update lock (IU): It can be acquired at the page level only for lower hierarchical resources, and once the update completes, it converts to IX lock.

As shown below, the transaction has an exclusive lock on a key, and it has an Intent exclusive lock on the page level.

Conversion Locks

SQL Server converts lock types to support multiple queries in a transaction. These locks are known as conversion locks.

• SIX – Shared with Intent Exclusive lock: The SQL Server transaction holds a shared lock on several pages and has an exclusive lock on several rows.
• SIU – The SQL Server transaction holds a shared lock on several pages and has an Update lock on several rows.
• UIX- Update with Intent Exclusive Lock: The SQL Server transaction hold an Update lock on several pages and has an Exclusive lock on several rows.

Schema Locks in SQL Server

SQL Server acquires two kinds of schema locks.

• Schema stability lock (Sch-S): This lock is used when schema depending query compiles, and its execution plan is being generated. The Sch-S lock does not block any access to the object data.
• Schema modification lock (Sch-M): This lock results from a DDL( Data Definition Language) query execution. SQL Server can have only one schema modification lock on an object. You cannot modify an object with this schema lock.

In the below example, we get both Sch-S and Sch-M locks while modifying an object definition.

BEGIN TRAN
Alter TABLE DemoTable ADD new bit
SELECT resource_type, request_mode, resource_description
FROM   sys.dm_tran_locks
WHERE  resource_type <> 'DATABASE'
ROLLBACK

Lock Compatibility

The lock compatibility is helpful to check allowed locks in case of multiple transactions in the same resource simultaneously. If a transaction places a lock, the new lock placed by another transaction should be compatible with it. Therefore, you can go through the following lock compatibility list and find supported locks during multiple transactions.

Lock Escalations

SQL Server introduced a lock escalation feature to prevent too much locking that could cause memory pressure. SQL Server considers the number of locks held on a particular scan and the number of locks held by whole transaction and memory dynamically. SQL Server converts low-level locks to high-level locks in lock escalation. For example, it converts row locks to page-level locks.

It uses the following threshold for lock escalations.

• Memory threshold: The lock memory threshold is set at 40 percent of the lock memory.
• Lock Threshold: If the number of locks acquired on the current table or index is greater than 5000, lock escalations can be triggered.

Users can control lock escalations using the alter table statement. You can completely disable the lock escalation for that table using a parameter value DISABLE.

ALTER TABLE Table_name SET (LOCK_ESCALATION = < TABLE | AUTO | DISABLE > –One of those options) GO

You can refer to Microsoft documentation for understanding lock escalations in detail.

Note: You should not disable lock escalation until it is thoroughly tested in a lower environment, and it is recommended to use by experienced DBAs only.

Conclusion

This article gives a detailed overview of SQL Server locks and DMV to monitor the lock and its escalation process. Locking is quite normal behavior in SQL Server, and you should be familiar with it to understand how multiple transactions work, simulating and providing consistent data.