SQL deadlock on deletion and then insert insertion

Question

I have a deadlock problem in SQL Server that I could not solve.

Basically, I have a large number of concurrent connections (from many machines) that perform transactions where they first delete a range of records and then reinsert records within the same range with bulk insertion.

Essentially, a transaction looks like this:

BEGIN TRANSACTION T1 DELETE FROM [TableName] WITH( XLOCK HOLDLOCK ) WHERE [Id] =@Id AND [SubId] =@SubId INSERT BULK [TableName] ( [Id] Int , [SubId] Int , [Text] VarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(CHECK_CONSTRAINTS, FIRE_TRIGGERS) COMMIT TRANSACTION T1 

Bulk insert only inserts elements matching the Id and SubId deletes in the same transaction. In addition, these Id and SubId entries should never overlap.

When I have enough simultaneous transactions of this form, I begin to see a significant number of deadlocks between these statements.

I added XLOCK HOLDLOCK locking hints to try to deal with the problem, but they don't seem to be able to cope.

The canonical deadlock graph for this error shows:

Compound 1:

• Holds RangeX-X in PK_TableName
• Holds page lock on page
• X-page lock request in table

Compound 2:

• Holds page lock on page
• RangeX-X Lock Queries on a Table

What do I need to do to make sure that these deadlocks do not occur.

I read about RangeX-X locks, and I'm not sure I fully understand what happens to them. Do I have any options besides locking the whole table here?

Answer 1

Following from Sam Shaffron, answer:

• Consider the READPAST hint to skip any held locks if @ ID7 @SubID is distinc
• Consider SERIALIZABLE and remove XLOCK, HOLDLOCK
• Use a separate staging table for bulk paste, then copy from it

Answer 2

It is difficult to give you an exact answer without having a list of indexes / table size, etc., however keep in mind that SQL cannot capture multiple locks in a single instance. It will capture locks one at a time, and if the other connection already holds the lock, and it blocks what the first transaction requires, you have a dead end.

In this particular case, you can do several things:

• Make sure there is an index (Id, SubId) so that SQL can capture a single range lock for deleted data.
• If dead ends become rare, repeat your dead ends.
• You can approach this with sledghammer and use TABLOCKX, which will never lock.
• Get accurate deadlock analysis using trace flag 1204 http://support.microsoft.com/kb/832524 (the more information you have about the actual deadlock, the easier it is to work around)