SQL SERVER下非聚集索引引发的死锁问题

最近一个消息发送功能在测试的过程中遇到了因为非聚集索引引发的死锁问题，下面是小伙伴事后总结的内容，现分享出来。
（因为一些内容涉及到公司信息，所以只分享了其中部分内容，但是应该不影响整体阅读）

情景介绍

表名是Receiver，字段有id(主键，聚集索引)，msgId(非聚集索引)，receiver(非聚集索引)，createTime，updateTime

并发执行的语句为

1、insert into Receiver select msgId,receiver,createTime,updateTime from tempTable2、update Receiver set updateTime=#{} where msgId=#{} and receiver in (#{})

发生场景

1、多线程(100个)执行Receiver的插入语句
2、多线程(8线程)执行Receiver的更新语句

锁分析

1、insert语句执行时，优先申请插入记录所在的行(或页、表)的X锁（排他锁），记录插入完成后，申请非聚集索引的X锁，完成索引的更新，全部完成后，释放持有的X锁
2、update语句执行时，申请msgId所在非聚集索引的U锁，通过msgId非聚集索引获取需要更新的记录所在位置，然后申请对应记录的聚集索引的U锁（更新锁），因为不涉及非聚集索引的更新，所以数据更新完成后，所有U锁被释放

死锁分析

首先假设insert优先执行，insert顺利申请到带插入数据的行的X锁，因为一次插入多条记录，所以实际申请的是聚集索引页的pagelock。记录插入完成后，insert进程会去申请msgId、updateTime非聚集索引的X锁。假设此时insert进程被暂停执行，执行权交给update进程。update优先申请msgId非聚集索引的U锁以查询需要更新的记录集合，查找到对应的记录之后，会申请这些记录的U锁(同样是pagelock)。但是因为此数据页已经被insert进程加上了X锁，所以无法在加上U锁，因此update进程进入等待状态。同时，insert进程也需要申请索引的X锁，但是却已经被update进程持有了U锁。最后，两个进程进入了相互等待状态，死锁发生。

发生条件

1、高并发情况下的update与insert
2、update的where条件中用到了表的非聚集索引

解决办法

修改update语句
原始语句为

update Receiver set updateTime=#{} where msgId=#{} and receiver in (#{})

1、增加行级锁

update Receiver with(rowlock) set updateTime=#{} where msgId=#{} and receiver in (#{})

改为行级锁后，可以显著减少insert请求的锁与update请求的锁发生重合的情况
劣势：更新多条记录时会持有多个行级锁，而之前可能只要持有一个页锁
2、where语句中的条件加上with(nolock)

update Receiver set updateTime=#{} where id in(select r.id from Receiver r with(nolock) where r.msgId=#{} and r.receiver in (#{}))