We 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 ;
