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:

image

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
image 

 

— Fill Data
declare @index int
set
@index = 1

while

@index <=1000

begin
 

print

@index

INSERT INTO TempDemos.dbo.user_detail

(network_id,first_name,last_name,email,create_date)

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’

, GETDATE())

set @index=@index+1

end

 

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

Observations:

  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:
???<GAP>???

Solution:

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

Conclusion:

🙂 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…

References:

http://msdn.microsoft.com/en-us/library/ms191242.aspx

http://rusanu.com/2009/05/16/readwrite-deadlock/

http://support.microsoft.com/kb/169960

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/07/30/the-anatomy-of-a-deadlock.aspx

http://www.mail-archive.com/searchdatabase@lists.techtarget.com/msg00035.html

http://blogs.msdn.com/craigfr/default.aspx
http://technet.microsoft.com/en-us/library/cc546518.aspx

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.

Advertisements
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 )

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

%d bloggers like this: