Deadlock Due To Bookmark Lookups

In this blog post, we will see how deadlocks can happen in SQL Server due to Bookmark Lookups.

Let’s create a dummy table WideWorldImporters & associated indexes. This will be our test data to play with.

use WideWorldImporters
GO

-- create a opy of sales.customers
select * into sales.customers2
from sales.Customers

-- create clustered index on customer id
create clustered index ix_CustomerID
on sales.customers2
(CustomerID)

-- create a non-clustered index on customer name
create index ix_CustomerName
on sales.customers2
(CustomerName)

In a new query window, simulate user 1, who runs a SELECT statement in an endless loop.

-- simulate user 1
-- runs a SELECT operation in an endless loop
while 1=1
select WebsiteURL from sales.Customers2
where CustomerName = 'Tailspin Toys (Head Office)'

In another query window, simulate user 2, who runs an UPDATE statement in an endless loop.

-- simulate user 2 (another window)
-- runs an UPDATE operation in an endless loop
-- dirrty code :( sorry

begin
declare @varname varchar(100)
while 1=1
begin
set @varname = (select CustomerName from sales.Customers2
where customerid=1)
if @varname = 'Tailspin Toys (Head Office)'
begin
update sales.Customers2
set CustomerName = 'Tailspin Toys (Head Office) 2'
where CustomerID = 1
end
else
begin
update sales.Customers2
set CustomerName = 'Tailspin Toys (Head Office)'
where CustomerID = 1
end
end
end
go

In a few seconds, you will see a deadlock, and most likely the SELECT operation will be the victim as it does less work.

(0 rows affected)
Msg 1205, Level 13, State 51, Line 23
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

What’s going on? For deadlocks to happen, there needs to be two resources and two users, at least (well, in most cyclic cases).

Here too, we have two resources. The first one is the base table (table with clustered index) and the other one being the non-clustered index data structure.

User 1 is running an endless SELECT loop. The SELECT query reads data from the base table via a bookmark loopkup. It seeks on the CustomerName non-clustered index and then looks up the base table.

User 2 runs an endless UPDATE which is updating the CustomerName column. Remember, the update operation has to update the base table, as well as the non-clustered index on CustomerName.

While both the operations are running in an endless loop, a point comes when user 1 is holding a S (Shared) lock on the index structure and wants and S lock on the base table to read the data, but at that very same time, user 2 is already holding an X (exclusive) lock on the base table and wants an X lock on the index to update the index. So, user 1 has to wait (S is incompatible with X) and user 2 has to wait for the same reason. There is a deadlock.

Here is the most terrible illustration you will ever see.

Remember, this is still a cyclic deadlock, just another classification. So, what is the solution? 🙂

Thanks for reading. If you liked the content, do share it. I am on Twitter if you wish to connect: https://www.twitter.com/A_Bansal
You can also subscribe to my exclusive newsletter ConnectWithAB – https://sqlmaestros.com/stay-connected-with-amitbansal/