A Deadlock Occurence and Resolution

Question

A Deadlock Occurence and Resolution

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

amiller (2/3/2009)
🙂 Very interesting and with many information I didn't know. One more, does the ALLOW_PAGE_LOCKS option on indexes in the tables involved matters for deadlocks?
thanks
Albert

Albert,

YES. These options make a difference. See the following thread where having ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS OFF caused deadlocking to occur.

http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/c5356da9-167e-4143-a64b-9a39dbc176b6/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

doobya (2/3/2009)
Jonathan Kehayias (2/3/2009)
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.
That is an interesting article.
I am surprised that the lock acquisition against a single table for a single statement isn't batched and serialized!

I am not sure what you mean in this statement? The SH locks are required to ensure consistency during the lookup process across resources. You can certainly change issolation levels and affect they types of locks taken by the SELECT, but that isn't necessarily a solution to the problem depending on the issolation level that you decide to use.

mordik SSC Veteran Points: 212 More actions · Answer 3

😀 thanks for taking the time to put this together...very useful

bvandenbrink Newbie Points: 7 More actions · Answer 4

"As there was only one non-clustered index existing on that table, I created a clustered index on the table on the ID column with this command"

I understand not having an index on the ID column.

Would creating a non-clustered index on this column haved solved the problem as well,

or did you have a specific reason to chose for the clusterred one.

blurpyblurp Old Hand Points: 348 More actions · Answer 5

Useful intro on identifying deadlocks.

The part that concerned me more was:

"As part of the upgrade, the application team performed modifications to the stored procedure code such that every insertion may cause up to 10 updates in the table."

Why would so many updates need to happen on the same table from a single insert? The sprocs/functions used in between must be low level and small to have caused so many updates?

If it's status updates, that's ALOT of status updates.

I guess as long as the code works now and it's no deadlocking it's ok?

harry9katz SSC Enthusiast Points: 110 More actions · Answer 6

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 SSChasing Mays Points: 610 More actions · Answer 7

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 SSC Enthusiast Points: 110 More actions · Answer 8

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 SSChasing Mays Points: 610 More actions · Answer 9

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 Ten Centuries Points: 1048 More actions · Answer 10

Very nice step wise article.

Thanks for sharing Praveen!

Rob-406527 SSC Eights! Points: 907 More actions · Answer 11

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 SSC Journeyman Points: 83 More actions · Answer 12

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 Newbie Points: 7 More actions · Answer 13

hunkwangshz

Newbie

Points: 7

July 19, 2009 at 6:59 pm

#1026672

Brilliant article and great comments!

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 14

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 Grasshopper Points: 11 More actions · Answer 15

Beside creating proper indexes, developers also need to lock tables in the same order all the time to avoid deadlock situation.

Derek