Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mdellamalva
Starting Member
2 Posts |
 Posted - 2010-01-21 : 12:54:56
|
| I am experiencing deadlocks within an Enterprise level application.I have turned on Deadlock Detection in DBCC and see different deadlock situations.In one situation the Input Buf: Language Event: from Node 1 indicates a select statement from one table and the Input Buf: Language Event: from Node 2 indicates an update to another tableIn another the Input Buf: Language Event: from Node 1 indicates an update to one row in a table and the Input Buf: Language Event: from Node 2 also indicates an update to a different row in the same table.Here are my questions:Does the Input Buf: Language Event: contain the offending sql or is this the previous sql before the deadlock was detected - my understanding is that these are the offending sqls.If these are the offending sqls how can they end up in deadlocks?In scenario 1 the select statement and the update statements are from two different tables - there should be no conflict here. What am I missing?In scenario 2 the update statements are for different rows in the same table. Does an update use page level locking or row level locking? Will a clustered index prevent the deadlock in this scenario? Any recommendations?This of course is occuring at a customer's site and can not be replicated in our development environment.I am including the details from the deadlocks detected:****************************Scenario 1:2010-01-21 09:36:12.92 spid4 02010-01-21 09:36:12.92 spid4 Wait-for graph 02010-01-21 09:36:12.92 spid4 02010-01-21 09:36:12.92 spid4 Node:1 02010-01-21 09:36:12.92 spid4 KEY: 6:1237579447:1 (ac004019f995) CleanCnt:2 Mode: Range-S-U Flags: 0x0 02010-01-21 09:36:12.92 spid4 Grant List 1:: 02010-01-21 09:36:12.92 spid4 Owner:0x33528a60 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:248 ECID:0 02010-01-21 09:36:12.92 spid4 SPID: 248 ECID: 0 Statement Type: SELECT Line #: 1 02010-01-21 09:36:12.92 spid4 Input Buf: Language Event: select psempm.empid empid, psempm.seq seq, psempm.name name, psempm.ssn ssn, psempm.seniority seniority, psempm.position position, psempm.deptwc deptw 0c, psempm.gpn gpn, psempm.crew crew, psemp 12010-01-21 09:36:12.92 spid4 Grant List 2:: 02010-01-21 09:36:12.92 spid4 Requested By: 02010-01-21 09:36:12.92 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:245 ECID:0 Ec:(0x2D373510) Value:0x42c22400 Cost:(0/1634) 02010-01-21 09:36:12.92 spid4 02010-01-21 09:36:12.92 spid4 Node:2 02010-01-21 09:36:12.92 spid4 KEY: 6:1237579447:4 (8d013a13bead) CleanCnt:2 Mode: X Flags: 0x0 02010-01-21 09:36:12.92 spid4 Grant List 2:: 02010-01-21 09:36:12.92 spid4 Owner:0x3727d500 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:245 ECID:0 02010-01-21 09:36:12.92 spid4 SPID: 245 ECID: 0 Statement Type: UPDATE Line #: 1 02010-01-21 09:36:12.92 spid4 Input Buf: Language Event: update pstrne set pstrne.function = (convert(numeric(5,2), convert(decimal, (DateDiff(ss,pstrnx.date1,pstrnx.date2) - DateDiff(ss,pstrne.workday,pstrne.wlunch))) / 3600.00)) from pstrnx w 0here (pstrne.empid = '003035') and (pstrne.workday = '01/21/ 12010-01-21 09:36:12.92 spid4 Requested By: 02010-01-21 09:36:12.92 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:248 ECID:0 Ec:(0x2D2D5570) Value:0x7bca2c60 Cost:(0/0) 02010-01-21 09:36:12.92 spid4 Victim Resource Owner: 02010-01-21 09:36:12.92 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:248 ECID:0 Ec:(0x2D2D5570) Value:0x7bca2c60 Cost:(0/0) 02010-01-21 09:36:12.92 spid4 02010-01-21 09:36:12.92 spid4 End deadlock search 11200 ... a deadlock was found. 0***********************************Scenario 2:Deadlock encountered .... Printing deadlock information 02010-01-20 15:31:33.96 spid4 02010-01-20 15:31:33.96 spid4 Wait-for graph 02010-01-20 15:31:33.96 spid4 02010-01-20 15:31:33.96 spid4 Node:1 02010-01-20 15:31:33.96 spid4 TAB: 6:389576426 [] CleanCnt:3 Mode: S Flags: 0x0 02010-01-20 15:31:33.96 spid4 Grant List 0:: 02010-01-20 15:31:33.96 spid4 Grant List 2:: 02010-01-20 15:31:33.96 spid4 Owner:0x69501700 Mode: S Flg:0x0 Ref:2 Life:02000000 SPID:324 ECID:0 02010-01-20 15:31:33.96 spid4 SPID: 324 ECID: 0 Statement Type: UPDATE Line #: 1 02010-01-20 15:31:33.96 spid4 Input Buf: Language Event: update psdcs set psdcs.flag = 'C' where (psdcs.psdcsid = 580079) and (psdcs.flag <> 'E' ) 02010-01-20 15:31:33.96 spid4 Grant List 3:: 02010-01-20 15:31:33.96 spid4 Requested By: 02010-01-20 15:31:33.96 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:122 ECID:0 Ec:(0x3C7C1518) Value:0x67b36800 Cost:(0/28EC) 02010-01-20 15:31:33.96 spid4 02010-01-20 15:31:33.96 spid4 Node:2 02010-01-20 15:31:33.96 spid4 TAB: 6:389576426 [] CleanCnt:3 Mode: S Flags: 0x0 02010-01-20 15:31:33.96 spid4 Grant List 0:: 02010-01-20 15:31:33.96 spid4 Owner:0x41cc1e80 Mode: S Flg:0x0 Ref:2 Life:02000000 SPID:122 ECID:0 02010-01-20 15:31:33.96 spid4 SPID: 122 ECID: 0 Statement Type: UPDATE Line #: 1 02010-01-20 15:31:33.96 spid4 Input Buf: Language Event: update psdcs set psdcs.flag = 'C' where (psdcs.psdcsid = 580081) and (psdcs.flag <> 'E' ) 02010-01-20 15:31:33.96 spid4 Grant List 2:: 02010-01-20 15:31:33.96 spid4 Grant List 3:: 02010-01-20 15:31:33.96 spid4 Requested By: 02010-01-20 15:31:33.96 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:324 ECID:0 Ec:(0x6122B510) Value:0x71c806e0 Cost:(0/2368) 02010-01-20 15:31:33.96 spid4 Victim Resource Owner: 02010-01-20 15:31:33.96 spid4 ResType:LockOwner Stype:'OR' Mode: IX SPID:324 ECID:0 Ec:(0x6122B510) Value:0x71c806e0 Cost:(0/2368) 02010-01-20 15:31:33.96 spid4 02010-01-20 15:31:33.96 spid4 End deadlock search 10616 ... a deadlock was found. 02010-01-20 15:31:33.96 spid4 ---------------------------------- 0 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
agiotti
Starting Member
37 Posts |
Posted - 2010-01-29 : 23:16:03
|
| Couple of questions.1. what is the index strategy for scenario 22. is the calling application in scenario 1 a COM+ application? |
 |
|
|
|
|
|
|
|
Deadlocks