sql - 应该使用什么类型的 Transaction IsolationLevel 来忽略插入但锁定选定的行？

Question

我有一个进程启动事务，将记录插入 Table1，然后调用长时间运行的 Web 服务(最多 30 秒)。如果 Web 服务调用失败，则插入回滚(这是我们想要的)。这是插入的示例(实际上是对多个表的多次插入，但我正在简化这个问题):

INSERT INTO Table1 (UserId, StatusTypeId) VALUES (@UserId, 1)

我有第二个进程从第一步查询 Table1，如下所示:

SELECT TOP 1 * FROM Table1 WHERE StatusTypeId=2

然后为用户更新该行。当进程 1 运行时，Table1 被锁定，因此进程 2 将在进程 1 完成之前完成，这是一个问题，因为在进程 1 完成其 Web 服务调用时引入了长时间的延迟。

进程 1 只会插入 1 的 StatusTypeId，它也是唯一插入 Table1 的操作。进程 2 只会查询 StatusTypeId = 2。我想告诉进程 2 忽略对 Table1 的任何插入，但锁定它选择的行。 Process 2 的默认隔离级别等待太多，但我担心 IsolationLevel.ReadUncommitted 允许读取太多脏数据。我不希望两个用户运行 Process 2 然后意外地获得同一行。

除了 ReadUncommitted 之外，是否还有其他 IsolationLevel 可以使用，它表示忽略插入的行但确保选择锁定了选定的行？

Answer 1

关于 SELECT 被插入阻塞，这应该可以通过提供适当的索引来避免。

测试表。

CREATE TABLE Table1
(
UserId INT PRIMARY KEY,
StatusTypeId INT,
AnotherColumn varchar(50)
)
insert into Table1
SELECT number, (LEN(type)%2)+1, newid()
FROM master.dbo.spt_values
where type='p'

查询窗口一

BEGIN TRAN
INSERT INTO Table1 (UserId, StatusTypeId) VALUES (5000, 1)
WAITFOR DELAY '00:01';
ROLLBACK

查询窗口二( block )

SELECT TOP 1 * 
FROM Table1 
WHERE StatusTypeId=2 
ORDER BY AnotherColumn

但是如果您在添加索引后重试测试，它不会阻止 CREATE NONCLUSTERED INDEX ix ON Table1 (StatusTypeId,AnotherColumn)

关于 Process 2 的行锁定，您可以使用以下方法(READPAST 提示将允许 2 个并发 Process 2 事务开始处理不同的行，而不是一个阻塞另一个)。您可能会发现 this article by Remus Rusanu 相关

BEGIN TRAN

SELECT TOP 1 * 
FROM Table1  WITH (UPDLOCK, READPAST)
WHERE StatusTypeId=2
ORDER BY AnotherColumn

/*
Rest of Process Two's code here
*/
COMMIT