Deadlock vicitm detected with two UPDLOCKS



  • How is this possible? For my understanding if i already have an UPDLOCK in the first process. The second process needs to wait or hang in the select, during the time the first process are not commiting the transaction.

    Deadlockgraph:

    Deadlock-Graph

    
    
    
    
    
    
    unknown 
    
    unknown 
    
    
    (@P0 datetime2,@P1 int,@P2 bigint,@P3 nvarchar(4000),@P4 bigint)UPDATE SYS_SCHED_SEQUENCE SET mod_ts = @P0, seq_next = @P1, VERSION = @P2 WHERE ((IDENT = @P3) AND (VERSION = @P4)) 
    
    
    
    
    unknown 
    
    unknown 
    
    
    (@P0 nvarchar(4000))SELECT IDENT, seq_end, gen_ts, mod_ts, seq_next, seq_start, VERSION FROM SYS_SCHED_SEQUENCE WITH (UPDLOCK) WHERE (IDENT = @P0) 
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    

    Edit: We already found the answer in the Excution Plan as in the comments got suggestions. The Executionplan was unexpectedly using an Index Seek wich causing the whole Key to be locked and not a single Row. This happend because of different Types in the Preparedstatement "nvarch" and the column definition type "varchar".



  • How is this possible?

    SQL Server uses mostly row-level locking, and so both queries start running and taking U locks on the keys they read (one because it's an UPDATE statement, and the other because it's a SELECT with UPDLOCK hint).

    Soon, each process owns U locks on many different keys. Then one session needs a U lock on a key locked by the other and blocks. Then the unblocked session needs a U lock on a key owned by the blocked session, which is a deadlock.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2