时间戳

首页 > 解决方案 > 更新查询死锁仅在两个以上事务同时发生时

sql - 更新查询死锁仅在两个以上事务同时发生时

问题描述

在编写一些用于连接产品数据库的 SQL Server 更新查询时，我遇到了这样一种情况：如果具有这些更新的两个事务同时运行，一切都很好 - 但是当两个以上的事务试图同时更新同一个表时，其中一些陷入僵局。

我将问题分解为对表定义的“UNIQUE NONCLUSTERED”约束。当我删除此约束时，所有事务都将等待其所需的资源并在没有错误的情况下完成。

这是我重现问题的示例代码：

CREATE TABLE [dbo].[profiles]
(
    [ProfileID] [int] IDENTITY(1,1) NOT NULL, 
    [ProfileName] [nvarchar](255) NOT NULL, 
    [GroupFK] [int] NULL

    CONSTRAINT [PK_Profile] 
        PRIMARY KEY CLUSTERED ([ProfileID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    -- !!! [UI_UniqueNameInGroup] seems problematic for me because i get deadlocks when executing more than two transactions at once !!!
    CONSTRAINT [UI_UniqueNameInGroup] 
        UNIQUE NONCLUSTERED ([GroupFK] ASC, [ProfileName] ASC)
                      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                            ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO profiles (ProfileName) VALUES ('PROFILE_99')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_88')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_77')
INSERT INTO profiles (ProfileName) VALUES ('PROFILE_66')

这些是我的交易样本：

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_99' 
     WHERE ProfileID = 4

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_66' 
     WHERE ProfileID = 1

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_88' 
     WHERE ProfileID = 3

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

BEGIN TRAN  
     UPDATE profiles 
     SET ProfileName = 'NewProfile_77' 
     WHERE ProfileID = 2

     WAITFOR DELAY '00:00:05.000'
COMMIT TRAN

这是我的死锁图：

死锁图1.xdl

死锁图2.xdl

请通过向我解释这个问题来帮助我 - 我不明白为什么只有在同时执行两个以上的事务时才会发生死锁。

我也很高兴为此找到一个可行的解决方案。

我这边有解决方案（不更改数据库方案）吗？

标签： sqlsql-servertransactionssql-updatedeadlock

解决方案

更改约束 [UI_UniqueNameInGroup] 以仅允许行锁：

CONSTRAINT [UI_UniqueNameInGroup] UNIQUE NONCLUSTERED ([GroupFK] ASC, [ProfileName] ASC)
WITH (PAD_INDEX = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = OFF
) ON [PRIMARY]

这会导致问题，因为如果您拒绝页锁和行锁，那么您会强制 SQL Server 使用表锁，但其他会话已经设置了表锁。

推荐阅读