Deadlock with insert and delete

Deadlock graph:

  
    
  
  
    
      
        
unknown    
        
unknown    
      
      
(@param0 int,@param1 int,@param2 int,@param3 real,@param4 int,@param5 int,@param6 int,@param7 nvarchar(21),@param8 int,@param9 nvarchar(7),@param10 datetime,@param11 datetime,@param12 nvarchar(14),@param13 int,@param14 int,@param15 int,@param16 real,@param17 int,@param18 int,@param19 int,@param20 nvarchar(21),@param21 int,@param22 nvarchar(7),@param23 datetime,@param24 datetime,@param25 nvarchar(14),@param26 int,@param27 int,@param28 int,@param29 real,@param30 int,@param31 int,@param32 int,@param33 nvarchar(21),@param34 int,@param35 nvarchar(12),@param36 datetime,@param37 datetime,@param38 nvarchar(14),@param39 int,@param40 int,@param41 int,@param42 real,@param43 int,@param44 int,@param45 int,@param46 nvarchar(21),@param47 int,@param48 nvarchar(12),@param49 datetime,@param50 datetime,@param51 nvarchar(14),@param52 int,@param53 int,@param54 int,@param55 real,@param56 int,@param57 int,@param58 int,@param59 nvarchar(21),@param60 int,@param61 nvarchar(7),@param62 datetime,@param63 datetime,@param64 nvarchar(14),  /*INSERT INTO TradeBuffer_US_PC .... VALUES ...*/
 
    
    
      
        
unknown    
      
      
SET QUOTED_IDENTIFIER ON
DECLARE @LastID INT
SELECT TOP 1 @LastID = ID FROM [TradeBuffer_US_PC] ORDER BY ID DESC

IF @LastID IS NOT NULL
BEGIN
    BEGIN TRY
        ...
        DELETE FROM TradeBuffer_US_PC WHERE ID 
    
  
  
    
      
        
      
      
        
      
    
    
      
        
      
      
        
      
    
  

ID is a primary identity key.

The first process is doing a multi insert (INSERT INTO ... VALUES(...), (...), (...)) and the second process is doing a delete on the same table.

Second process is merging the buffer table to the real table and purging the buffer table.

The buffer table has a unique key defined with IGNORE_DUP_KEY on. So the multi insert might contain duplicated rows that's already in the buff

The insert process (process21881f1bc28) has a partial range lock on keys in the nonclustered index of the table TradeBuffer_US_PC, but is waiting on the rest of the keys in the range.

The delete process (process2187b184108) has an exclusive lock on one of the keys in that same range, and is waiting on a lock to delete one of the other keys in that range.

Related to that, check out this extremely detailed blog post:

Locking in Microsoft SQL Server (Part 20) – Range lock (RangeS-U) deadlock due to IGNORE_DUP_KEY index option

The author there demonstrates that inserts into tables with IGNORE_DUP_KEY nonclustered indexes can lead to SERIALIZABLE locking behavior on the nonclustered index keys. This is the most restrictive locking behavior SQL Server offers, and is thus least conducive to concurrency.

Unfortunately, this means you have some work on your hands to alleviate this deadlock situation. Here are some general ideas that might help, without having seen the code involved:

-
Your lowest effort option is to delete rows in smaller batches than you are currently. This would at least limit the number of deadlocks you encounter.

-
A likely larger change would be to explicitly use the SERIALIZBLE isolation level in the delete process, and then do a SELECT on the entire range of keys that you expect to delete at the very beginning. This should acquire the range of locks you need to delete.

-
Another option would be to take advantage of ordering. Change both the insert and delete processes so that they are operating on the data in the same order (key order ascending, for instance). This will hopefully allow locks to be taken in the same order, and thus they will be less likely to deadlock (and will instead just wait on the incompatible locks to be released).

PS: I noticed this in the process node of the insert process:

isolationlevel="read uncommitted (1)"

I'm not sure why you're using READ UNCOMMITTED for this data modification query, but it's likely not doing what you hope it does.

StackExchange Database Administrators Q#224581, answer score: 5