Deadlock troubleshooting in SQLServer

Question

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]

Answer 1

I have run across this type of deadlock before. The issue is a foreign key validation in the entities table to the revisions table is being blocked by the update to the revisions table. This is caused by the revisions missing a key to make one of the processes more efficient - probably a key on the uuid field - and it scanning the PK while the reference is being looked up. This blocks on the queries cross-requesting pages of the index (NOTE: This is my theory - I have not confirmed it, but operating as if it is true has corrected these issues for me in the past). I recommend:

CREATE INDEX Idx_Revisions_uuid ON revisions (uuid)

Possibly with whatever other INCLUDE data, or even index fields, would be best for you. Do remember that for this to work, the uuid field must be the first one in the index.