SQL Server并发访问同表死锁原因排查与解决方案咨询

从你提供的死锁报告可以直接定位循环等待链：

• 进程spid66持有SectionTable第24768207页的SIX（共享意向排他）锁，申请第23196243页的S（共享）锁
• 进程spid69持有SectionTable第23196243页的SIX锁，申请第24768207页的S锁
  两边都不释放自己持有的锁，等待对方释放资源，最终触发死锁。

出现这种交叉持锁的核心原因是CreateSectionTable里的全表Max查询逻辑：

1. 两个并发事务都要执行SELECT @nTableIndex = Max(DataTableIndex) + 1 FROM SectionTable，这个查询没有对应索引支撑时会全表扫描所有数据页，扫描过程中逐页加S锁；因为事务后续马上要执行UPDATE写操作，持有的锁会升级为SIX锁，且锁会一直持有到事务结束才释放。
2. 两个并发事务扫描数据页的顺序不是固定一致的，就会出现A先拿到页X的锁、B先拿到页Y的锁，之后两边继续扫描都要申请对方已经持有的页的锁，形成死循环。
3. 额外说明：你现在先查Max再Update的写法本身就有竞态bug，就算没触发死锁，两个并发请求也可能拿到完全相同的@nTableIndex值，导致DataTableIndex字段重复。

给CopyForm存储过程加sp_getapplock互斥锁确实可以解决死锁：相当于强制所有表单复制请求串行执行，同一时间只允许一个复制操作运行，自然不会出现交叉持锁的情况。但这个方案的缺点是并发性能极差，复制操作如果耗时较长，后续所有复制请求都会阻塞排队，用户侧会感知到明显的操作卡顿，除非你的业务并发量极低，否则不推荐优先用这个方案。

不需要把整个操作串行化，从锁冲突的根源入手调整即可，按改造成本从低到高列：

• 方案1：原子更新+锁提示，最小改动解决问题
  给DataTableIndex字段建非聚集降序索引，让Max查询不需要全表扫描，直接走索引快速拿到最大值，大幅减少持锁范围。同时把原来先查Max再更新的两步逻辑合并成单条原子语句，加锁提示保证锁申请顺序一致：

  -- 替换CreateSectionTable中原有查Max+更新的代码
  UPDATE SectionTable
  SET DataTableIndex = t.NextIndex,
      OrderNum = @nSectionNumber
  FROM SectionTable s
  CROSS JOIN (
      SELECT ISNULL(MAX(DataTableIndex), 0) + 1 AS NextIndex
      FROM SectionTable WITH(UPDLOCK, HOLDLOCK)
  ) t
  WHERE s.ObjectID = @argObjectID
  

  这里的UPDLOCK会让查询Max的时候直接申请更新锁，HOLDLOCK保证锁持有到事务结束，所有并发请求都会在同一个索引资源上排队拿锁，不会出现交叉持不同页锁的情况，也不会出现重复值问题，持锁时间极短，对并发影响很小。
• 方案2：用SEQUENCE替代Max自增逻辑，性能最优
  如果DataTableIndex只需要保证全局唯一递增，不需要保证值连续，完全可以单独创建SEQUENCE对象来生成自增值，不需要每次都扫表算Max：

  -- 一次性创建序列，初始值设为当前表中最大的DataTableIndex+1即可
  CREATE SEQUENCE Seq_SectionTable_DataTableIndex
  AS INT
  START WITH 1 -- 替换成实际的初始值
  INCREMENT BY 1;
  
  -- 存储过程中直接取序列值，不需要查表
  SELECT @nTableIndex = NEXT VALUE FOR Seq_SectionTable_DataTableIndex;
  UPDATE SectionTable SET DataTableIndex = @nTableIndex, OrderNum = @nSectionNumber WHERE ObjectID = @argObjectID
  

  序列取值是数据库引擎层面的轻量原子操作，几乎不会产生锁等待，从根源上消除了Max全表扫描带来的锁冲突，是性能最好的方案。
• 方案3：开启读提交快照隔离（RCSI）
  在数据库层面开启READ COMMITTED SNAPSHOT隔离级别，读操作默认不加S锁，基于行版本读取数据，不会和写操作持有的X/SIX锁冲突，也能避免这次的死锁。这个方案不需要改业务代码，但需要评估tempdb的额外开销，适合整体读多写少的业务场景。

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