February 23, 2009 at 12:00 am
Hi,
We was facing the deadlock issue in the below given query....
SELECT DISTINCT *,
t2.INSTANCEID,
t2.CONTEXTID
FROM
os_prod_processaudit.dbo.WMPROCESS t2,
(SELECT INSTANCEID as inst, MAX(t76.AUDITTIMESTAMP) as stamp FROM os_prod_processaudit.dbo.WMPROCESS t76 WHERE t76.PROCESSKEY like '%PCIUMP74JMZ%'
group by INSTANCEID) as b
WHERE
t2.PROCESSKEY like '%PCIUMP74JMZ%' AND
t2.AUDITTIMESTAMP=b.stamp AND b.inst=t2.INSTANCEID and
t2.AUDITTIMESTAMP < '1233545406000' AND
t2.STATUS IN ('2') AND
t2.PARENTINSTANCEID IS NULL
Then we tried to run the above select queries with "NO LOCK" and now the problem is resolved (as we make the resource as sharable).
But the concern is No lock may leads to many problems in DB so ……is there any way to use No lock with some kind of commitment to prevent inconsistency /Duplication.
The current scenario of the table (WMPROCESS) is like ….the rows are not getting updated frequently. And the above mentioned query is used inside a purge job.
February 23, 2009 at 12:47 am
Enable traceflag 1222. It causes the deadlock monitor to write a deadlock graph into the error log whenever a deadlock occurs. The graph will show both processes involved in the deadlock, along with the resources they deadlocked over. The graph has enough info to find the cause of the deadlock.
If you're not sure, post the graph here and we'll help.
Things to check first.
Do you have appropriate indexes to support this query?
Is that distinct really necessary?
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
February 23, 2009 at 1:41 am
Please below find the errorlog trace out by DBCC
2009-01-11 21:52:12.57 spid4 -- next branch --,0
2009-01-11 21:52:12.57 spid4 ,0
2009-01-11 21:52:12.57 spid4 ,0
2009-01-11 21:52:12.57 spid4 Node:2,0
2009-01-11 21:52:12.57 spid4 Port: 0x802ce100 Xid Slot: -1, EC: 0xabf43600, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeClose,0
2009-01-11 21:52:12.57 spid4 Coordinator: EC = 0xabf43600, SPID: 61, ECID: 0, Not Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer List::,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 0, EC = 0xa7e0c0c0, SPID: 61, ECID: 1, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 1, EC = 0x3403e0c0, SPID: 61, ECID: 4, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 2, EC = 0x33ffc0c0, SPID: 61, ECID: 6, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 3, EC = 0xa79a40c0, SPID: 61, ECID: 8, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 4, EC = 0x3401e0c0, SPID: 61, ECID: 7, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 5, EC = 0xa79c60c0, SPID: 61, ECID: 5, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 9, EC = 0xa7de20c0, SPID: 61, ECID: 3, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 13, EC = 0xa7f840c0, SPID: 61, ECID: 2, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer List::,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 6, EC = 0x3492a0c0, SPID: 61, ECID: 17, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 7, EC = 0x39ff60c0, SPID: 61, ECID: 20, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 8, EC = 0x3493a0c0, SPID: 61, ECID: 23, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 10, EC = 0x5a6040c0, SPID: 61, ECID: 18, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 11, EC = 0xa79ac0c0, SPID: 61, ECID: 24, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 12, EC = 0xa79d40c0, SPID: 61, ECID: 21, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 14, EC = 0x3495c0c0, SPID: 61, ECID: 22, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 15, EC = 0x349120c0, SPID: 61, ECID: 19, Blocking,0
2009-01-11 21:52:12.57 spid4 ,0
2009-01-11 21:52:12.57 spid4 -- next branch --,0
2009-01-11 21:52:12.57 spid4 ,0
2009-01-11 21:52:12.57 spid4 ,0
2009-01-11 21:52:12.57 spid4 Node:2,0
2009-01-11 21:52:12.57 spid4 Port: 0x802ce100 Xid Slot: -1, EC: 0xabf43600, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeClose,0
2009-01-11 21:52:12.57 spid4 Coordinator: EC = 0xabf43600, SPID: 61, ECID: 0, Not Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer List::,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 0, EC = 0xa7e0c0c0, SPID: 61, ECID: 1, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 1, EC = 0x3403e0c0, SPID: 61, ECID: 4, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 2, EC = 0x33ffc0c0, SPID: 61, ECID: 6, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 3, EC = 0xa79a40c0, SPID: 61, ECID: 8, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 4, EC = 0x3401e0c0, SPID: 61, ECID: 7, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 5, EC = 0xa79c60c0, SPID: 61, ECID: 5, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 9, EC = 0xa7de20c0, SPID: 61, ECID: 3, Blocking,0
2009-01-11 21:52:12.57 spid4 Consumer: Xid Slot: 13, EC = 0xa7f840c0, SPID: 61, ECID: 2, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer List::,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 6, EC = 0x3492a0c0, SPID: 61, ECID: 17, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 7, EC = 0x39ff60c0, SPID: 61, ECID: 20, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 8, EC = 0x3493a0c0, SPID: 61, ECID: 23, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 10, EC = 0x5a6040c0, SPID: 61, ECID: 18, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 11, EC = 0xa79ac0c0, SPID: 61, ECID: 24, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 12, EC = 0xa79d40c0, SPID: 61, ECID: 21, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 14, EC = 0x3495c0c0, SPID: 61, ECID: 22, Blocking,0
2009-01-11 21:52:12.57 spid4 Producer: Xid Slot: 15, EC = 0x349120c0, SPID: 61, ECID: 19, Blocking,0
2009-01-11 21:52:12.57 spid4 Victim Resource Owner:,0
February 23, 2009 at 7:20 am
What traceflags do you have enabled? That doesn't look like the output from 1222. Looks more like 1204 or 1205
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
February 23, 2009 at 9:56 pm
yes that was 1204....because we have SQL server is 2000.
February 24, 2009 at 1:14 am
nivedita.kathal (2/23/2009)
yes that was 1204....because we have SQL server is 2000.
Please post SQL 2000-related questions in the SQL 2000 forums. If you post in the 2005 forums, you're very likely to get 2005-specific solutions. (Like a 2005-specific traceflag)
There's nothing in that output I can see that's useful in any way. I'm not sure what would generate output like that. It's not a deadlock graph though.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply