SQL deadlock on delete then bulk insert

Question

I have an issue with a deadlock in SQL Server that I haven't been able to resolve.

Basically I have a large number of concurrent connections (from many machines) that are executing transactions where they first delete a range of entries and then re-insert entries within the same range with a bulk insert.

Essentially, the 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

The bulk insert only inserts items matching the Id and SubId of the deletion in the same transaction. Furthermore, these Id and SubId entries should never overlap.

When I have enough concurrent transaction of this form, I start to see a significant number of deadlocks between these statements.

I added the locking hints XLOCK HOLDLOCK to attempt to deal with the issue, but they don't seem to be helpling.

The canonical deadlock graph for this error shows:

Connection 1:

• Holds RangeX-X on PK_TableName
• Holds IX Page lock on the table
• Requesting X Page lock on the table

Connection 2:

• Holds IX Page lock on the table
• Requests RangeX-X lock on the table

What do I need to do in order to ensure that these deadlocks don't occur.

I have been doing some reading on the RangeX-X locks and I'm not sure I fully understand what is going on with these. Do I have any options short of locking the entire table here?

Answer 1

Its hard to give you an accurate answer without having a list of indexes / table size etc, however keep in mind that SQL can not grab multiple locks at the same instance. It will grab locks one at at time, and if another connection already holds the lock and it holds a lock to something the first transaction needs, kaboom you have a deadlock.

In this particular instance there are a few things you can do:

1. Ensure there is an index on (Id, SubId), that way SQL will be able to grab a single range lock for the data being deleted.
2. If deadlocks become rare, retry your deadlocks.
3. You can approach this with a sledghammer and use a TABLOCKX which will not deadlock ever
4. Get an accurate deadlock analysis using trace flag 1204 http://support.microsoft.com/kb/832524 (the more info you have about the actual deadlock the easier it is to work around)

Answer 2

Following on from Sam Saffron's answer:

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