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:42:52
|
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 |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-30 : 11:52:35
|
Here is What's going onIn First Scenerio.a)Both Nodes (SPID 248 and 245) are accessing same table Which is retrieved from KEY- 6:1237579447:1 Where 6(DBID),1237579447(ObjectId) and 4(IndId).You can use this to findSelect * from Sys.sysobjects Where ObjectId = 1237579447 and similar for indexes in Sysindexes table. SPID 248 has Shared Lock (S) and SPID 245 has Exclusive Locks(X).SPID 248 is requesting X lock on table Where as SPID 245 is requesting S lock on table. Your Update must be performing Clustered Index Scan which locks other indexes. Check the Execution plan for the Update and Select.To make Update use different route, you need to have index on Empid and Workday and Should drop unnecessary index.2)Second Scenerio: Both SPIDs are trying to update same table. KEY-6:389576426SPID 324 has S lock but requesting Intent ExclusiveLock (IX) lock on SPID 122. SPID 122 has S lock but requesting Intent ExclusiveLock (IX) lock on SPID 324 and Hence Deadlock Occurs.Both SPIDs are trying lock same indexes. Do you have indexes on Psdcid? |
|
|
|
|
|
|
|