SQL Server单事务为何出现死锁异常？

在SQL Server 2014中有一张表Table_1，包含name和id两列（id为标识列）。同时在两个独立连接中运行以下循环插入脚本：

while 1 = 1
begin
begin try
    SET TRANSACTION ISOLATION LEVEL Serializable; 
        begin tran

        declare @maxId int = 0
        select @maxId = MAX(Id) + 1 from [dbo].[Table_1] 
        print(@maxId)

        set identity_insert [dbo].[Table_1] on
            INSERT INTO [dbo].[Table_1] (Id,[Name])
            VALUES (@maxId,'11')
        set identity_insert [dbo].[Table_1] off

commit tran
end try
begin catch

IF(@@TRANCOUNT > 0)
rollback tran

    print ( ERROR_MESSAGE())
    print ( ERROR_SEVERITY())
    print ( ERROR_STATE())
 
end catch 

end

运行后出现死锁错误：

Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

实际场景为模拟高并发请求，疑惑点：单事务中包含select和insert操作的两个连接为何未互相等待，反而触发死锁？按死锁的定义，需要两个事务分别锁定不同资源并互相依赖对方资源，当前场景为何符合死锁条件？

死锁原因 #

核心是**Serializable隔离级别下的键范围锁（Key Range Lock）**导致循环等待：

1. 当设置SET TRANSACTION ISOLATION LEVEL Serializable时，执行SELECT MAX(Id)+1查询时，SQL Server会对Id列最大键值之后的范围添加共享键范围锁（S-Range），目的是防止幻读（保证事务期间不会有新的更大Id行插入，维持MAX结果的有效性）。
2. 两个连接同时执行该查询时，共享锁之间不互斥，因此两者都能成功获取该范围的共享键范围锁。
3. 进入INSERT步骤时，事务需要将共享键范围锁升级为排他键范围锁（X-Range），但排他锁与共享锁互斥：
   • 连接A需等待连接B释放共享锁才能升级
   • 连接B同时也需等待连接A释放共享锁才能升级
4. 这种循环等待状态满足死锁的四个必要条件（互斥、持有并等待、不可剥夺、循环等待），因此触发死锁。

解决方案 #

1. 优先使用标识列自动生成Id：
   去掉手动计算MAX(Id)的逻辑，直接依赖SQL Server的IDENTITY属性自动生成Id，这是最安全高效的并发方案，完全规避手动生成Id带来的锁冲突。
2. 使用更新锁提示替代Serializable隔离级别：
   如果必须手动生成Id，在查询MAX(Id)时添加UPDLOCK, HOLDLOCK提示，强制获取更新锁而非共享锁：

   select @maxId = MAX(Id) + 1 from [dbo].[Table_1] WITH (UPDLOCK, HOLDLOCK)
   

   更新锁与共享锁互斥，同一时间只有一个事务能获取该锁，另一个事务会等待，从根源避免循环等待的死锁场景。
3. 使用序列（SEQUENCE）生成Id：
   创建序列对象来生成自增Id，比手动计算MAX(Id)更可靠，并发性能更好：

   CREATE SEQUENCE dbo.IdSequence START WITH 1 INCREMENT BY 1;
   -- 获取下一个Id
   declare @maxId int = NEXT VALUE FOR dbo.IdSequence;
   

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