Today, I’m going to talk about a special deadlock case, which is called KEY-LOOKUP deadlock. This deadlock can happen if there are many sessions which are reading or updating on the same row at the same time. We don’t distinguish kind of lock modes (Share / Exclusive), if a lock is existed, other locks must wait until it is released and then they are granted on the resource.
The first, we come back the question WHAT IS DEADLOCK? A deadlock occurs when two separate transactions are holding a resource that the other needs. Each transaction is waiting to release resource it is holding until the other resource becomes available. Let’s see the below picture:
Transaction 1 is holding a lock on Resource 1 and requesting a lock on Resource 2 while Transaction 2 is holding a lock on Resource 2 and requesting a lock on Resource 1. So transaction 1 could not get a lock on Resource 2 because a lock is held by transaction 2 and transaction 2 could not get a lock on Resource 1. Deadlock is happening.
Now, we’re talking about the special case KEY-LOOKUP deadlock. Why is happening and how to prevent it? As you know, KEY-LOOKUP deadlock is the special case of Reader-Writer deadlock, a transaction is reading on non-clustered index and look up clustered index to retrieval data while other transaction is writing / updating data of non-clustered index based on clustered index. OK, we’re doing an example to understand the case deeply.
Step 1: create data
Use Database_Testing;
go
Create table dbo.Customer_KeyLookUp_Deadlock
(
ID int,
CustomerID int,
CustomerName varchar(50),
CustomerEmail varchar(50),
CustomerAddress varchar(100)
)
go
create unique clustered index cl_ID on dbo.Customer_KeyLookUp_Deadlock
(
ID
)
go
create unique nonclustered index ncl_CustomerID on dbo.Customer_KeyLookUp_Deadlock
(
CustomerID
)
include
(CustomerEmail)
go
— insert dataInsert into dbo.Customer_KeyLookUp_Deadlock
values (1,100,’Nguyen Van A’,’nguyenvana@harveynash.vn’,’Testing’),
(2,101,’Nguyen Van B’,’nguyenvanb@harveynash.vn’,’Testing’),
(3,102,’Nguyen Van C’,’nguyenvanc@harveynash.vn’,’Testing’),
(4,103,’Nguyen Van D’,’nguyenvand@harveynash.vn’,’Testing’),
(5,104,’Nguyen Van E’,’nguyenvane@harveynash.vn’,’Testing’),
(6,105,’Nguyen Van F’,’nguyenvanf@harveynash.vn’,’Testing’),
(7,106,’Nguyen Van G’,’nguyenvang@harveynash.vn’,’Testing’),
(8,107,’Nguyen Van H’,’nguyenvanh@harveynash.vn’,’Testing’),
(9,108,’Nguyen Van K’,’nguyenvank@harveynash.vn’,’Testing’),
(10,109,’Nguyen Van J’,’nguyenvanj@harveynash.vn’,’Testing’)
Step 2: run 2 transactions to create deadlock
Transaction 1: update data where CustomerAddress Like ‘Testing%’
use Database_Testing;
go
begin tran
update [dbo].[Customer_KeyLookUp_Deadlock]
set CustomerID = CustomerID +10
where CustomerAddress like ‘Testing%’rollback
go 5000
Transaction 2: select CustomerName data where CustomerEmail Like ‘nguyen%’, we also create non-clustered index on CustomerID and we force to use nonclustered index on the SELECT statement
use Database_Testing
go
declare @CustomerName varchar(50)
select @CustomerName= CustomerName
from [dbo].[Customer_KeyLookUp_Deadlock] with(INDEX=ncl_CustomerID)
where CustomerEmail like ‘nguyen%’;
go 5000
Depends on your environment, you need change values Go <n> to cause deadlock issue. With my testing environment,I chose n=5000. Because both of statements only access the same data page at the same time then SQL Server engine raises deadlock error.
In this case, we would expect to have the blocking case instead of deadlocks due to exclusive (X) and shared (S) lock incompatibility for the duration of the transaction However, even if we are dealing with the single row, there are two indexes involved. The question is here WHY? OK, let’s investigate some situations.
Case 1: update one column which is not a part of nonclustered index.
begin tran
update [dbo].[Customer_KeyLookUp_Deadlock]
set CustomerAddress =’Testing 2′
where ID = 10select
l.request_session_id,
OBJECT_NAME(p.object_id) as [Object Name],
i.name as IndexName,
l.resource_type [Lock Type],
l.resource_description [Resource],
l.request_mode [Mode],
l.request_status [Status],
wt.blocking_session_id [Blocked by]
from sys.dm_tran_locks l
join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
left join sys.dm_os_waiting_tasks wt on wt.resource_address = l.lock_owner_address
and l.request_status =’WAIT’
where resource_type = ‘KEY’
and request_session_id = @@SPID
commit
go
SQL Server engine only requests an exclusive(X) lock on clustered index.
Case 2: update one column which is a part of nonclustered index. Add CustomerEmail as an included column to nonclustered index.
begin tran
update [dbo].[Customer_KeyLookUp_Deadlock]
set CustomerEmail =’dungdinh@testing’
where ID = 10select
l.request_session_id,
OBJECT_NAME(p.object_id) as [Object Name],
i.name as IndexName,
l.resource_type [Lock Type],
l.resource_description [Resource],
l.request_mode [Mode],
l.request_status [Status],
wt.blocking_session_id [Blocked by]
from sys.dm_tran_locks l
join sys.partitions p on l.resource_associated_entity_id = p.hobt_id
join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id
left join sys.dm_os_waiting_tasks wt on wt.resource_address = l.lock_owner_address
and l.request_status =’WAIT’
where resource_type = ‘KEY’
and request_session_id = @@SPID
commit
go
And result is difference definitely, SQL Server engine requests exclusive (X) clock on both of clustered and nonclustered index.
Now, we’re explaining the reason why deadlock is happening in KEY-LOOK case based on the two indexes involved. What is happening if both statements access the same data at the same time where CustomerAddress like ‘Testing%’ and CustomerEmail like ‘nguyen%’ ?
In the first step, UPDATE statement acquires exclusive (X) lock on the row in the clustered index and SELECT statement acquires shared (S) lock on the row in the nonclustered index.
At the same time, UPDATE statement is trying to acquire the exclusive (X) lock on the nonclustered index row and being blocked because there is the shared (S) lock held. Same thing happens with SELECT statement, which is trying to acquire shared (S) lock on the clustered index row and being blocked because of the exclusive (X) lock held. Classic deadlock.
Very clearly, both statements are waiting for the source to complete and deadlock is happening as the definition of deadlock we knew.
How to prevent deadlock?
There are a few ways that can help us to prevent the deadlock. First option, we add column (CustomerName) to nonclusterd index. So, SELECT statement does not need to access the data from the clustered index, which will solve the problem. Unfortunately, that solution increases the size of the nonclustered index key row and introduce additional overhead during data modifications and index maintenance.
Another approach would be switching to optimistic isolation levels where writers do not block readers. While it can help to solve blocking and deadlocking issues, it would also introduce additional tempdb overhead and increases the fragmentation.
Source reference:
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
One thought on “KEY-LOOKUP Deadlock in SQL Server”