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<>@P3 and status<>@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?