事务并发隔离 - 为什么我可以更新另一个事务记录的子集

Question

我试图理解我遇到的一个问题，我认为在使用读取提交隔离级别处理事务时应该不可能。我有一个用作队列的表。 在一个线程（连接 1）中，我将多批 20 条记录插入到每个表中。 每批 20 条记录在事务中执行。 在第二个线程（连接 2）中，我执行更新以更改已插入队列的记录的状态，这也发生在事务中。 并发运行时，我期望受更新（连接 2）影响的行数应该是 20 的倍数，因为连接 1 在事务中以 20 行的批次插入表中的行。

但我的测试表明情况并非总是如此，有时我能够从连接 1 的批处理中更新记录子集。 这是否可行，还是我缺少有关事务、并发性和隔离级别的某些内容？ 下面是我创建的一组测试脚本，用于在 T-SQL 中重现此问题。

此脚本以 20 个事务批次将 20,000 条记录插入表中。

USE ReadTest
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
SET NOCOUNT ON
DECLARE @trans_id INTEGER
DECLARE @cmd_id INTEGER
DECLARE @text_str VARCHAR(4000)
SET @trans_id = 0
SET @text_str = 'Placeholder String Value'                
-- First empty the table
DELETE FROM TABLE_A
WHILE @trans_id < 1000 BEGIN
    SET @trans_id = @trans_id + 1
    SET @cmd_id = 0
    BEGIN TRANSACTION
--  Insert 20 records into the table per transaction
    WHILE @cmd_id < 20 BEGIN
        SET @cmd_id = @cmd_id + 1
        INSERT INTO TABLE_A ( transaction_id, command_id, [type], status, text_field ) 
            VALUES ( @trans_id, @cmd_id, 1, 1,  @text_str )
    END             
    COMMIT
END
PRINT 'DONE'

此脚本更新表中的记录，将状态从 1 更改为 2，然后检查更新操作中的行计数。 当行计数不是 20 的倍数时，print 语句指示此值以及受影响的行数。

USE ReadTest
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
SET NOCOUNT ON
DECLARE @loop_counter INTEGER
DECLARE @trans_id INTEGER
DECLARE @count INTEGER
SET @loop_counter = 0
WHILE @loop_counter < 100000 BEGIN
    SET @loop_counter = @loop_counter + 1
    BEGIN TRANSACTION
        UPDATE TABLE_A SET status = 2 
        WHERE status = 1
            and type = 1
        SET @count = @@ROWCOUNT
    COMMIT
    IF ( @count % 20 <> 0 ) BEGIN
--      Records in concurrent transaction inserting in batches of 20 records before commit.
        PRINT '*** Rowcount not a multiple of 20. Count = ' + CAST(@count AS VARCHAR) + ' ***'
    END
    IF @count > 0 BEGIN
--      Delete the records where the status was changed.
        DELETE TABLE_A WHERE status = 2
    END
END
PRINT 'DONE'

此脚本在名为 ReadTest 的新数据库中创建测试队列表。

USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ReadTest')
  BEGIN;
  DROP DATABASE ReadTest;
  END;
GO
CREATE DATABASE ReadTest;
GO
ALTER DATABASE ReadTest
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE ReadTest
SET READ_COMMITTED_SNAPSHOT OFF
GO
USE ReadTest
GO
CREATE TABLE [dbo].[TABLE_A](
    [ROWGUIDE] [uniqueidentifier] NOT NULL,
    [TRANSACTION_ID] [int] NOT NULL,
    [COMMAND_ID] [int] NOT NULL,
    [TYPE] [int] NOT NULL,
    [STATUS] [int] NOT NULL,
    [TEXT_FIELD] [varchar](4000) NULL
 CONSTRAINT [PK_TABLE_A] PRIMARY KEY NONCLUSTERED 
(
    [ROWGUIDE] ASC
) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[TABLE_A] ADD  DEFAULT (newsequentialid()) FOR [ROWGUIDE]
GO

Answer 1

你的期望完全错位了。您从未在查询中表达过将正好 20 行"取消排队"的要求。UPDATE 可以返回 0、19、20、21 或 1000 行，并且所有结果都是正确的，只要status为 1，type为 1。如果您预计"取消排队"的顺序发生（在您的问题中以某种方式回避了这一点，但从未明确说明过），那么您的"取消排队"操作必须包含ORDER BY子句。如果您添加了这样一个明确声明的要求，那么您对"取消排队"始终返回整批"enqueue"行（即 20 行的倍数）的期望将更接近于成为合理的期望。就目前的情况而言，正如我所说，完全错位了。

有关较长的讨论，请参阅使用表作为队列。

我不应该担心当一个事务提交 一批 20 条插入的记录，只有另一个并发事务 能够更新这些记录的子集而不是全部 20 条记录吗？

基本上，问题归结为如果我在插入时选择，我会看到多少插入的行？仅当隔离级别声明为可序列化时，您才有权关注。其他隔离级别都不会预测在 UPDATE 运行时插入的行数是可见的。只有 SERIALIZABLE 声明结果必须与一个接一个地运行两个语句相同（即序列化，因此得名）。虽然一旦您考虑了物理顺序和缺少 ORDER BY 子句，更新如何"看到"仅部分 INSERT 批处理的技术细节就很容易理解，但解释是无关紧要的。根本问题是期望是没有根据的。即使通过添加适当的 ORDER BY 和正确的簇状索引键来"修复"问题"（上面链接的文章解释了详细信息），期望仍然是没有保证的。更新"看到"1、19 或 21 行仍然是完全合法的，尽管这不太可能发生。

我想我一直理解 READ COMMIT 只读取 数据，并且事务提交是一个原子操作，使 事务中发生的更改立即可用。

这是正确的。不正确的是期望并发 SELECT（或更新）看到整个更改，与它在执行中的位置无关。打开 SSMS 查询并运行以下命令：

use tempdb;
go
create table test (a int not null primary key, b int);
go
insert into test (a, b) values (5,0)
go
begin transaction
insert into test (a, b) values (10,0)

现在打开一个新的 SSMS 查询并运行以下命令：

update test 
    set b=1
    output inserted.*
    where b=0

这将阻止未提交的 INSERT 后面。现在返回到第一个查询并运行以下命令：

insert into test (a, b) values (1,0)
commit

提交此内容时，第二个 SSMS 查询将完成，它将返回两行，而不是三行。QED。这是读取提交。您期望的是可序列化执行（在这种情况下，上面的示例将死锁）。

Answer 2

它可能像这样发生：

写入器
1. /插入器写入 20 行（不提交）
2. 读取器/更新程序读取一行（未提交 - 它丢弃它）
3. 编写器/插入器提交
4. 读取器/更新程序读取 19 行，这些行现在已提交，因此可见

我相信只有可序列化的隔离级别（或更并发的快照隔离）才能解决此问题。