Deadlocks are occuring

Question

Deadlocks are occuring

SQLQuest29

SSCrazy Eights

Points: 8264
More actions
February 27, 2012 at 11:08 am

#257495

Hi All,
We have a simple SP that is being called by our app which executes below sql :
SELECT JobID FROM ActiveArea WHERE JobStateID = 256 AND AllocatedByID = @OwnerID AND NOT EXISTS (SELECT JobID FROM TransactionRequests WHERE TransactionRequests.JobID = ActiveArea.JobID)
The above is being deadlocked by
UPDATE [ActiveArea] set [JobStateID] = @1,[OwnerID] = [AllocatedByID],[AllocatedByID] = NULL,[TransactionID] = @2,[LastUpdatedDT] = getutcdate() WHERE [JobID]=@3
This table has 3 indexes:
IX_ActiveArea --> NC --> CodeSourceID, SourceSystemID, JobStateID, SourceSequenceNumber, JobID
IX_ActiveArea2 --> NC --> JobStateID, CodeDestinationID, OriginalFileName, OwnerOverrideID, CreatedDT
PK_ActiveArea --> PK --> JobID
Can anyone provide any insight on how to resolve deadlocks ?
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

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

sengeet86 Ten Centuries Points: 1252 More actions · Answer 2

How to Track Down Deadlocks

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/

java programming

Leo.Miller SSChampion Points: 13051 More actions · Answer 3

Given that the UPDATE will first update the row, then update the indexes and the SELECT will work through the index and then fetch the row, and both the update and select are affecting/using the JobStateID a deadlock is likely.

It looks like the two indexes are the real issue and you may need to redesign these.

Without seeing a query plan or whet else is happening it's difficalt to say more.

Leo

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 4

GilaMonster (2/27/2012)
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)

Below is the deadlock xml

<TextData>

<deadlock-list>

<deadlock victim="processd18e0e08">

<process-list>

<executionStack>

<frame procname="*****.dbo.STORED_PROCEDURE_HIDDEN_PURPOSELY" line="1" stmtstart="122" sqlhandle="0x0300070098271d1c9465f300019f00000100000000000000">

SELECT JobID FROM ActiveArea WHERE JobStateID = 256 AND AllocatedByID = @OwnerID AND NOT EXISTS (SELECT JobID FROM TransactionRequests WHERE TransactionRequests.JobID = ActiveArea.JobID) </frame>

<frame procname="adhoc" line="1" sqlhandle="0x01000700e150981bd0172605010000000000000000000000">

STORED_PROCEDURE_HIDDEN_PURPOSELY '475' </frame>

</executionStack>

<inputbuf>

STORED_PROCEDURE_HIDDEN_PURPOSELY '475' </inputbuf>

</process>

<executionStack>

<frame procname="adhoc" line="1" stmtstart="44" sqlhandle="0x020000005ba7491b5e6fe204c31381ff53f2a5cd94ccb58a">

UPDATE [ActiveArea] set [JobStateID] = @1,[OwnerID] = [AllocatedByID],[AllocatedByID] = NULL,[TransactionID] = @2,[LastUpdatedDT] = getutcdate() WHERE [JobID]=@3 </frame>

<frame procname="adhoc" line="1" sqlhandle="0x0200000020d0d534c4996d9f548e9682ce24fbbb22a3d784">

UPDATE ActiveArea SET JobStateID = 262, OwnerID = AllocatedByID, AllocatedByID = NULL, TransactionID = 281465392, LastUpdatedDT = GetUTCDate() WHERE JobID = 60065491 </frame>

</executionStack>

<inputbuf>

UPDATE ActiveArea SET JobStateID = 262, OwnerID = AllocatedByID, AllocatedByID = NULL, TransactionID = 281465392, LastUpdatedDT = GetUTCDate() WHERE JobID = 60065491 </inputbuf>

</process>

</process-list>

<resource-list>

<keylock hobtid="72057594785759232" dbid="7" objectname="*****.dbo.ActiveArea" indexname="PK_ActiveArea" id="lockc2f82a80" mode="X" associatedObjectId="72057594785759232">

<owner-list>

<owner id="process12704d708" mode="X" />

</owner-list>

<waiter-list>

<waiter id="processd18e0e08" mode="S" requestType="wait" />

</waiter-list>

</keylock>

<keylock hobtid="72057594865844224" dbid="7" objectname="*****.dbo.ActiveArea" indexname="IX_ActiveArea2" id="lockc3568280" mode="S" associatedObjectId="72057594865844224">

<owner-list>

<owner id="processd18e0e08" mode="S" />

</owner-list>

<waiter-list>

<waiter id="process12704d708" mode="X" requestType="wait" />

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

</TextData>

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

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

Create a new nonclustered index (AllocatedByID, JobStateID) INCLUDE (JobID)

Should completely prevent the deadlocks.

This is what's can be called a key-lookup deadlock. The select takes a lock on the nonclustered index then wants a lock on the base table for the key lookup. The update first takes a lock on the base table, then wants locks on the nonclustered indexes. The fix is to make sure one of them only has one step, so create a covering index for the select.

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

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 6

GilaMonster (2/27/2012)
Create a new nonclustered index (AllocatedByID, JobStateID) INCLUDE (JobID)
Should completely prevent the deadlocks.
This is what's can be called a key-lookup deadlock. The select takes a lock on the nonclustered index then wants a lock on the base table for the key lookup. The update first takes a lock on the base table, then wants locks on the nonclustered indexes. The fix is to make sure one of them only has one step, so create a covering index for the select.

Thanks Gail. As always, you are very helpful.

I will implement this in on our test server. Can you tell me, what is the best start to have a thorough understanding on key lookup, etc -- query tuning stuff ? I really need to get my hands on it.

While I was examining the execution plan, the key lookup cost almost 33% of the query cost and was thinking of having a covering index.

Many thanks !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

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

Grant Fritchey's execution plans book (see books link in the side bar) and then Grant Fritchey's Query performance tuning distilled (purchase from your favourite book shop)

No, Grant does not give me commission on book sales. :hehe:

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