SQL Server 查询 - 为什么会出现死锁？

Question

我有以下代码:

set transaction isolation level read committed; --this is for clarity only

DECLARE @jobName nvarchar(128);

BEGIN TRAN
    SELECT @jobName = JobName
    FROM dbo.JobDetails
    WHERE ExecutionState_Status = 1

    WAITFOR DELAY '00:00:10'

    UPDATE dbo.JobDetails
    SET ExecutionState_Status = 10
    WHERE JobName = @jobName

COMMIT 

第二部分几乎相同:

set transaction isolation level read committed;

DECLARE @jobName nvarchar(128);

BEGIN TRAN
    SELECT @jobName = JobName
    FROM dbo.JobDetails
    WHERE ExecutionState_Status = 1

    WAITFOR DELAY '00:00:15'

    UPDATE dbo.JobDetails
    SET ExecutionState_Status = 20
    WHERE JobName = @jobName

COMMIT 

区别在于我们设置的状态(10 与 20)和延迟(10 秒与 15 秒)。

我在 Management Studio 中并行执行它们 - 两个选项卡。现在的问题是 - 使用读已提交事务隔离级别，它按预期工作 - 应用了最后一次修改并且两个脚本都成功执行。

但这不是我想要的 - 我只想执行一个，而第二个不应该执行任何操作。这就是为什么我尝试将级别更改为可重复读取。根据我的知识(我现在想挑战)它应该是这样的:

• 第一个事务启动并锁定它读取的行
• 第一笔交易将等待 10 秒
• 第二个事务同时启动，并且无法执行选择，因为它被第一个事务锁定
• 第一个事务完成等待，更新表并提交
• 然后第二个事务可以继续进行，但不会执行任何操作，因为所有状态 = 1 的行都已更新

不幸的是，我看到的结果与此相去甚远 - 事务陷入僵局，其中一个事务被 SQL Server 终止。我真的不明白为什么会发生这种情况，因为它们以相同的顺序访问资源。

以下是测试所需的脚本:

CREATE TABLE [dbo].[JobDetails](
    [JobName] [nvarchar](128) NOT NULL,
    [ExecutionState_Status] [int] NULL DEFAULT ((0)),
 CONSTRAINT [PK_dbo.JobDetails] PRIMARY KEY CLUSTERED 
(
    [JobName] ASC
)) 
GO

INSERT INTO JobDetails VALUES( 'My Job', 1)
UPDATE JobDetails SET ExecutionState_Status = 1

附加说明:

• 我仅使用表中的一行进行测试。
• 将级别更改为可序列化也会导致死锁。
• 这段代码看起来像这样的原因是因为我试图模拟 ORM 将要做的事情 - 首先获取实体，然后在状态为 1 时 checkin 代码，然后使用 WHERE 发送更新 基于PK。我知道我可以在没有 ORM 更新的情况下编写该代码 WHERE ExecutionState_Status = 1

Answer 1

这个假设是错误的:

second transaction starts in the meantime and cannot execute the select since it's locked by the first one

两个可重复读取事务的select都获取并保持S键上的锁，直到提交。 S 锁兼容。当update尝试获取与S锁不兼容的X锁时，它们会陷入死锁。与此相反，read commited 事务中的 select 会立即释放 S 锁。

使用 exec sp_lock 来查看锁，例如

DECLARE @jobName nvarchar(128);

BEGIN TRAN
    SELECT @jobName = JobName
    FROM dbo.JobDetails
    WHERE ExecutionState_Status = 1

    WAITFOR DELAY '00:00:10'

    exec sp_lock  58,57

    UPDATE dbo.JobDetails
    SET ExecutionState_Status = 10
    WHERE JobName = @jobName

COMMIT