Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Deadlock Occurence and Resolution


A Deadlock Occurence and Resolution

Author
Message
harry9katz
harry9katz
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 234
U can find the deadlock on line by running the script:
select * from master..sysprocesses where blocked>0 and spid<>blocked
order by waittime desc
and find the spid
then use :
dbcc inputbuffer (spid no)
Tony Webster
Tony Webster
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: 321
harry9katz (2/4/2009)
U can find the deadlock on line by running the script:
select * from master..sysprocesses where blocked>0 and spid<>blocked
order by waittime desc
and find the spid
then use :
dbcc inputbuffer (spid no)

That isn't quite the same thing - that finds a blocked process. Put simply, a deadlock is when process 1 is locking resource A and wants resource B, and process 2 is locking B and wants A - obviously, if you leave them to it, they'll wait forever, so SQL Server has the lock monitor thread to detect this and pick one of the two processes to summarily execute, leaving the information in the log if you have the trace flag enabled.
harry9katz
harry9katz
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 234
Right U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)
and decide if to kill the process or not.
this is a fast solution not clean as in the article
Tony Webster
Tony Webster
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: 321
harry9katz (2/6/2009)
Right U are but U can see all the blocked paid's by order of waittime and can see what spid A is Doing by DBCC inputbuffur(spid a)
and decide if to kill the process or not.


But if it's a genuine deadlock, SQL server will have beaten you to it - more or less as soon as the circular dependency arises (i.e. 1 has A and wants B, and 2 has B and wants A - or perhaps a more complicated case) the lock monitor will choose the one with the least to lose (i.e. usually the one holding the least locks) and kills the transaction it is in, which rolls back and releases the locks, so the other can proceed. Even if you were aware that it had happened, nothing is left blocking anything - this is specifically what a deadlock is (sometimes called a "deadly embrace" in the two-process case particularly) - which is distinct from the case of one process just blocking another (for example if an application had opened a transaction and not closed it)
Amit W
Amit W
Right there with Babe
Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)Right there with Babe (772 reputation)

Group: General Forum Members
Points: 772 Visits: 105
Very nice step wise article.

Thanks for sharing Praveen!



Rob-406527
Rob-406527
SSC Journeyman
SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)SSC Journeyman (75 reputation)

Group: General Forum Members
Points: 75 Visits: 128
Very nice. Quick question, if there was only RID info (Node:1 RID: 8:1:231:0) in the deadlock entry could you still lookup the object info?
Ed Sanford
Ed Sanford
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
Found it to be an interesting article; but, am unlikely to have that kind of problem as we always have primary keys on our tables. :-)
hunkwangshz
hunkwangshz
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Brilliant article and great comments!
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22803
Jonathan Kehayias (2/3/2009)
doobya (2/3/2009)
SQL 2005 has xml deadlock report in profilerwhich will track deadlock down to tables and stored procedure statementsI think your solution does not solve the deadlock - just makes it less likelyIf the db+app had been designed correctly the omission of the index would have reduced performance but not created deadlocks


Take a look at the article from yesterdy. A missing/incorrect index can most definitely be the cause of a deadlock. In fact most deadlocks are simple index issues. Very rarely do I see a deadlock that doesn't involve cross locking indexes on a single table, though it does happen at times.





Here's a repo of one half of a deadlock scenario where a seemimgly innocent join caused an index scan.
The solution I used was to use a temporary table instead.


CREATE TABLE MyTable(PK INT NOT NULL PRIMARY KEY, Val INT)
INSERT INTO MyTable(PK,Val)
SELECT 1,10 UNION ALL SELECT 2,10
--
--
DECLARE @x XML
SET @x=''
--
--
-- Joining directly to XML caused a clustered index scan on MyTable.PK
UPDATE MyTable
SET Val=r.value('@VAL','INT')
FROM @x.nodes('/ROOT/ROW') AS x(r)
WHERE MyTable.PK=r.value('@PK','INT')
--
--
DECLARE @T TABLE(PK INT NOT NULL PRIMARY KEY, Val INT)
INSERT INTO @T(PK,Val)
SELECT r.value('@PK','INT'),
r.value('@VAL','INT')
FROM @x.nodes('/ROOT/ROW') AS x(r)
--
--
-- Joining to a temporary table, this turns into a clustered index seek on MyTable.PK
UPDATE MyTable
SET Val=r.Val
FROM @T r
WHERE MyTable.PK=r.PK
--
--
DROP TABLE MyTable



____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537




Derek Jin
Derek Jin
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 23
Beside creating proper indexes, developers also need to lock tables in the same order all the time to avoid deadlock situation.

Derek
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