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/