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:

Deadlocks

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 data

Insert 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 = 10

select
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.

Deadlock1

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 = 10

select
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.

Deadlock2

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.

Deadlock1

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.

Deadlock2

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/

Advertisements

One thought on “KEY-LOOKUP Deadlock in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s