SQL Server 2016 – How Can Deadlock Occur with Pagelock on Two Different Tables

deadlocksql serversql-server-2016

I'm using SQL Server 2016 and running into a situation where a deadlock, via pagelock, is happening on two different tables. As I understand the database never shares pages across tables, so how is it possible that selecting from table bar can block the update on table foo? Especially given foo has no FKs incoming or outgoing (it's a standalone, isolated table).

Here is the deadlock xml:

<deadlock>
<victim-list>
    <victimProcess id="process200e2c2cca8"/>
</victim-list>
<process-list>
    <process id="process200e2c2cca8" taskpriority="0" logused="528" waitresource="PAGE: 7:1:463762 " waittime="2813" ownerId="232195085" transactionname="implicit_transaction" lasttranstarted="2019-06-18T01:57:05.067" XDES="0x201379a6430" lockMode="IX" schedulerid="2" kpid="2780" status="suspended" spid="77" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-06-18T01:57:05.267" lastbatchcompleted="2019-06-18T01:57:05.267" lastattention="1900-01-01T00:00:00.267" clientapp="Microsoft JDBC Driver for SQL Server" hostname="EC2AMAZ-U81HN6O" hostpid="0" loginname="mydb" isolationlevel="read committed (2)" xactid="232195085" currentdb="7" currentdbname="mydb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
        <executionStack>
            <frame procname="adhoc" line="1" stmtstart="684" stmtend="1618" sqlhandle="0x0200000068ff5415175911deba600fac1e2197ddfe8b65890000000000000000000000000000000000000000">  unknown    </frame>
            <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  unknown    </frame>
        </executionStack>
        <inputbuf>  update foo set version=@P0, field1=@P1, ... fieldN=@PN where id=@P25 and version=@P26                                                                                                                                                                                                          </inputbuf>
    </process>
    <process id="process2013de9b848" taskpriority="0" logused="1252" waitresource="PAGE: 7:1:481017 " waittime="2786" ownerId="232194529" transactionname="implicit_transaction" lasttranstarted="2019-06-18T01:57:03.300" XDES="0x2012a5ea430" lockMode="S" schedulerid="1" kpid="2884" status="suspended" spid="93" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-06-18T01:57:05.280" lastbatchcompleted="2019-06-18T01:57:05.280" lastattention="1900-01-01T00:00:00.280" clientapp="Microsoft JDBC Driver for SQL Server" hostname="EC2AMAZ-U81HN6O" hostpid="0" loginname="mydb" isolationlevel="read committed (2)" xactid="232194529" currentdb="7" currentdbname="mydb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
        <executionStack>
            <frame procname="adhoc" line="1" stmtstart="132" stmtend="460" sqlhandle="0x02000000b1cb870e1f69a812df19c828461940c4f02bf9230000000000000000000000000000000000000000">  unknown    </frame>
            <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">  unknown    </frame>
        </executionStack>
        <inputbuf>  select count(*) as ct from bar where proj_id=@P0 and biz=@P1 and baz=@P2 and status&lt;&gt;@P3 and status&lt;&gt;@P4                                           </inputbuf>
    </process>
</process-list>
<resource-list>
    <pagelock fileid="1" pageid="463762" dbid="7" subresource="FULL" objectname="mydb.dbo.foo" id="lock2011f3b5b80" mode="SIX" associatedObjectId="72057594055753728">
        <owner-list>
            <owner id="process2013de9b848" mode="SIX"/>
        </owner-list>
        <waiter-list>
            <waiter id="process200e2c2cca8" mode="IX" requestType="wait"/>
        </waiter-list>
    </pagelock>
    <pagelock fileid="1" pageid="481017" dbid="7" subresource="FULL" objectname="mydb.dbo.bar" id="lock200bdfb6980" mode="IX" associatedObjectId="72057594042974208">
        <owner-list>
            <owner id="process200e2c2cca8" mode="IX"/>
        </owner-list>
        <waiter-list>
            <waiter id="process2013de9b848" mode="S" requestType="convert"/>
        </waiter-list>
    </pagelock>
</resource-list>
</deadlock>

How can two different tables, with different pages, create a pagelock deadlock?

Best Answer

This is a classic deadlock, but it can be hard to tell in SSMS or by just looking at the XML. Both tables are being accessed in both sessions.

Viewing it in SentryOne Plan Explorer is a bit easier on the eyes, and you can also hit the "play" button to watch how the timing of the deadlock played out. Here's a screenshot:

screenshot of deadlock graph in Plan Explorer

Here's what happens:

  1. Session 93 runs a statement that takes an SIX lock on a page in dbo.foo
    • this statement is not shown in the <inputbuf> element
  2. Session 77 runs a statement that tkaes an IX lock on a page in dbo.bar
    • this statement is not shown in the <inputbuf> element
  3. Session 77 runs a statement that attempts to take an IX lock on the page in dbo.foo that was locked in step #1
    • This is the update foo...where id=@P25 and version=@P26 statement that is included in the <inputbuf> element
  4. Session 93 runs a statement that *attempts to take an S lock on the page in dbo.bar that was locked in step #2
    • This is the select count(*) as ct from bar where... that is included in the <inputbuf> element

And thus the deadlock.

You will need to track down the code that is running these queries, and see what other statements are being run in each batch / transaction. The query text shown in the <inputbuf> element may be misleading because it can be truncated, and it doesn't necessarily show every statement that was executed during the transaction (you can see a great example of this exact thing on Erik Darling's blog).

I noticed that the connection is using "implicit_transaction" which is an unfortunate "feature" of the JDBC driver for SQL Server. You may wish to call setAutoCommit(true) if you don't intend for all of the statements for this connection to execute in a transaction. This would likely resolve your deadlock problem.

If you do need to do all of this work in a transaction, the classic advice to avoid this deadlock is to make sure to acquire and release locks in the same order. Without seeing your code it's hard to be more specific than that, but let me know if you have any questions.

Related Question