SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlock Issue


Deadlock Issue

Author
Message
lianvh 89542
lianvh 89542
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 666
We are running SQL Server 2008R2 Ent and we are experiencing a high incident rate of Deadlocks . We started by adding some of the missing indexes . This practice did help to resolve some of the deadlocks. However we get a Clustered Index Update of 86% of the processing time during One particular Transaction . I had a look at the update statement and there is not much we can dot to optimize it as it is a simple update statement : " Update Table1 set column1 = Getdate() , column2 = 'Name1' where column 3 = 99999.
This transaction causes the deadlock . Any ideas to resolve this issue ?
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4319 Visits: 2774
Good if you are able to regenerate the issue. Setup the profiler with deadlock events under lock event groups along with deadlock graphs. Run the profiler and start generating the deadlock event. It'll give you a very clear picture of whats causing the deadlock.
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41211 Visits: 32666
A single straight update statement all by itself can't cause a deadlock with another update/insert/delete statement. So, other statements within these query batches must be involved. Since you're on 2008, you can use the system_health extended event session to look at the deadlock graphs. That will tell you the other statement where the deadlock is occurring. What's likely is that you're doing two statements that involve both reading from the table and writing to it, but they are doing those reads and writes in different order. Understand all the statements involved and you'll be more likely to arrive at the solution to the deadlock.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 1336
As Grant Fritchey said single update statement can not cause deadlock to itself. So run sp_who2 which will give you what processes are running at the time. Look for blyby column. Look which SP ID this update statement has, and see if there is any non zero value in bklby Column. If you see something there, see if that SP ID is blocked by other ans so on.

You can get the text of SP ID by runinning dbcc inputbuffer (spid).

You can run profiler trace as sqlnaive sugegsted and also run DMV's as Grant said. But SP_who2 is the first palce where I would start.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12799 Visits: 8565
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Note there are 2 additional parts to this blog series.

There is also several Profiler Events under the Locks category that can show detailed information about deadlocks.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search