December 11, 2007 at 2:55 am
that was excellent Gail.
"Keep Trying"
December 12, 2007 at 12:52 pm
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
December 12, 2007 at 4:44 pm
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 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x4BA99588) Value:0x310
2007-12-11 08:56:37.52 spid4 Victim Resource Owner:
2007-12-11 08:56:37.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:159 ECID:0 Ec:(0x4FED1588) Value:0x3
2007-12-11 08:56:37.52 spid4 Requested By:
2007-12-11 08:56:37.52 spid4 Input Buf: Language Event: select ACCOUNT_OPERATIONS,BUSORG,BUSPARTNER,COSTCENT
2007-12-11 08:56:37.52 spid4 SPID: 77 ECID: 0 Statement Type: INSERT Line #: 5
2007-12-11 08:56:37.52 spid4 Owner:0x30a02200 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:77 ECID:0
2007-12-11 08:56:37.52 spid4 Grant List 0::
2007-12-11 08:56:37.52 spid4 PAG: 10:1:4099 CleanCnt:2 Mode: S Flags: 0x2
2007-12-11 08:56:37.52 spid4 Node:2
2007-12-11 08:56:37.52 spid4
2007-12-11 08:56:37.52 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:77 ECID:0 Ec:(0x4BA99588) Value:0x310
2007-12-11 08:56:37.52 spid4 Requested By:
2007-12-11 08:56:37.52 spid4 Input Buf: Language Event: BEGIN TRANSACTION; Execute Ins3OPERATIONS N'BS_PLUG'
2007-12-11 08:56:37.52 spid4 SPID: 159 ECID: 0 Statement Type: INSERT Line #: 9
2007-12-11 08:56:37.52 spid4 Owner:0x607e9720 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:159 ECID:0
2007-12-11 08:56:37.52 spid4 Grant List 1::
2007-12-11 08:56:37.52 spid4 PAG: 10:1:105728 CleanCnt:2 Mode: IX Flags: 0x2
2007-12-11 08:56:37.52 spid4 Node:1
2007-12-11 08:56:37.52 spid4
2007-12-11 08:56:37.52 spid4 Wait-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
December 13, 2007 at 1:18 am
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
December 13, 2007 at 11:32 am
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
December 14, 2007 at 2:26 am
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
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply