1

enter image description hereWe are using Microsoft SQL Server 2017 Standard Edition (64-bit) on AWS and we are suffering from unexplained deadlocks events.

We are trying to update different unique rows from a different processes, the table and page escalation is disabled, the sqlserver using key lock and we still get a deadlock.

The data in the profiler showed that each process worked on a different row, so we do not understand why this deadlock occurred.

** We think that we managed to reproduce this deadlock in test tables ,but we still don't have an explanation for this deadlock (table and page escalation is disabled).

CREATE TABLE TestTable 
( 
ID CHAR(8) NOT NULL PRIMARY KEY,
Val CHAR(1) 
) 

--in session 1 
BEGIN TRANSACTION 
  DECLARE @INX bigint ;
  DECLARE @INX2 nchar(8);

  SET @INX = NEXT VALUE FOR SequenceExample ; 
  

  set @INX2 = @INX;

  Print (@INX2)

  WAITFOR DELAY '00:00:05'
 
  INSERT INTO [dbo].[TestTable]
           ([ID]
           ,[Val])
     VALUES
           (@INX2,'Y');

  WAITFOR DELAY '00:00:05' 

  UPDATE [dbo].[TestTable] 
  SET Val='X' 
  WHERE ID= @INX2
  
  WAITFOR DELAY '00:00:05' 
  
  COMMIT ;

--in session 2 
BEGIN TRANSACTION 
  DECLARE @INX bigint ;
  DECLARE @INX2 nchar(8);

  SET @INX = NEXT VALUE FOR SequenceExample ; 
  

  set @INX2 = @INX;

  Print (@INX2)

  WAITFOR DELAY '00:00:05'
 
  INSERT INTO [dbo].[TestTable]
           ([ID]
           ,[Val])
     VALUES
           (@INX2,'Y');

  WAITFOR DELAY '00:00:05' 

  UPDATE [dbo].[TestTable] 
  SET Val='X' 
  WHERE ID= @INX2
  
  WAITFOR DELAY '00:00:05' 
  
  COMMIT ;

9
  • 4
    Are you able to capture the XML deadlock graph and share it?
    – AMtwo
    Dec 1 '21 at 23:25
  • Are there any other indexes on the table? Triggers? Dec 2 '21 at 0:30
  • @AMtwo I added the graph
    – Edi
    Dec 2 '21 at 6:22
  • @ErikDarling There's another non-clustered unique index that includes two fields (Id and Val), no triggers.
    – Edi
    Dec 2 '21 at 6:27
  • Can you add the definition for the Sequence Dec 2 '21 at 8:55
1

so the deadlock issue was caused by a mismatch between the column data type char(8) and the variable datatype nchar(8) . the mismatch forced that DB to convert the data which led to deadlock when two transactions started at the same time, although that each one of them worked on another record.
the issue was solved after we update the column datatype to nchar .(same as the variable )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.