SQL 2008 deadlocks on simple delete

Question

We're running a very small delete against our events table in SQL 2008 and it randomly creates a deadlock. We cannot recreate it on demand however. The delete statement is good for 2-5 rows max.

DELETE FROM Administration_NET.dbo.tblEvents Where EMSaccountID = 'DELE10A'

Any ideas?

Answer 1

run:

SET SET SHOWPLAN_ALL ON

and then your delete, is there a table scan? if so you have an index issue

Answer 2

Deadlock against itself or deadlock with another statement?

It can be a simple reader/writer deadlock based on index access order, see my link for a description. This is verly likely if EMSaccountID is a non-clustered index.

Answer 3

How many different types of statements are hitting the tblEvents table? Is it possible you have an insert/update/select statement running at the same time as your delete? You could try recreating the issue by running a large amount of inserts/updates/selects at the same time as attempting some deletes.

Answer 4

I found this KB article helpful in a similar situation.

Answer 5

I have stupid idea - maybe you set table Administration_NET.dbo.tblEvents to use cascade delete... In this case delete operation will set more locks on more tables.

Answer 6

I like Remus answer, if you are just looking for a functional solution however, then why no try an exclusive table lock on the delete, eg.

DELETE FROM Administration_NET.dbo.tblEvents with (tablockx) Where EMSaccountID = 'DELE10A'

An interesting and unexpected problem to raise, but if the delete is quick on the expected population and the exclusive table lock doesn't interfere with other high-frequency transactions upon the database, then this is may be a perfectly reasonable way to address the issue...

It would be very interesting if other contributors can highlight as to why this occurs however!

Answer 7

Nobody can help you out without richer information:

In particular, you must enable some trace flags so SQL Server will log the reason it rolled back the transaction in question.

So, enable deadlock tracing:

DBCC TRACEON(1204,1222)

(This setting will vanish when you cycle sql server, you can specify it in the startup options if required)

Once this setting is enabled, your SQL server log will contain information about the deadlock chain, it looks something like this:

2003-05-14 11:46:26.76 spid4     Starting deadlock search 1
2003-05-14 11:46:26.76 spid4     Target Resource Owner:
2003-05-14 11:46:26.76 spid4      ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4      Node:1       ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x43CAB580) Value:0x42bdf340
2003-05-14 11:46:26.76 spid4     
2003-05-14 11:46:26.76 spid4     End deadlock search 1 ... a deadlock was not found.
2003-05-14 11:46:26.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     ----------------------------------
2003-05-14 11:46:31.76 spid4     Starting deadlock search 2

Paste that information on your question, it is essential to diagnosing the issue.