代码之家 › 专栏 › 技术社区 › Craig Koster

SQL Server隔离级别和表锁定

isolation-level transactions sql-server c#

Craig Koster · 技术社区 · 7 年前

假设SQL server中有一个表,用作需要处理的项目的队列。类似这样:

Id (bigint)
BatchGuid (guid)
BatchProcessed (bit)
...

...以及一些描述需要处理的项目的其他列等。因此,许多正在运行的使用者会根据需要向该表添加记录,以指示需要处理的项目。

现在让我们假设我有一个工作,负责从这个表中获取一批项目并进行处理。假设我们想让它一次处理10个。现在还假设此作业可以同时运行多个实例,因此它可以并发访问表(以及可能正在向队列添加新记录的任何其他使用者)。

我正计划这样做:

using(var tx = new Transaction(Isolation.Serializable))
{
    var batchGuid = //newGuid
    executeSql("update top(10) [QUeueTable] set [BatchGuid] = batchGuid where [BatchGuid] is null");
    var itemsToProcess = executeSql("select * from [QueueTable] where [BatchGuid] = batchGuid");
    tx.Commit()
}

所以基本上,我要做的是启动一个可序列化的事务,用特定的GUID标记10个项目,然后获取这10个项目,然后提交。

这是可行的策略吗?我相信serializable的隔离级别基本上会锁定整个表,以防止读/写,直到事务完成-这是正确的吗?基本上,事务将阻止表上的所有其他读/写操作?我相信这就是我在这种情况下想要的,因为我不想读取脏数据,也不想在标记要处理的10个批时并发运行的作业相互践踏。

任何关于我是否在正确的轨道上的见解都将不胜感激。如果有更好的方法来实现这一点,我也欢迎其他选择。

2 回复 | 直到 7 年前

ColdSolstice 7 年前

可序列化隔离模式不一定会锁定整个表。如果您在BatchGuid上有一个索引,您可能会做得很好,但如果没有,SQL可能会升级为表锁。

您可能需要了解以下几点:

使用 OUTPUT 语句,您可以将更新和选择组合到一个查询中
您可能需要使用 UPDLOCK 如果有多个进程运行此查询

GarethD 7 年前

如果使用 OUTPUT 条款:

UPDATE TOP (10) [QueueTable]
OUTPUT inserted.*
SET [BatchGuid] = batchGuid 
WHERE [BatchGuid] IS NULL;

或者更具体地说:

var itemsToProcess = executeSql("update top(10) [QUeueTable] output inserted.* set [BatchGuid] = batchGuid where [BatchGuid] is null");

我想这是我个人的偏好,但我从来都不是 UPDATE TOP(n) 语法,因为您不能指定 ORDER BY ,并且在大多数情况下,在指定top时,您希望指定order by,我更倾向于使用以下内容:

UPDATE  q
OUTPTUT inserted.*
SET     [BatchGuid] = batchGuid 
FROM    (   SELECT  TOP (10) *
            FROM    dbo.QueueTable
            WHERE   BatchGuid IS NULL
            ORDER BY ID
        ) AS q

附录

作为对评论的回应,我不认为有任何比赛条件的可能性,但我不是百分之百确定。我之所以不相信这一点,是因为尽管查询读起来是一个SELECT和一个更新,但它是一个语法糖,它只是一个更新,并且使用与顶部查询完全相同的计划和锁。然而,由于我不确定,我决定测试:

首先,我在temp DB中设置了一个示例表,以及一个记录表来记录更新的id

USE TempDB;
GO
CREATE TABLE dbo.T (ID BIGINT NOT NULL IDENTITY PRIMARY KEY, Col UNIQUEIDENTIFIER NULL);
INSERT dbo.T (Col)
SELECT TOP 1000000 NULL
FROM sys.all_objects a, sys.all_objects b;

CREATE TABLE dbo.T2 (ID BIGINT NOT NULL PRIMARY KEY);

然后,在10个不同的SSMS窗口中,我运行了以下操作:

WHILE 1 = 1
BEGIN
    DECLARE @ID UNIQUEIDENTIFIER = NEWID();

    UPDATE  T
    SET     Col = @ID
    OUTPUT inserted.ID INTO dbo.T2 (ID)
    FROM    (   SELECT  TOP 10 *
                FROM    dbo.T
                WHERE   Col IS NULL
                ORDER BY ID
            ) t;

    IF @@ROWCOUNT = 0
        RETURN;
END

整个过程运行了20分钟,更新了约500000行,然后我停止了所有10个线程。由于两次更新同一行会在插入到T2时引发主键冲突并出错,并且需要停止所有10个线程,这表明不存在争用条件,为了确认这一点,我运行了以下命令:

SELECT Col, COUNT(*)
FROM dbo.T
WHERE Col IS NOT NULL
GROUP BY Col
HAVING COUNT(*) <> 10;

正如所料,它没有返回任何行。

我很高兴被证明是错的,我承认我很幸运,这100000次迭代中没有一次发生冲突,但我不相信这是运气。我真的相信只有一个锁,因此不管您是否有事务,您只需要正确的隔离级别。