Types of Locks in SQL Server

1. Introduction

In this tutorial, we’ll study the locks in SQL Server 2022. We’ll use our University schema for queries.

2. Fundamentals of Locking

A database uses locks to maintain data integrity and consistency in concurrent transactions. Locks enforce the ACID principle (Atomicity, Consistency, Isolation, and Durability) and avoid simultaneous conflicting operations.

SQL Server sets isolation levels to uphold integrity while processing concurrent transactions. An isolation level controls the degree to which one transaction is shielded from resource (data) modifications by other transactions. Those levels are implemented via various lock types:

1. Shared
2. Exclusive
3. Update
4. Intent
5. Schema
6. Bulk update
7. Key range

SQL Server has two concurrency control types:

1. pessimistic locking
2. optimistic concurrency

In pessimistic locking, we immediately lock to prevent other transactions from modifying the data. In contrast, we reduce lock contention in optimistic concurrency by freezing selected rows and isolating that DB snapshot.

3. Shared Lock

We use a Shared lock (S) to allow concurrent read access without modification.

Here’s an example. In the first session, we set the isolation level to Repeatable Read to hold the lock till commit or rollback:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Then, we run a SELECT query with a shared lock to read a course:

BEGIN TRAN;
SELECT id, name, credits 
FROM Course 
WHERE id='CS111';

Here’s the output:

id         name                                credits
---------- ----------------------------------- --------
CS111      Introduction to Operating Systems   7

We can check the shared lock in our first session is at the key level:

SELECT request_session_id, resource_type, request_mode 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID 
AND resource_type='KEY';

request_session_id resource_type   request_mode
------------------ --------------- -------------
101                KEY             S

In our second session, we fetch the same course data using the same query, without blocking.

Now, we try to update the course credits. This time, we get stuck in a wait loop:

UPDATE Course
SET credits = 8
WHERE id='CS111';

Let’s open a third session and check the sessions that are in a suspended state:

SELECT
    session_id,
    blocking_session_id,
    command,
    status
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE blocking_session_id <> 0;

Our session 2 (id=101) is blocked by session 1 (id=100):

session_id blocking_session_id command  status   
---------- ------------------- -------- ----------
100        101                 UPDATE   suspended

We get unblocked by explicitly committing or rolling back the ongoing transaction that acquired the lock.

4. Exclusive Lock

We use an exclusive lock (X) to bar other transactions from accessing or modifying the locked data. We do this to preserve data integrity.

Continuing the same example, in our first session, let’s set the isolation level to Repeatable Read and update the credits:

BEGIN TRAN;
UPDATE Course
SET credits = 8
WHERE id='CS111';

Here are the locks held:

request_session_id resource_type   request_mode
------------------ --------------- -------------
101                DATABASE        S
101                PAGE            IU
101                KEY             U
101                OBJECT          IX

In our case of an UPDATE query on a single row, SQL Server acquires an Intent Exclusive (IX) lock at the object (table) level. This indicates our intention to place exclusive locks at the row (key) level.

Why don’t we see an X lock right away? Well, SQL Server follows a hierarchical locking structure:

1. Database
2. Table or Object
3. Page
4. Row

If SQL Server placed an exclusive lock at the table level, no other session could access any different (even unrelated) row within that table. That would severely limit concurrency. Hence, the SQL Server uses intent locks at the table level to optimize performance. It holds an exclusive lock only at the precise row (key) being modified.

We’ll be blocked in our second session if we try to SELECT that row:

BEGIN TRAN;
SELECT id, name, credits 
FROM Course 
WHERE id='CS111';

We unblock our second session once we roll back the transaction from our first session.

5. Update Lock

An update lock (U) is a combination of a shared and an exclusive lock. It allows reads but not write operations.

For this case, we take an update lock using UPDLOCK and run our SELECT query with it:

BEGIN TRAN;
SELECT id, name, credits 
FROM Course
WITH (UPDLOCK) 
WHERE id='CS111';

Next, let’s check the lock type:

SELECT request_session_id, resource_type, request_mode 
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

We got a U lock on the key:

request_session_id resource_type   request_mode
------------------ --------------- -------------
54                 DATABASE        S
54                 PAGE            IU
54                 KEY             U
54                 OBJECT          IX

We can run the SELECT query to read this row in other transactions without blocking. However, we’ll be blocked in an update operation in another transaction unless we commit or roll back the first session.

6. Intent Locks

In SQL Server, we place an intent lock at a higher-level resource (such as a table or page) to tell the SQL Server that we might request locks at a lower granularity (such as rows). This lock helps the SQL Server manage concurrent resources efficiently.

How? It uses lock intent requests to decide lock allocation.

6.1. Types

We have three types of intent locks:

1. Intent Shared (IS)
2. Intent Exclusive (IX)
3. Shared with Intent Exclusive (SIX)

IS registers our intent to place a shared lock on lower-level resources (e.g, rows). With an IX lock, we tell SQL Server that we’ll shortly place an exclusive lock on a lower-level resource. IX is compatible with IS but not shared or exclusive schema locks.

Finally, SIX is a hybrid of shared and exclusive locks. It operates at a higher-level resource, such as a table. We use SIX to show the intent to place an exclusive lock at a lower level (e.g., rows). The objective is to prevent other transactions from placing exclusive locks on the resource while allowing shared access.

We use TABLOCKX and HOLDLOCK constructs to tell SQL Server we want a SIX lock: TABLOCKX stands for Table Exclusive Lock, and HOLDLOCK means Serializable isolation level. Serializable isolation internally uses key-range locks to prevent phantom reads. While running a transaction where we execute two identical queries (with some time lag), if we find the result of the second query different from the first, we call this a phantom read.

Under the Serializable isolation, SQL Server places key-range or table-level locks so that no new rows are inserted or deleted matching the query predicate. This ensures full consistency by blocking insertions or deletions in the queried range.

6.2. Example

Let’s take a SIX lock over Course in our first session:

SELECT id, name, credits 
FROM Course
WITH (TABLOCKX, HOLDLOCK)
WHERE id='CS111';

Then, run our UPDATE query. We can verify the SIX lock is held (Shared at the schema level and exclusive at the object (row) level):

request_session_id resource_type  request_mode 
------------------ -------------- --------------
52                 DATABASE       S
52                 OBJECT         X

In our second session, our SELECT query will be blocked due to the SIX lock from the first session.

7. Other Locks

7.1. Schema Locks

SQL Server provides schema locks for schema modification or consistency checks. It has two subtypes:

1. Schema Modification (Sch-M)
2. Schema Stability (Sch-S)