I have two SQL queries (MSSQL server):
SELECT [Value]
FROM [dbo].[BigTable]
ORDER BY [Id] DESC
and
UPDATE [dbo].[BigTable]
SET [Value] = [Value]
Where [Id] - Primary clustered key.
When I run them infinitely in the loop I get deadlock, which is obvious. But what is not obvious (for me): why on deadlock graph I get "Owner mode: U" for select statement.
As far as I know select statement can only have shared locks. And here I'm not using any hints or additional transactions to make update lock. Any idea why I see it here?
XML for deadlock is attached
<deadlock-list>
<deadlock victim="process1c094ee5468">
<process-list>
<process id="process1c094ee5468" taskpriority="0" logused="0" waitresource="PAGE: 7:1:1502 " waittime="1289" ownerId="901143" transactionname="SELECT" lasttranstarted="2021-05-05T18:04:54.470" XDES="0x1c094329be8" lockMode="S" schedulerid="6" kpid="22644" status="suspended" spid="62" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-05-05T18:04:54.470" lastbatchcompleted="2021-05-05T18:04:54.453" lastattention="1900-01-01T00:00:00.453" clientapp="Core Microsoft SqlClient Data Provider" hostname="ALEXEY-KLIPILIN" hostpid="3132" loginname="sa" isolationlevel="read committed (2)" xactid="901143" currentdb="7" currentdbname="SampleDb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="92" sqlhandle="0x02000000bf49f5138395d042205ae64888add734815151770000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
SELECT * FROM [dbo].[BigTable] ORDER BY Id DESC </inputbuf>
</process>
<process id="process1c096e1d088" taskpriority="0" logused="100" waitresource="PAGE: 7:1:1503 " waittime="1289" ownerId="901139" transactionname="UPDATE" lasttranstarted="2021-05-05T18:04:54.470" XDES="0x1c08bc84428" lockMode="X" schedulerid="4" kpid="9160" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-05-05T18:04:54.470" lastbatchcompleted="2021-05-05T18:04:54.397" lastattention="1900-01-01T00:00:00.397" clientapp="Core Microsoft SqlClient Data Provider" hostname="ALEXEY-KLIPILIN" hostpid="3132" loginname="sa" isolationlevel="read committed (2)" xactid="901139" currentdb="7" currentdbname="SampleDb" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="88" sqlhandle="0x0200000018eeb102d311fd032bb670822f260841060b64410000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
UPDATE [dbo].[BigTable] SET [Value] = [Value] </inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="1" pageid="1502" dbid="7" subresource="FULL" objectname="SampleDb.dbo.BigTable" id="lock1c0884bdd00" mode="X" associatedObjectId="72057594043760640">
<owner-list>
<owner id="process1c096e1d088" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process1c094ee5468" mode="S" requestType="wait"/>
</waiter-list>
</pagelock>
<pagelock fileid="1" pageid="1503" dbid="7" subresource="FULL" objectname="SampleDb.dbo.BigTable" id="lock1c0a0a23380" mode="U" associatedObjectId="72057594043760640">
<owner-list>
<owner id="process1c094ee5468" mode="S"/>
</owner-list>
<waiter-list>
<waiter id="process1c096e1d088" mode="X" requestType="convert"/>
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>