sql server死锁：identify导致的 insert 和 select max(id)

【1】死锁信息
- 【1.1】图
- 【1.2】XML
【2】分析思路
- 【2.1】语句分析
- 【2.2】思路分析
【3】解决思路

【1】死锁信息

【1.1】图

左边：select max(id) from tab
右边：insert into tab values.....

两个语句是同一个表，id是主键、聚集索引

【1.2】XML

Deadlock graph    <deadlock-list>
 <deadlock victim="process9ada68">
  <process-list>
   <process id="process9ada68" taskpriority="0" logused="0" waitresource="PAGE: 102:1:5940067" waittime="3953" ownerId="1571798125" transactionname="user_transaction" lasttranstarted="2020-11-16T12:04:38.950" XDES="0x55282258" lockMode="S" schedulerid="2" kpid="2896" status="suspended" spid="80" sbid="1" ecid="0" priority="0" transcount="1" lastbatchstarted="2020-11-16T12:04:38.950" lastbatchcompleted="2020-11-16T12:04:38.950" clientapp="Microsoft SQL Server" hostname="iZqcugpmvybrkfZ" hostpid="1596" loginname="sa" isolationlevel="read committed (2)" xactid="1571798125" currentdb="102" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x020000002b707300c7eefdadd6f8dfe7f8457b5eb4e02c32">
SELECT MAX(&quot;Tbl1002&quot;.&quot;ID&quot;) &quot;Expr1003&quot; FROM &quot;QPRecordDB&quot;.&quot;dbo&quot;.&quot;RecordUserInout&quot; &quot;Tbl1002&quot;     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 32767 Object Id = 315066412]    </inputbuf>
   </process>
   <process id="processfe03e8" taskpriority="0" logused="620" waitresource="PAGE: 102:1:5940068" waittime="3953" ownerId="1571798118" transactionname="user_transaction" lasttranstarted="2020-11-16T12:04:38.950" XDES="0x37f294a8" lockMode="IX" schedulerid="3" kpid="4960" status="suspended" spid="83" sbid="1" ecid="0" priority="0" transcount="2" lastbatchstarted="2020-11-16T12:04:38.950" lastbatchcompleted="2020-11-16T12:04:38.950" clientapp="Microsoft SQL Server" hostname="iZqcugpmvybrkfZ" hostpid="1596" loginname="sa" isolationlevel="read committed (2)" xactid="1571798118" currentdb="102" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="316" sqlhandle="0x020000002109bb37cd7e5870d56e6418e1fe6003a303a1c9">
INSERT [QPRecordDB].[dbo].[RecordUserInout]([UserID],[KindID],[ServerID],[EnterScore],[EnterUserMedal],[EnterLoveliness],[EnterMachine],[EnterClientIP]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011)     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@Param000004 int,@Param000005 int,@Param000006 int,@Param000007 bigint,@Param000008 int,@Param000009 int,@Param000010 nvarchar(33),@Param000011 nvarchar(15))INSERT [QPRecordDB].[dbo].[RecordUserInout]([UserID],[KindID],[ServerID],[EnterScore],[EnterUserMedal],[EnterLoveliness],[EnterMachine],[EnterClientIP]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="5940067" dbid="102" objectname="QPRecordDB.dbo.RecordUserInout" id="lockffffffff80008e00" mode="IX" associatedObjectId="72057594056540160">
    <owner-list>
     <owner id="processfe03e8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9ada68" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="5940068" dbid="102" objectname="QPRecordDB.dbo.RecordUserInout" id="lockffffffffb86aa940" mode="S" associatedObjectId="72057594056540160">
    <owner-list>
     <owner id="process9ada68" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processfe03e8" mode="IX" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>

回到顶部

【2】分析思路

【2.1】语句分析

select max(id) from RecordUserInout

（1）当我用max(id)查询的时候，它会顺着索引去找最大的，比如找到了最大记录在这个页，它会这个页上加上S锁

（2）接着insert into values插入的时候，它需要同时在这两个页上写数据，于是向这个页上加IX锁

（3）但是第一个页已经加了S锁，于是第一个页的IX就会等待，但是第二个页就不需要

怎么想都感觉不是很对

难道是这样？

（1）select max(id) 要扫描整个聚集索引树才能找到max(id)，对聚集索引树页都加了S锁
（2）同时，在select max(id) 还没找到的时候，insert into 插入到了最后面，然后它排它X了，然后它需要X锁来更新聚集索引上层页
（3）但这个时候上层页正在被select 给S锁阻塞住了
（4）select max(id) 的后续扫描又被 insert 的X锁给阻塞了

感觉其实也不是很对，既然