不同索引引发SQL Server死锁问题及规避方案咨询

问题场景 #

以下测试场景中，SQL Server会触发死锁，但Oracle不会：

• 进程1通过精确匹配WHERE子句查询主键记录，使用某一索引；
• 进程2以FIFO方式通过行级锁、另一索引查询同一条记录，按预期阻塞；
• 进程1通过主键删除该记录。

SQL Server环境与复现 #

版本信息 #

SQL Server 2019（Windows 10环境），已启用READ_COMMITTED_SNAPSHOT和ALLOW_SNAPSHOT_ISOLATION选项。

环境搭建 #

create table deadlocktest (
  pk int, 
  id1 int, 
  id2 int, 
  x int, 
  y int, 
  seq int, 
  primary key nonclustered  (pk) );

create UNIQUE NONCLUSTERED index  idx_id1 on deadlocktest(id1, x, seq);
create UNIQUE NONCLUSTERED index  idx_id2 on deadlocktest(id2);
create UNIQUE NONCLUSTERED index  idx_seq on deadlocktest(seq);
  
insert into deadlocktest values (1, 10, 100, 1000, 10000, 1);
insert into deadlocktest values (2, 20, 200, 2000, 20000, 2);

测试用例 #

关闭自动提交，事务隔离级别为READCOMMITTED：

T1: BEGIN TRANSACTION;
T2: BEGIN TRANSACTION;
T1: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id2 = 100 and x = 1000 and seq = 1;
T2: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq;
    -- T2阻塞
T1: delete from deadlocktest where pk = 1;
T2: 触发死锁错误

注：即使T1中的查询不使用UPDLOCK ROWLOCK也会出现相同问题，该锁仅用于控制执行顺序。

死锁说明 #

死锁图显示：左侧（死锁牺牲品）为T2的查询语句，右侧为T1的删除语句。

查询计划 #

T2查询计划（使用idx_id1索引）：

select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq
  |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id1]), SEEK:([XGEN].[dbo].[deadlocktest].[id1]=(10) AND [XGEN].[dbo].[deadlocktest].[x]=(1000)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([XGEN].[dbo].[deadlocktest]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

T1查询计划（使用idx_seq索引）：

select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000 and seq = 1
  |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[idx_seq]), SEEK:([XGEN].[dbo].[deadlocktest].[seq]=(1)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([XGEN].[dbo].[deadlocktest]), SEEK:([Bmk1000]=[Bmk1000]),  WHERE:([XGEN].[dbo].[deadlocktest].[id1]=(10) AND [XGEN].[dbo].[deadlocktest].[x]=(1000)) LOOKUP ORDERED FORWARD)

删除语句计划（使用主键索引）：

delete from deadlocktest where pk = 1
  |--Table Delete(OBJECT:([XGEN].[dbo].[deadlocktest]), OBJECT:([XGEN].[dbo].[deadlocktest].[PK__deadlock__321403CE902EA0FE]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id1]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id2]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_seq]))
       |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[PK__deadlock__321403CE902EA0FE]), SEEK:([XGEN].[dbo].[deadlocktest].[pk]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

Oracle环境与复现 #

版本信息 #

Oracle XE 18（Windows 10环境）

环境搭建 #

create table deadlocktest (
  pk int, 
  id1 int, 
  id2 int, 
  x int, 
  y int, 
  seq int, 
  primary key (pk) );
  
insert into deadlocktest values (1, 10, 100, 1000, 10000, 1);
insert into deadlocktest values (2, 20, 200, 2000, 20000, 2);

create UNIQUE index  idx_id1 on deadlocktest(id1, x, seq);
create UNIQUE index  idx_id2 on deadlocktest(id2);
create UNIQUE index  idx_seq on deadlocktest(seq);

测试用例 #

关闭自动提交，事务隔离级别为READCOMMITTED：

T1: SET TRANSACTION READ WRITE;
T2: SET TRANSACTION READ WRITE;
T1: select pk, x  from deadlocktest where id2 = 100 and x = 1000 and seq = 1 for update;
T2: select pk, x from deadlocktest where id1 = 10 and x = 1000  order by seq for update;
    -- T2阻塞
T1: delete from deadlocktest where pk = 1;

无死锁发生，T1完成删除后，T2保持阻塞直到T1事务结束。

查询计划 #

T2查询计划：

select pk, x from deadlocktest where id1 = 10 and x = 1000  order by seq for update;
SELECT STATEMENT
  FOR UPDATE
    BUFFER (SORT)
      XGEN.DEADLOCKTEST  TABLE ACCESS (BY INDEX ROWID)
        XGEN.IDX_ID1  INDEX (RANGE SCAN)

T1查询计划：

select pk, x  from deadlocktest where id2 = 100 and x = 1000 and seq = 1 for update;
SELECT STATEMENT
  FOR UPDATE
    XGEN.DEADLOCKTEST  TABLE ACCESS (BY INDEX ROWID)
      XGEN.IDX_ID2  INDEX (UNIQUE SCAN)

SQL Server死锁规避方案 #

1. 统一锁获取顺序 #

强制并发事务通过相同的索引路径访问目标行，避免因不同索引导致的锁顺序不一致。例如，让两个查询都强制使用主键索引：

-- T1查询强制走主键索引
select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK, INDEX(PK__deadlock__321403CE902EA0FE)) where id2 = 100 and x = 1000 and seq = 1;
-- T2查询强制走主键索引
select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK, INDEX(PK__deadlock__321403CE902EA0FE)) where id1 = 10 and x = 1000  order by seq;

2. 将主键改为聚集索引 #

当前SQL Server表使用非聚集主键，表为堆表，RID查找会额外引入锁资源。将主键改为聚集索引后，非聚集索引会包含聚集键，避免RID lookup，锁的获取逻辑更简洁：

-- 删除原非聚集主键，重建为聚集主键
alter table deadlocktest drop constraint PK__deadlock__321403CE902EA0FE;
alter table deadlocktest add constraint PK__deadlock__321403CE902EA0FE primary key clustered (pk);

3. 调整事务逻辑，提前获取一致的锁 #

在删除操作前，通过与查询相同的索引路径获取锁，确保锁顺序一致。例如T1在删除前，先通过T2使用的索引路径访问行：

T1: BEGIN TRANSACTION;
T1: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq;
T2: BEGIN TRANSACTION;
T2: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq;
    -- T2阻塞
T1: delete from deadlocktest where pk = 1;

4. 强制使用快照隔离级别 #

虽然已启用快照隔离选项，但可以显式指定事务使用SNAPSHOT隔离级别，避免共享锁与排他锁的冲突（需注意版本存储的磁盘开销）：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
-- 后续查询与删除操作

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