SQL SERVER – How Can Deadlock Happen on Same Table?

Interesting TRUE Story

As part of learning I get inspiration from the activities we do daily and see how it can be mapped to SQL Server. This in a way many say is an obsession for the subject I love – SQL Server. But I think it is part of how we laterally think. In the recent past, I have been following a number of sports and this quest is fundamental to make sure when my little girl asks me, I am ready with the answers. Till date it has been a fun ride and I have nothing to complain. Recently, I was watching a live World Chess Championship Tournament and it was intriguing. There was silence all around and I was intensely watching the match. My little girl was amused with this silence and let me be myself for close to 15 mins. She then tried to join me where in the match was about to end. She was watching me and TV with curiosity to why I was concentrating so much. The match actually ended in a draw and people were clapping hands and the players walked away. Seeing this, my daughter asked me to why there was no shouting or cheering from the audience (sorry for the football/cricket fever that was running for a while).

Then she asked an interesting question, why did the players walk away? They didn’t talk, fight or do anything but they just walked away, why? I told it was a stalemate game and none of the players won. So they decided to end it this way gracefully. That night I was thinking to how such simple things can get the curiosity of our children. It was a dead game and in SQL Server terms I would say a deadlock.

Back to Reality

This incident was in my mind for a while and I was planning to share it when the appropriate time comes. Unrelated, I got a simple enquiry from one of my blog readers. Can a table deadlock itself? Is that possible? Moreover, we are just doing inserts into this table as it is getting deadlocked, how is it possible? They were interestingly getting something like this in their environment and wanted to know how that is possible.

A web search can give you deadlock information generally where there are two objects that are in cyclic deadlock with each other, rarely do we get to see a single table deadlock. So I thought to write this blog to show how we can still get deadlocks using a single table. The sequence for this script would look like:

	Session 1	Session 2
1	Create our table
2	BeginTransactionInsert row1
3		BeginTransactionInsert Row2
4	Insert row2Will get blocked
5		Insert Row 1Will get blocked and become deadlock
6	Deadlock encountered, one of the session terminates

Now that we got the flow ready, let us put the same in code. Below is the script for your reference.

Script for Scenario

-- Session 1: Step 1. Create our table
USE tempdb
GO
CREATE TABLE deadlocks
(ID INT, Name CHAR(20), Company CHAR(50));
GO
CREATE UNIQUE CLUSTERED INDEX deadlock_ind ON deadlocks (ID, Name)
GO
-- ----------------------
-- Session 1: Step 2. Insert row 1
BEGIN TRAN
INSERT INTO deadlocks
VALUES (1, 'Pinal', 'SQLAuth')
-- ----------------------
-- Session 2: Step 3. Insert Row 2
BEGIN TRAN
INSERT INTO deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 1: Step 4. Insert row 2
INSERT INTO deadlocks
VALUES (10, 'SQLAuth', 'Blog')
-- ----------------------
-- Session 2: Step 5. Insert Row 1
INSERT INTO deadlocks
VALUES (1, 'Pinal', 'SQLAuth')

Please take a note of the session above. We will alternate the way in which the insert statements are built. Once these steps are followed, the famous deadlock error message of 1205 is shown:

Msg 1205, Level 13, State 47, Line 7

Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In the above example, we are just using insert statements to generate a deadlock situation. How many of you have ever encountered such deadlocks in your environments? Do let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)