SQL Server如何锁定行？竖表结构频繁死锁问题咨询

1. SQL Server记录锁定核心规则 #

SQL Server的锁机制遵循层级化、按需分配的逻辑，核心规则如下：

• 锁粒度层级：从高到低分为数据库锁、表锁、页锁、键锁（即行级锁，针对索引条目），默认由数据库自动选择最优粒度，当单事务锁定行数量超过阈值（默认约5000行）时，会自动将分散的行级锁升级为表锁，降低锁管理开销。
• 锁类型：读操作默认持有共享锁（S锁），同一条记录可被多个S锁同时持有；写/插入/删除操作持有排他锁（X锁），与任意类型锁都不兼容；更新操作会先持有更新锁（U锁，读转写的过渡锁），避免多个读操作同时转写引发冲突。
• 锁持有周期：默认读提交隔离级别下，读操作的S锁会在读取完对应行后立即释放；所有写操作的锁会持有到事务提交或回滚。

2. 未主动查询的记录被锁定的触发场景 #

答案是会，即使没有主动查询某条记录，也可能被锁定，常见触发原因有两类：

• 锁升级：单事务持有的行锁数量达到升级阈值后，行锁会升级为表锁，此时整张表的所有记录都会被锁定，无论是否被该事务主动访问。
• 全表/全页扫描：如果查询没有走精准索引匹配，SQL Server会遍历全表或整个数据页筛选符合条件的行，遍历过程中会给所有经过的行加锁，不符合条件的行的锁会在筛选后释放，但如果两个事务的扫描顺序相反，就可能在释放锁之前出现互相持有对方所需锁的情况。

3. 垂直存储场景的死锁诱因分析 #

插入操作死锁的核心原因 #

认为未持久化的插入记录不会被锁是认知误区：插入操作会为新写入的索引条目持有排他（X）键锁直到事务结束，且如果主键为聚集索引（SQL Server默认联合主键会创建聚集索引），插入时需要在聚集索引的对应排序位置插入新条目，很容易触发键范围锁冲突。
结合当前表结构ID version fieldindex fieldvalue，联合主键为(ID, version, fieldindex)，死锁高频的具体诱因如下：

• 单逻辑记录对应60条物理行，无论是加载还是写入逻辑记录，单事务都需要一次性操作60行，持有的锁数量远高于普通行存储结构，非常容易触发锁升级为表锁，直接导致全表操作冲突。
• 联合主键的排序规则为ID优先、其次为version、最后为fieldindex，如果多个事务同时操作不同ID、不同version的记录，但因为锁升级或扫描时遍历了对方操作的行，就会出现锁冲突。
• 如果多个事务同时插入同一ID+version下的不同fieldindex记录，或者跨ID的插入操作遍历顺序相反，会出现互相持有对方所需锁的死锁环路。

4. 对应优化方案 #

• 调整索引结构：将联合主键对应的聚集索引改为非聚集索引，新增自增列作为聚集索引，避免插入时的索引页分裂和范围锁冲突。
• 拆分事务粒度：将单逻辑记录的60行写入操作拆分为更小的事务，减少单事务持有的锁数量，避免触发锁升级。
• 禁用锁升级：如果业务无法拆分事务，可以通过开启跟踪标志1211/1224强制禁止行锁升级为表锁，以更高的锁管理开销换取并发性能。
• 优化查询逻辑：确保所有查询都走精准的(ID, version)索引匹配，避免全表/全页扫描，减少不必要的行锁定。

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