Home > Database > Deadlock on Index

Deadlock on Index

In this entry I would like to share about the “Debugging Deadlock”. Below is the short notes on business requirement.
My database has a User’s table called “USER_DETAIL”. I am updating this table using a Stored procedure. This can be called form multiple application instances.

Table definition & Fill with Data:


Here is short descrition about the columns…
user_detail_id: Primary key, Identity Column in this table.
network_id: Say, it is like user name.
tl_id (Team lean Id): Self referance for User_detail_id. Like a Manager/Lead for a User. And also that, Manager/Lead is also a user in application.

I am skipping remaining columns from this section, as we can understand from its name.

This table 3 non-cluster indexes. Table definition, Insert statements are documented here.

Inserted some dummy data using a WHILE loop


— Fill Data
declare @index int
@index = 1


@index <=1000




INSERT INTO TempDemos.dbo.user_detail


VALUES (‘network_id_’ + CONVERT(varchar(10), @index)

, ‘first_name_’ + CONVERT(varchar(10), @index)

, ‘last_name_’ + CONVERT(varchar(10), @index)

, ’email_’ + CONVERT(varchar(10), @index) + ‘@sa.com’


set @index=@index+1



Now, lets work for generating deadlock. Execute the same code from Instance of SSMS. For the demo purpose I am taking all input values into a #temp table.
And below are steps in the stored procedure.
1. Update User
2. Update TeamLead (If exists Update, else Insert)
3. Update User.TeamLead

Note: The order of execution may not be correct. But here I wanted to have deadlock. So I am with this order of steps.

Now take Process 1, Process 2 into 2 separate SSMS instances. And execute as shown below.
 11-2-2009 11-39-27 PM
Execute each section one by one. If you look at lock information immediately after executing section-6, you will have it as below…
11-3-2009 12-09-28 AM


  1. One process is searching the row using Heap search, another one is using Non-Clustered.
  2. Each one is holding one resource and waiting for each other.
  3. Causing a deadlock

Cause for Deadlock:


This table doesn’t have a cluster index. Change the Non-cluster ix_user_detail(on network_id column) to cluster index.


🙂 Basically I am not expert in DB development. I just tried to explore it. Found the solution by Trail&Error method.
I welcome, if any one interested in describe it technically. Let’s fill the ???<GAP>???

Donwload other presentation and sample code from here…








Bind multiple connections to share the same locking space. Execute the stored procedure sp_bindsession to keep more than one session in the same.


Pradeep Y.

Categories: Database
  1. No comments yet.
  1. No trackbacks yet.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: