Same update on different rows results in deadlocking
1
vote
0
answers
180
views
We are testing our SQL Server database for concurrency issues and it appears that a single, simple update statement being run concurrently in transactions is resulting in deadlocks.
Here is the statement:
Since the primary key is one of the problem locks, here's the primary key's definition:
(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),
@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn
datetime,@UpdatedBy uniqueidentifier)
UPDATE [dbo].[TestAttempts]
Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,
[UpdatedBy]=@UpdatedBy
Where [Id]=@Id
Here's the diagram provided by the xdl we've retrieved for one of the deadlocks:

ALTER TABLE [dbo].[TestAttempts] ADD CONSTRAINT [PK_TestAttempts] PRIMARY KEY CLUSTERED
(
[Id] ASC,
[TestAdministrationId] ASC,
[TestTakerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
This concurrency test essentially involves the same code being run multiple times concurrently but updating different records. I'm fairly certain that there aren't any queries being run in the transaction prior to this update that would lock the key or the table. It's mainly a sequence of updates being performed, and this particular update only runs once in each process. So I'm confused as to how the same update query could deadlock here based only its primary key and the table itself. I'd expect each individual process to access the objects in the same sequence.
--- EDIT ---
As requested, here's the full DDL for the table and its indexes:
Create Table [dbo].[TestAttempts]
(
[Id] UniqueIdentifier Not Null,
[TestTakerId] Int Not Null,
[TestAdministrationId] UniqueIdentifier Not Null,
[TestFormName] NVARCHAR(50) NULL,
[DocumentId] UniqueIdentifier Null,
[TimeRemaining] TIME NULL,
[Score] Decimal(18, 5) Null,
[Pass] Bit Null,
[StartedOn] DateTime Null,
[EndedOn] DateTime Null,
[LocationName] NVARCHAR(255) NULL,
[KioskUsed] BIT NOT NULL DEFAULT 0,
[STAUsed] BIT NOT NULL DEFAULT 0,
[CreatedOn] DateTime Not Null,
[CreatedBy] UniqueIdentifier Not Null,
[UpdatedOn] DateTime Null,
[UpdatedBy] UniqueIdentifier Null,
[Notes] NTEXT NULL,
[WhiteboardHistory] NTEXT NULL,
Constraint [FK_TestAttempts_Documents] Foreign Key ([DocumentId]) References [dbo].[Documents]([Id]),
Constraint [PK_TestAttempts] Primary Key Clustered ([Id], [TestAdministrationId], [TestTakerId])
) On [PRIMARY]
Go
Create Index [IX_TestAttempts_TestAdministrationId_TestTakerId] On [dbo].[TestAttempts]([TestAdministrationId], [TestTakerId])
Go
Create Index [IX_TestAttempts_DocumentId] On [dbo].[TestAttempts]([DocumentId])
GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K2_K3_K1_K10] ON [dbo].[TestAttempts]
(
[TestTakerId] ASC,
[TestAdministrationId] ASC,
[Id] ASC,
[EndedOn] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K1_K3_K4] ON [dbo].[TestAttempts]
(
[Id] ASC,
[TestAdministrationId] ASC,
[TestFormName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [_dta_index_TestAttempts_17_591456956__K10_K3_K2_K1_K9] ON [dbo].[TestAttempts]
(
[EndedOn] ASC,
[TestAdministrationId] ASC,
[TestTakerId] ASC,
[Id] ASC,
[StartedOn] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
And the full XDL deadlock graph:
unknown
unknown
(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn datetime,@UpdatedBy uniqueidentifier)UPDATE [dbo].[TestAttempts] Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,[UpdatedBy]=@UpdatedBy Where [Id]=@Id
unknown
unknown
(@Version nvarchar(7),@TestAttemptId uniqueidentifier,@TestFormName nvarchar(1),@ActorId uniqueidentifier,@TimeStamp datetime,@Id uniqueidentifier,@UpdatedOn datetime,@UpdatedBy uniqueidentifier)UPDATE [dbo].[TestAttempts] Set [TestFormName]=@TestFormName,[UpdatedOn]=@UpdatedOn,[UpdatedBy]=@UpdatedBy Where [Id]=@Id
I've also pasted a query plan here (Note it's not the exact plan from the executions that caused the deadlocks; they are no longer available. It's a plan that results on the same database from running the same update statement):
https://www.brentozar.com/pastetheplan/?id=rJLNaP7R2
Asked by theta-fish
(111 rep)
Sep 3, 2023, 09:18 PM
Last activity: Sep 4, 2023, 03:08 PM
Last activity: Sep 4, 2023, 03:08 PM