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?