SQL Server死锁图疑问：行X锁、页IX锁获取规则及优化方案

关于锁申请逻辑的疑问解答 #

首先明确数据库层级锁的申请规则：所有行级锁的申请都必须遵循从上到下的层级顺序，即先申请表级意向锁，再申请页级意向锁，最后申请行级锁，这个规则是固定的，不会出现跳过页级锁直接拿行锁的情况。

你提到的「页上已有S锁还能拿到行X锁」的现象不可能出现在不同事务的场景下，从锁兼容矩阵就能验证：页级S锁和页级IX锁是完全互斥的，只要有其他事务持有某页的S锁，其余事务都无法拿到该页的IX锁，自然也不可能拿到该页下的行X锁。

你观察到的现象大概率是以下两种误判导致的：

• 你将页级IS（意向共享）锁错看成了S（共享）锁：IS锁和IX锁是兼容的，这种场景下右侧UPDATE可以正常拿到页IX锁和行X锁
• 页级S锁和行X锁属于同一个事务：同一事务持有的锁不会触发兼容校验，事务可以在持有页S锁的情况下，申请同页的IX锁升级为SIX（共享意向排他）锁，再获取下属行的X锁

除事务重试外的死锁解决方案 #

• 拆分大事务：将左侧的复杂长事务拆分为多个逻辑独立的短事务，尽可能缩短锁的持有时间，从根源降低锁冲突概率
• 调整事务隔离级别：将默认的可重复读（RR）隔离级别下调为读已提交（RC），RC级别下普通查询使用一致性快照读不会加行级S锁，同时会消除间隙锁，大幅减少锁冲突的可能
• 优化索引：确保UPDATE语句的过滤条件匹配到精准的二级/聚集索引，避免全表扫描、全页扫描导致的大范围不必要加锁
• 统一访问顺序：所有事务按照固定的顺序（比如按主键ID升序）访问数据行和索引页，避免交叉加锁导致的循环等待
• 改写查询逻辑：如果事务内需要先查询再修改对应行，直接使用SELECT ... FOR UPDATE语句一次性拿到行级X锁，不要先拿S锁再升级为X锁，减少锁升级阶段的冲突

内容的提问来源于stack exchange，提问作者ElMent