2

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]
6
  • try to run a SQL trace (SQL Server profiler) looking for deadlocks. You can find some information here : learn.microsoft.com/en-us/sql/tools/sql-server-profiler/…. With the graph you can clearly see what query is blocking who. and you should use SP_WHOISACTIVE. its a procedure that list everything running inside your sql server whoisactive.com
    – Racer SQL
    Jan 15, 2020 at 16:47
  • As a side note, do you know why implicit transactions are used? transactionname="implicit_transaction" Jan 15, 2020 at 17:01
  • @RandiVertongen I know nothing. I've learned everything I know about locks since yesterday.
    – Mirrana
    Jan 15, 2020 at 17:06
  • @RacerSQL It is my understanding that the SQL Profiler is deprecated, and is replaced by Extended Events, which is where I got the above xml. Nonetheless, I tried to follow the guide you linked, and there does not appear to be anything in my UI that matches the instructions given. Can deadlocks even be monitored using Profiler in SQLServer 2012?
    – Mirrana
    Jan 15, 2020 at 17:35
  • Are there any triggers on the tables?
    – Bob Klimes
    Jan 15, 2020 at 17:41

1 Answer 1

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.

2
  • The revisions table already has a clustered index for uuid (the primary key). It also has a non-clustered index on a foreign key back to the entities table (revisions.entity_id --> entities.uuid). Did you mean that I should add an index on the head_rev_id column of entities?
    – Mirrana
    Jan 15, 2020 at 17:59
  • In this case (where there is already an index on the field I thought was the problem), that would be well worth the try. Also, finding out what tables and indexes the referenced deadlocked keys are on would be useful. See this post by Kendra Little to get that information and see what is actually conflicting. Jan 16, 2020 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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