Understanding Key-Range Locks in SQL Server: What You Need to Know for Optimized Concurrency

Understanding Key-Range Locks in SQL Server

SQL Server uses various types of locks to manage concurrency and ensure data consistency. One such lock is the key-range lock, which can lead to unexpected behavior when dealing with transactions and queries that access tables with non-unique indexes.

In this article, we will delve into the world of key-range locks, exploring how they work, why they can cause issues in certain scenarios, and what you can do to mitigate these problems.

What is a Key-Range Lock?

A key-range lock is a type of lock that allows a session to access a specific range of values within an index. The type of index (unique or non-unique) determines the boundaries of the range. When a query accesses a table with a non-unique index, it will acquire a key-range lock on the specified range.

Unique Index

When an index is unique, the lower and upper bounds of the key-range are set to the same value. This means that a session can access only one row within the range. For example, if we have a table with an ID column and a unique index on it, and we execute the following query:

SELECT * FROM MyTable WITH (XLOCK) WHERE Id = 1;

The lock acquired by this session will be exclusive, covering only the rows with IDs between 1 and 1. Any subsequent queries that attempt to access rows within this range will be blocked.

Non-Unique Index

When an index is non-unique, the lower bound of the key-range is set to the value of the index itself, while the upper bound is set to the next value in the sequence (if any). For instance, if we have a table with an ID column and a non-unique index on it:

CREATE TABLE [dbo].[MyTable](
    [Id] [bigint] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

And we execute the following query:

SELECT * FROM MyTable WITH (XLOCK) WHERE Id = 1;

The lock acquired by this session will cover the range from the first value in the index to the next value, i.e., from 1 to 2. Any subsequent queries attempting to access rows within this range will be blocked.

Lock Types

SQL Server uses several types of locks, each with its own characteristics and purposes:

XLOCK: Exclusive lock on a row or key-range.
SLOCK: Shared lock on a row or key-range.
IXLOCK: Index-wide lock.

Implications for Transactions

Transactions can acquire multiple locks simultaneously to ensure that all operations within the transaction are executed consistently. However, this also means that transactions may block other sessions attempting to access the same data.

In our example, we have two transactions:

Transaction 1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM MyTable WITH (XLOCK) WHERE Id = 1;
WAITFOR DELAY '00:00:10';
COMMIT TRANSACTION;

Transaction 2:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT * FROM MyTable WITH (XLOCK) WHERE Id = 2;

In this scenario, both transactions will acquire exclusive locks on the key-range corresponding to their respective IDs. However, because they are executing sequentially within a serialization isolation level, they will not block each other.

Locks and Row Modes

When examining system tables such as sys.dm_tran_locks, you may notice that there is only one column for lock resource description. This can sometimes make it more difficult to determine exactly what locks have been acquired by the current session.

To overcome this, let’s analyze an example code snippet provided in the original question:

CREATE TABLE mytable(Id bigint not null, index idxId /*unique*/ nonclustered (id));
GO

insert into mytable(Id) values (1), (2), (5), (7), (20), (21), (22);
GO


SET TRANSACTION ISOLATION LEVEL SERIALizable;
BEGIN TRANSACTION
SELECT * FROM mytable WITH(XLOCK) WHERE id = 1;

--nonunique index: rangeXX, 
--........locked values: 1&amp;2 for select...Id=1
--........locked values: 2&amp;5 for select...Id=2

SELECT tl.request_mode, tl.request_type, tl.request_status, tl.resource_description, irs.*
FROM 
sys.dm_tran_locks as tl 
LEFT JOIN 
(
select %%lockres%% as idxresourcedescription, Id as [column:Id/value]
from mytable WITH (index(idxId), nolock)
) as irs
ON tl.resource_description = irs.idxresourcedescription;

--rollback transaction
GO


--in another session/window
SELECT * FROM mytable WITH(XLOCK, serializable) WHERE id = 5; --this is not blocked...
RAISERROR('', 0, 0) WITH NOWAIT;
SELECT * FROM mytable WITH(XLOCK, serializable) WHERE id = 2; --...but this is blocked
GO

This query uses the sys.dm_tran_locks system table to reveal which locks are currently in place. It then selects rows from MyTable, locking a specific key-range for each row.

The lock resource description field (resource_description) will display the value of the column being locked, as well as any index constraints on that column.

By examining this data, you can see how the locks affect transactions and which keys are blocked by the current session’s operations.

Conclusion

Key-range locks in SQL Server provide a way to manage concurrency while ensuring data consistency. However, understanding their behavior and implications is crucial for avoiding blocking issues with queries.

In summary, key-range locks cover a specific range of values within an index, and the type of lock (exclusive or shared) depends on whether it’s unique or non-unique. By utilizing system views such as sys.dm_tran_locks, developers can gain insight into how locks are being acquired by their transactions and plan queries to minimize blocking issues.

When developing applications that interact with SQL Server databases, understanding the intricacies of key-range locks will help you design more efficient and robust code that handles concurrent access effectively.

Last modified on 2023-06-12