使用nvarchar(max)时SQL Server事务死锁问题排查问询

问题背景 #

环境为SQL Server 2022（v16.00.1135），存储过程通过OPENJSON解析含nvarchar(max)字段的JSON参数，将数据导入临时表#reports和#details后处理。正常执行符合预期，但当@parameter数组包含41条报表数据时，触发错误1205：事务因锁/通信缓冲区资源与其他进程死锁，被选为死锁牺牲品。

已验证的规避规律：

• 仅大参数量触发问题
• 将代码标记(*)和(**)的text字段改为nvarchar(200)可解决
• 改用表变量@td替代临时表#details也可规避
• 设置MAXDOP=1有效

根因分析 #

1. 大参数触发并行执行计划，加剧锁竞争 #

当JSON参数规模增大时，OPENJSON解析及临时表写入的IO、内存开销显著上升，SQL Server查询优化器会默认选择并行执行计划（除非显式设置MAXDOP=1）。并行执行时，多个工作线程会同时对临时表执行写入、读取操作，不同线程会争夺临时表的页锁、行锁等资源。当线程间出现循环等待锁资源的情况（比如线程A持有锁X等待锁Y，线程B持有锁Y等待锁X），就会触发死锁。小参数时，优化器倾向于生成串行计划，锁竞争概率极低，因此不会触发死锁。

2. LOB字段（nvarchar(max)）增加锁竞争维度 #

标记(*)和(**)的text字段为nvarchar(max)，属于大值类型（LOB）。SQL Server对LOB数据的处理逻辑与普通字符串不同：写入LOB数据时，会先将数据写入独立的LOB存储区，同时在临时表的主数据页中存储指向LOB数据的指针。并行处理LOB字段时，线程不仅要竞争临时表主数据页的锁资源，还要竞争LOB存储区的资源锁，这大幅增加了死锁发生的概率。将字段改为nvarchar(200)后，数据直接存储在主数据页，锁竞争的维度减少，从而避免死锁。

3. 临时表与表变量的锁机制差异 #

临时表（#开头）是存储在tempdb中的物理表，会生成实际的锁资源，且tempdb是所有会话共享的数据库，即使是会话私有临时表，其底层存储的锁资源仍可能被并行线程竞争。而表变量是作用域仅限于当前批处理的逻辑对象，小数据量时直接存储在内存中，SQL Server对表变量的锁机制极轻量（甚至不会生成可检测的锁资源），不存在跨线程的锁竞争，因此能规避死锁。

4. MAXDOP=1禁用并行执行，消除多线程锁竞争 #

设置MAXDOP=1强制查询使用单线程串行执行，所有解析、写入操作由单个线程完成，不存在多个线程同时请求锁资源的情况，自然不会产生死锁。这也反向验证了死锁的核心诱因是并行执行带来的锁竞争。

额外补充：事务延长锁持有时间 #

存储过程在事务内创建并操作临时表，事务的存在会延长锁资源的持有时间，进一步扩大了并行线程间锁竞争的时间窗口，大参数下这种竞争更容易演变为死锁。

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