Edit: In case it is relevant, we are connecting to the database via JDBC in a java webapp. We use autocommit=off
for all of these operations, and deadlocks mean the job thread is not committed.
I am trying to solve an intermittent deadlock issue in SQLServer. I have a deadlock report and am having trouble understanding how two queries are even in contention.
There are two threads involved: One has acquired an X
lock on a table called entities
before it then attempts to acquire a U
lock on a table called revisions
. Meanwhile, another thread has an X
lock on revisions
and then is trying to get a U
lock on entities
. I'm going to focus on the entities
table for this question.
What is confusing me here is that I believe I found the query that created the X
lock on entities
, but I cannot see how this lock conflicts with the U
request.
Here is an example query for what I'm guessing is the X lock query:
INSERT INTO entities(
uuid,
entity_type,
internal_id,
date_modified) VALUES (
@P0,
'USER',
null,
getdate()
)
There are two indexes on this table: The primary key (uuid), and the unique index (entity_type, internal_id).
The deadlock report claims that the index involved is a unique index that covers (entity_type, internal_id). Testing has confirmed that the above statement does indeed create an X lock for the row using the clustered index (uuid), but it also creates an X lock for the unique index. I've tested this in SSMS using a transaction, and then querying the sys.dm_tran_locks
table to see what locks were created.
However, the query that is creating a U lock on the entities
table does not appear to lock on that index at all, as it only creates a lock on the primary key. Here's the query:
UPDATE entities SET
head_rev_id = @P0,
date_modified = getdate()
WHERE uuid = @P1
It should be noted that I cannot confirm what the actual parameters that are in both queries. Based on the context of how these queries are eventually executed, however, we cannot see a logical reason how they could be the same. (Side question: Is there a way I can see the parameters that are passed into the query? I've enabled TRACE 1222 but I haven't gotten any more details other than the deadlock report that was in the extended events.) I tried updating the same entity in a second transaction, and the sys.dm_tran_locks
view indicates that it has an X lock in WAITING status using only the clustered index.
Here is the deadlock report xml. Maybe I'm reading this wrong, but what else can I look for?
<deadlock>
<victim-list>
<victimProcess id="process476f3dc38" />
</victim-list>
<process-list>
<process id="process476f3dc38" taskpriority="0" logused="1768" waitresource="KEY: 66:72057594135838720 (c3a138dec5f7)" waittime="4831" ownerId="7413985" transactionname="implicit_transaction" lasttranstarted="2020-01-06T17:17:18.320" XDES="0x46f5363a8" lockMode="U" schedulerid="4" kpid="3064" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-01-06T17:17:18.440" lastbatchcompleted="2020-01-06T17:17:18.437" lastattention="1900-01-01T00:00:00.437" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" loginname="xxx" isolationlevel="read committed (2)" xactid="7413985" currentdb="66" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x0200000045fed007bf27432ade4d4e079e599714526613ff0000000000000000000000000000000000000000">
UPDATE revisions SET state = @P0 WHERE uuid = @P1 </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000))UPDATE revisions SET state = @P0 WHERE uuid = @P1 </inputbuf>
</process>
<process id="process476f37c38" taskpriority="0" logused="10568" waitresource="KEY: 66:72057594133544960 (4c37a96d5030)" waittime="4831" ownerId="7413919" transactionname="implicit_transaction" lasttranstarted="2020-01-06T17:17:18.290" XDES="0x4744323a8" lockMode="U" schedulerid="7" kpid="1920" status="suspended" spid="85" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-01-06T17:17:18.440" lastbatchcompleted="2020-01-06T17:17:18.440" lastattention="1900-01-01T00:00:00.440" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxx" hostpid="0" loginname="xxx" isolationlevel="read committed (2)" xactid="7413919" currentdb="66" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="78" sqlhandle="0x02000000ab882b34a7f1c9e08c7ff4a42d357133d4972a960000000000000000000000000000000000000000">
UPDATE entities SET
head_rev_id = @P0,
date_modified = getdate()
WHERE uuid = @P1 </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000))UPDATE entities SET
head_rev_id = @P0,
date_modified = getdate()
WHERE uuid = @P1 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594135838720" dbid="66" objectname="revisions" indexname="revisions_parent_idx" id="lock2cfaf0e00" mode="X" associatedObjectId="72057594135838720">
<owner-list>
<owner id="process476f37c38" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process476f3dc38" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594133544960" dbid="66" objectname="entities" indexname="unique_entities" id="lock2e75ddc80" mode="X" associatedObjectId="72057594133544960">
<owner-list>
<owner id="process476f3dc38" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process476f37c38" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
Here is the schema for the two tables in question.
CREATE TABLE [entities]
(
[uuid] [varchar](100) NOT NULL,
[entity_type] [varchar](100) NOT NULL,
[internal_id] [varchar](255) NULL,
[date_modified] [datetime2](6) NOT NULL,
[head_rev_id] [varchar](100) NULL,
[test] [varchar](100) NULL,
CONSTRAINT [entities_pk] PRIMARY KEY CLUSTERED
(
[uuid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [unique_entities] UNIQUE NONCLUSTERED
(
[entity_type] ASC,
[internal_id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ALTER TABLE [entities]
WITH CHECK ADD CONSTRAINT [entities_revision_fk] FOREIGN KEY ([head_rev_id])
REFERENCES [revisions] ([uuid])
ALTER TABLE [entities]
WITH CHECK ADD CONSTRAINT [entity_types_entities_fk] FOREIGN KEY ([entity_type])
REFERENCES [entity_types] ([entity_type])
ON DELETE CASCADE
CREATE TABLE [revisions]
(
[entity_id] [varchar](100) NOT NULL,
[uuid] [varchar](100) NOT NULL,
[parent_uuid] [varchar](100) NULL,
[priority] [numeric](20, 0) NOT NULL,
[state] [varchar](50) NOT NULL,
CONSTRAINT [revisions_pk] PRIMARY KEY CLUSTERED
(
[uuid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ALTER TABLE [revisions]
WITH CHECK ADD CONSTRAINT [rev_entity] FOREIGN KEY ([entity_id])
REFERENCES [entities] ([uuid])
ON DELETE CASCADE
ALTER TABLE [revisions]
WITH CHECK ADD CONSTRAINT [rev_rev_state] FOREIGN KEY ([state])
REFERENCES [revision_states] ([state])
CREATE NONCLUSTERED INDEX [revisions_entity_idx] ON [revisions]
(
[entity_id] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [revisions_parent_idx] ON [revisions]
(
[parent_uuid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX [u_duplicate_unassigned] ON [revisions]
(
[entity_id] ASC,
[state] ASC
)
WHERE ([state] = 'UNASSIGNED')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
transactionname="implicit_transaction"