Search code examples
sqlsql-servermultithreadinglockingdeadlock

SQL Server - Deadlock on key update

SQL Server 2014 Express.

I've simlified my problem to the following:

CREATE TABLE [dbo].[foo](
    [fooid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
    [fooval] [nvarchar](4),
    CONSTRAINT [foo_PK] PRIMARY KEY CLUSTERED 
    (
        [fooid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[foo] ([fooval]) VALUES (1) 
GO

INSERT INTO [dbo].[foo] ([fooval]) VALUES (2)
GO

CREATE TABLE [dbo].[bar](
    [barid] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
    [barval] [nvarchar](4),
    CONSTRAINT [bar_PK] PRIMARY KEY CLUSTERED 
    (
        [barid] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[bar] ([barval]) VALUES (1)
GO

INSERT INTO [dbo].[bar] ([barval]) VALUES (2)
GO

So I have two simple tables with a clustered primary key on fooid and barid.

I run the following two queries in two debuggers.

First query:

BEGIN TRAN
UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1

UPDATE dbo.bar SET barval = 1 WHERE barval = 1
COMMIT

Second query:

BEGIN TRAN
UPDATE dbo.bar SET barval = 2 WHERE barid = 2

UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2
COMMIT

While debugging, I execute first update of query 1, then first update of query 2, then second update of query 1 and finally second update of query 2.

This results in a deadlock. I am running snapshot isolation level Read Committed.

The graph shows:

<deadlock-list>
 <deadlock victim="process2f3ed64e8">
  <process-list>
   <process id="process2f3ed64e8" taskpriority="0" logused="288" waitresource="KEY: 5:72057607973896192 (227b7397de24)" waittime="2067" ownerId="1978563" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:57.280" XDES="0x2e2ff23b0" lockMode="U" schedulerid="1" kpid="9892" status="suspended" spid="59" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:56.997" lastbatchcompleted="2015-08-24T16:24:56.993" lastattention="1900-01-01T00:00:00.993" clientapp="Microsoft SQL Server Management Studio - Abfrage" hostname="VSL53439" hostpid="9124" loginname="x" isolationlevel="read committed (2)" xactid="1978563" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000118b7210fc35334336b07155dea42e1470abe8dd0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x02000000bf0a381fd6fec29b6ed330f87409b4e8c47d26f10000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
BEGIN TRAN
UPDATE dbo.bar SET barval = 2 WHERE barid = 2

UPDATE dbo.foo SET fooval = 2 WHERE fooval = 2
COMMIT    </inputbuf>
   </process>
   <process id="process2e01b5088" taskpriority="0" logused="432" waitresource="KEY: 5:72057607973830656 (c939eba47c7b)" waittime="2970" ownerId="1978502" transactionname="user_transaction" lasttranstarted="2015-08-24T16:24:54.100" XDES="0x2df783000" lockMode="U" schedulerid="5" kpid="1928" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-08-24T16:24:53.730" lastbatchcompleted="2015-08-24T16:24:53.730" lastattention="1900-01-01T00:00:00.730" clientapp="Microsoft SQL Server Management Studio - Abfrage" hostname="VSL53439" hostpid="4348" loginname="x" isolationlevel="read committed (2)" xactid="1978502" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="6" stmtstart="38" stmtend="146" sqlhandle="0x02000000f8c0c134764c79fe77f7cda514cc62eaf1a50cc80000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="adhoc" line="6" stmtstart="336" stmtend="426" sqlhandle="0x020000005c75f728d068a9d6386669fb7b8e315b3e484d640000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
BEGIN TRAN
UPDATE dbo.foo SET fooval = 1 WHERE fooid = 1

UPDATE dbo.bar SET barval = 1 WHERE barval = 1
COMMIT    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057607973896192" dbid="5" objectname="dbdevelop.dbo.foo" indexname="foo_PK" id="lock2ea279880" mode="X" associatedObjectId="72057607973896192">
    <owner-list>
     <owner id="process2e01b5088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2f3ed64e8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057607973830656" dbid="5" objectname="dbdevelop.dbo.bar" indexname="bar_PK" id="lock2eb0e6500" mode="X" associatedObjectId="72057607973830656">
    <owner-list>
     <owner id="process2f3ed64e8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2e01b5088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

When I take a look into the lock acquiring I see that the following locks have been done

  1. acquired - IX - OBJECT
  2. acquired - IX - PAGE
  3. acquired - X - KEY
  4. acquired - X - EXTENT
  5. released - X - EXTENT
  6. acquired - U - EXTENT
  7. acquired - X - PAGE
  8. released - U - EXTENT
  9. released - X - PAGE
  10. released - 0 - KEY
  11. released - 0 - PAGE

So, everything gets released, except for the OBJECT from the beginning, which seems to be the primary key index. I guess it will be kept until the commit of the transaction is done and not released immediatly. And that seems to result in a deadlock.

Can you please answer me the following questions:

  1. Am I correct that the clustered primary key index lock will be kept until the commit?
  2. Am I correct that this will block all other concurrent update trys to wait?
  3. If so, why does the whole index get locked when updating with a given primary key in the where clause? This would mean that every update on a primary key where clause will lock the whole table for the transaction. I cant believe this.
  4. Is the best solution to add an index on fooval and barval?
  5. Will an sql server differ in its behaviour from an sql server express?
Solution

  • Crossing updates is a recipe for deadlocks. Regardless of indexes, types of indexes etc etc.. Always try to update tables in the same order. Having said that regardless of the index, if the data is on the same page then you have a lock scenario and because you are updating in a crisscross manner, one of your commands will be selected as a deadlock.

    1.Yes
    2.Yes
    3.This is complicated to answer, and there are wonderful explanations on the internet, but what you should understand is that regardless of indexes, locks will happen, and happen often but deadlocks are due to poor strategy.
    4.Irrelevant
    5.Yes in certain things but not for this situation.