How to avoid this dead lock?

Question

How to avoid this dead lock?

gary1

SSC-Insane

Points: 21152
More actions
May 3, 2010 at 3:02 pm

#144478

Hi,
We have K2 databases in SQL Server 2005 and frequently getting the below dead lock. Before this deadl lock occurs, sometimes I can see that the blocking happen & sometimes no blocking & only the below dead lock. Please advice on how to aviod this dead lock? what this dead lock graph is explaining?
2010-05-01 12:26:20.01 spid18s deadlock-list
2010-05-01 12:26:20.01 spid18s deadlock victim=process3c13c18
2010-05-01 12:26:20.01 spid18s process-list
2010-05-01 12:26:20.01 spid18s process id=process3c13c18 taskpriority=0 logused=0 waitresource=RID: 12:1:2409:0 waittime=515 ownerId=141390845 transactionname=SELECT lasttranstarted=2010-05-01T12:26:19.500 XDES=0xc6c97058 lockMode=S schedulerid=2 kpid=2180 status=suspended spid=75 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2010-05-01T12:26:19.500 lastbatchcompleted=2010-05-01T12:26:19.497 clientapp=.Net SqlClient Data Provider hostname=APPSRV2 hostpid=5344 loginname=abc\appsrv isolationlevel=read committed (2) xactid=141390845 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-05-01 12:26:20.01 spid18s executionStack
2010-05-01 12:26:20.01 spid18s frame procname=K2Server.dbo.kLogProcInst line=10 stmtstart=198 stmtend=434 sqlhandle=0x03000c006ee4c327a3d5ec001a9d00000100000000000000
2010-05-01 12:26:20.01 spid18s SELECT
TOP (@Count) ID,
Data
FROM
_Log
WHERE
ProcInstID = @ProcInstID
ORDER BY
ID ASC
2010-05-01 12:26:20.01 spid18s inputbuf
2010-05-01 12:26:20.01 spid18s Proc [Database Id = 12 Object Id = 667149422]
2010-05-01 12:26:20.01 spid18s process id=process3c26478 taskpriority=0 logused=96 waitresource=KEY: 12:72057594045071360 (3c009ce75fbe) waittime=515 ownerId=141390846 transactionname=DELETE lasttranstarted=2010-05-01T12:26:19.500 XDES=0x13ac78da0 lockMode=X schedulerid=4 kpid=5688 status=suspended spid=68 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-05-01T12:26:19.500 lastbatchcompleted=2010-05-01T12:26:19.500 clientapp=.Net SqlClient Data Provider hostname=APPSRV2 hostpid=5344 loginname=abc\appsrv isolationlevel=read committed (2) xactid=141390846 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2010-05-01 12:26:20.01 spid18s executionStack
2010-05-01 12:26:20.01 spid18s frame procname=K2Server.dbo.kLogRemove line=7 stmtstart=140 stmtend=260 sqlhandle=0x03000c001a973533b7d5ec001a9d00000100000000000000
2010-05-01 12:26:20.01 spid18s DELETE
_Log WITH (ROWLOCK)
WHERE
ID = @ID
2010-05-01 12:26:20.01 spid18s inputbuf
2010-05-01 12:26:20.01 spid18s Proc [Database Id = 12 Object Id = 859150106]
2010-05-01 12:26:20.01 spid18s resource-list
2010-05-01 12:26:20.01 spid18s ridlock fileid=1 pageid=2409 dbid=12 objectname=K2Server.dbo._Log id=lock4ea3980 mode=X associatedObjectId=72057594040221696
2010-05-01 12:26:20.01 spid18s owner-list
2010-05-01 12:26:20.01 spid18s owner id=process3c26478 mode=X
2010-05-01 12:26:20.01 spid18s waiter-list
2010-05-01 12:26:20.01 spid18s waiter id=process3c13c18 mode=S requestType=wait
2010-05-01 12:26:20.02 spid18s keylock hobtid=72057594045071360 dbid=12 objectname=K2Server.dbo._Log indexname=IX_Log id=lock4eb7b80 mode=U associatedObjectId=72057594045071360
2010-05-01 12:26:20.02 spid18s owner-list
2010-05-01 12:26:20.02 spid18s owner id=process3c13c18 mode=S
2010-05-01 12:26:20.02 spid18s waiter-list
2010-05-01 12:26:20.02 spid18s waiter id=process3c26478 mode=X requestType=convert

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

It tells you what the database ID is and it tells you the proc IDs AND it tells you the code snippets for the deadlock participants... next step is to open those two stored procedures and start looking them over. You might also want to search Books Online for troubleshooting deadlocks. They have some pretty decent information on the subject there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

This is what I would call an Intra-table deadlock. Most people think of a deadlock as between two process accessing two tables (inter-table) in different orders, but here the two "tables" are the index and the leaf levels of the same table.

The COUNT is accessing the index, then the leaf level, but the DELETE needs to delete the row, then update the index to reflect the delete. Since the order of accessing the data is reversed you get a deadlock.

One solution is to use the WITH (NOLOCK) on the COUNT, but this may give you inconsistent results.

Another option is to look at why you are deleting records from your _Log table. This looks like a clean up process, could it be moved to another time of day, like batch processing at night.

Consider creating a covering index on ID,ProcInstID which are probably INT data types, so not too big, but may keep the count out of the leaf levels.

There is typically no simple solution to this type of deadlock, you will need to test and see what happens.

Leo

Is it Friday yet?

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

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

I see mentions of K2. Is this a database for one of the K2 business process applications? If so, do you have permission from the vendor to make index or code changes to this database?

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

gary1 SSC-Insane Points: 21152 More actions · Answer 4

T

his is what I would call an Intra-table deadlock. Most people think of a deadlock as between two process accessing two tables (inter-table) in different orders, but here the two "tables" are the index and the leaf levels of the same table.
The COUNT is accessing the index, then the leaf level, but the DELETE needs to delete the row, then update the index to reflect the delete. Since the order of accessing the data is reversed you get a deadlock.
One solution is to use the WITH (NOLOCK) on the COUNT, but this may give you inconsistent results.
Another option is to look at why you are deleting records from your _Log table. This looks like a clean up process, could it be moved to another time of day, like batch processing at night.
Consider creating a covering index on ID,ProcInstID which are probably INT data types, so not too big, but may keep the count out of the leaf levels.
There is typically no simple solution to this type of deadlock, you will need to test and see what happens.

Thank you,

Here I can see the Procedure name that involved in the deadlock but I did find the TABLE name. From the dead lock graph, How can I find the Table name that the procedure is accessing?

I see mentions of K2. Is this a database for one of the K2 business process applications? If so, do you have permission from the vendor to make index or code changes to this database

I need to make sure that though. But I want to analyze the dead lock graph to find the reason for dead lock and let the developer know.

Thanks

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

These are the actual two lieces of code causing the problem:

The table is _Log

SELECT TOP (@Count) ID, Data, FROM _Log

WHERE ProcInstID = @ProcInstID

ORDER BY ID ASC

DELETE _Log WITH (ROWLOCK)

WHERE ID = @ID

Leo

Is it Friday yet?

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