SQL deadlock on delete then bulk insert
- by StarLite
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?
