Dead lock

Question

Post reply

Dead lock

Viewing 6 posts - 16 through 20 (of 20 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

ChiragNS

One Orange Chip

Points: 26137

December 11, 2007 at 2:55 am

#758870

that was excellent Gail.

"Keep Trying"

California SSCommitted Points: 1888 More actions · Answer 2

Gail (Man of great knowledge) that was amazing. It certainly is very helpful and thanks a million for your great analysis. I am going to keep this analysis as a reference and shall follow this anytime i get into a deadlock error.

Really appreciate all your help here. Thanks for everyone.

Cali

California SSCommitted Points: 1888 More actions · Answer 3

Gail,

Hate to be back with the same issue. Here's another dead lock info i see in my error logs and failed to figure out as to which process is dead locked.

007-12-11 08:56:37.52 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x4BA99588) Value:0x310

2007-12-11 08:56:37.52 spid4Victim Resource Owner:

2007-12-11 08:56:37.52 spid4ResType:LockOwner Stype:'OR' Mode: IX SPID:159 ECID:0 Ec:(0x4FED1588) Value:0x3

2007-12-11 08:56:37.52 spid4Requested By:

2007-12-11 08:56:37.52 spid4Input Buf: Language Event: select ACCOUNT_OPERATIONS,BUSORG,BUSPARTNER,COSTCENT

2007-12-11 08:56:37.52 spid4SPID: 77 ECID: 0 Statement Type: INSERT Line #: 5

2007-12-11 08:56:37.52 spid4Owner:0x30a02200 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:77 ECID:0

2007-12-11 08:56:37.52 spid4Grant List 0::

2007-12-11 08:56:37.52 spid4PAG: 10:1:4099 CleanCnt:2 Mode: S Flags: 0x2

2007-12-11 08:56:37.52 spid4Node:2

2007-12-11 08:56:37.52 spid4

2007-12-11 08:56:37.52 spid4ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x4BA99588) Value:0x310

2007-12-11 08:56:37.52 spid4Requested By:

2007-12-11 08:56:37.52 spid4Input Buf: Language Event: BEGIN TRANSACTION; Execute Ins3OPERATIONS N'BS_PLUG'

2007-12-11 08:56:37.52 spid4SPID: 159 ECID: 0 Statement Type: INSERT Line #: 9

2007-12-11 08:56:37.52 spid4Owner:0x607e9720 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:159 ECID:0

2007-12-11 08:56:37.52 spid4Grant List 1::

2007-12-11 08:56:37.52 spid4PAG: 10:1:105728 CleanCnt:2 Mode: IX Flags: 0x2

2007-12-11 08:56:37.52 spid4Node:1

2007-12-11 08:56:37.52 spid4

2007-12-11 08:56:37.52 spid4Wait-for graph

2007-12-11 08:56:37.52 spid4

2007-12-11 08:56:37.52 spid4...

This is my interopation after reading this dead locks. I am reading the graph bottom to Top, since Node 1 is listed all the way bottom.

Processes 159 placed a IX lock while doing a Insert operation. The SQL error happened at Line#9 at the statement.

Processes 77 wants to place a S lock while doing the select. The SQL error happened at line#5 of the statement.

If this is write, then which proceses was rollback? If my interopation is wrong, then how do we read this and understand this chain?

appreciated any comments.

Thanks again,

Cali

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It doesn't usually matter which one was the victim when it comes to resolving deadlocks.

To stop them happening, you'll likely have to look at the code that the processes involved were running at the time, shorten the transactions, optimise the statements that are actually deadlocking, make sure resources are accessed in the same order.

As for the last one....

It's interesting that SPID 77 has an input buffer of select, but a statement type of Insert. Perhaps there's a trigger involved?

Spid 107 doing an insert on line 9 of the proc Ins3OPERATIONS

Have a look at the tables that own the locked pages (10:1:105728 and 10:1:4099) and have a look at the indexes on them. Take a look at the code involved in the deadlock and at its execution plans. See if there are any index scans, clustered index scans or table scans. See if you can tweak the indexs to support the queries.

If you're not sure, use index tuning wizard as a start point (not on your production server) bearing in mind that it's not always right.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

California SSCommitted Points: 1888 More actions · Answer 5

Gail thanks a ton for for the answer. I looked at the pages in questions and found out to be database tables. i have sent the relevant info to the developers and see, what they have to say.

Thanks again for all your help.

Cali

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 6

Also try to replace SELECT INTO statements

into CREATE TABLE ... INSERT INTO.

Select Into holds some exclusive locks on system tables.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1284087,00.html