Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

A Deadlock Occurence and Resolution Expand / Collapse
Author
Message
Posted Wednesday, February 4, 2009 9:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:56 AM
Points: 36, Visits: 209
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)


Post #649994
Posted Thursday, February 5, 2009 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:58 PM
Points: 118, 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.
Post #650997
Posted Friday, February 6, 2009 6:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 10:56 AM
Points: 36, Visits: 209
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
Post #651597
Posted Friday, February 6, 2009 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:58 PM
Points: 118, 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)
Post #651703
Posted Friday, July 17, 2009 12:36 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, May 9, 2013 5:05 AM
Points: 772, Visits: 105
Very nice step wise article.

Thanks for sharing Praveen!



Post #754641
Posted Friday, July 17, 2009 12:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 13, 2011 11:59 AM
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?
Post #755074
Posted Friday, July 17, 2009 5:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 3:17 PM
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.
Post #755204
Posted Sunday, July 19, 2009 6:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 21, 2009 6:10 PM
Points: 1, Visits: 3
Brilliant article and great comments!
Post #755434
Posted Monday, July 20, 2009 2:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:40 PM
Points: 1,683, Visits: 19,609
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='<ROOT><ROW PK="1" VAL="50"/><ROW PK="2" VAL="100"/></ROOT>'
--
--
-- 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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #755589
Posted Monday, July 20, 2009 6:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 6, 2010 1:53 PM
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
Post #756275
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse