为何SQL Server中两事务更新不同行会阻塞？非主键条件引发问题

测试场景 #

创建测试表并插入数据：

CREATE TABLE TestTable(Id int NOT NULL PRIMARY KEY, Name nvarchar(5))

INSERT INTO TestTable(Id, Name) VALUES(1, 'T1')
INSERT INTO TestTable(Id, Name) VALUES(2, 'T2')

打开两个数据库连接，执行以下操作：

• 连接1执行：

BEGIN TRAN
UPDATE TestTable SET Name = 'U' WHERE Name = 'T1'

• 连接2执行：

BEGIN TRAN
UPDATE TestTable SET Name = 'U' WHERE Name = 'T2'

现象与问题 #

预期更新不同行的两个事务可并行执行，但实际连接2的事务被阻塞，需等待连接1的事务完成，且事务隔离级别对此无影响。若将UPDATE的WHERE条件改为主键Id，则两个事务可正常并行执行。

问题：为何SQL Server在使用非主键列作为查询条件时，无法并行更新不同行？该问题已导致应用死锁，如何解决？

原因分析 #

SQL Server执行UPDATE语句时，需先定位到符合WHERE条件的行：

• 当WHERE条件使用无索引的非主键列时，SQL Server会执行全表扫描来查找目标行。扫描过程中，会对扫描到的行依次添加意向排他锁（IX锁），即使这些行最终不符合条件，锁也不会立即释放。当第二个事务执行全表扫描时，会遇到第一个事务已锁定的行，从而被阻塞。
• 当WHERE条件使用主键时，由于主键是聚集索引，SQL Server可以直接通过索引定位到目标行，仅对目标行添加排他锁（X锁），不会影响其他行，因此两个事务可并行执行。

解决方法 #

• 给查询列创建非聚集索引：针对Name列创建非聚集索引，让SQL Server能通过索引快速定位目标行，避免全表扫描，仅锁定符合条件的行：

  CREATE NONCLUSTERED INDEX IX_TestTable_Name ON TestTable(Name)
  

• 优先使用主键/索引列作为WHERE条件：尽量用主键或已创建索引的列作为UPDATE的过滤条件，确保查询能精准定位目标行，减少锁的范围。
• 先查主键再更新：若必须使用无索引列作为条件，可先查询出目标行的主键，再通过主键执行UPDATE，缩小锁的范围。同时可添加锁定提示防止幻读：

  BEGIN TRAN
  DECLARE @TargetId int
  SELECT @TargetId = Id FROM TestTable WITH (UPDLOCK) WHERE Name = 'T1'
  UPDATE TestTable SET Name = 'U' WHERE Id = @TargetId
  

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