c# - SQL 冲突，来自不同线程/进程的并发 UPDATE 和 SELECT

Question

我的团队正在努力解决一个关于并发线程/进程的奇怪的 Microsoft SQL Server 问题。在极少数情况下，两个不同线程上的 UPDATE 和同步 SELECT 之间似乎存在竞争条件。很难重现，因为时间很关键。

当冲突发生时，SELECT 不会返回所需的记录，尽管不会生成错误。相反，SELECT 的行为就好像记录不存在一样，这是不正确的。结果我们的服务变得困惑，在我们恢复之前会出现一些困惑。

为了消除我们服务中出现错误的可能性，我使用纯 C# 代码和 System.Data.SqlClient 重现了该问题。我很乐意应要求提供该代码。我已尝试将代码和表格减少到重现问题所需的最低限度。

测试表是这样创建的:

CREATE TABLE [TestTable] 
(
    [RecordId] int IDENTITY(1,1) UNIQUE,
    [Locked] int NOT NULL DEFAULT 0,
    [Priority] int NULL,
    [Status] int NULL,
    [SystemName] nvarchar(50) NULL
)

该表有一个这样创建的索引:

CREATE NONCLUSTERED INDEX [IDX_GENERAL] 
ON [TestTable] ([Status], [SystemName], [Locked]) 
INCLUDE ([RecordId], [Priority])

该表包含一条这样创建的记录:

INSERT INTO [TestTable] ([Locked], [Priority], [Status], [SystemName])
VALUES (0, 3, 3000, 'System1')

在我们的服务中，多个线程和进程通过自动将它们的“锁定”字段设置为一个来获取对记录的独占写入权限。然后在进行任何必要的修改之后，线程/进程必须通过将其“锁定”列重置为零来释放记录。我看到的是一种极其罕见的情况，当一个线程解锁记录时，同时另一个线程试图找到它:

一个线程执行这样的更新(成功):

UPDATE [TestTable] 
SET [Locked] = 0 
OUTPUT INSERTED.* 
WHERE [Locked] = 1 
  AND [RecordId] = 1 
  AND [Status] = 3000 
  AND [SystemName] = 'System1'

同时，另一个线程像这样执行 SELECT(返回为空):

SELECT [RecordId] 
FROM [TestTable] 
WHERE [Status] = 3000 
  AND [SystemName] = 'System1' 
ORDER BY Priority DESC, RecordId ASC

我认为索引是问题的一部分，因为如果我删除 Status 或 SystemName 键，那么我将无法再重现该问题。我不知道什么会导致这种行为。我读到的所有内容都表明这根本不可能发生。

我欢迎任何有关如何解决此问题的问题、想法或建议...

Answer 1

你的逻辑有问题。在开始时，许多线程尝试获取记录的 id 以进行锁定。

 SELECT [RecordId] 
 FROM [TestTable] 
 WHERE [Locked]=0 AND [Status]=3000 AND [SystemName]='System1' 
 ORDER BY Priority DESC, RecordId ASC

然后他们尝试设置锁

UPDATE [TestTable] 
SET [Locked]=1 
OUTPUT INSERTED.* 
WHERE [Locked]=0 AND [RecordId]={0} 
      AND [Status]=3000 AND [SystemName]='System1'

自然有可能发生

1. 第一个线程获取解锁记录的ID
2. 第二个线程获取解锁记录的ID
3. 第二个线程尝试锁定记录并成功
4. 第一个线程尝试锁定记录但失败

是a race condition bug .

A race condition is a bug that occurs when the outcome of a program depends on which of two or more threads reaches a particular block of code first. Running the program many times produces different results, and the result of any given run cannot be predicted.

在你的情况下，这不是一个错误，而是你的逻辑的一些罕见情况。
你有两个选择

1. 为两个查询打开共同事务。 (向数据库锁问好)
2. 考虑到在执行时第二个查询记录可能已经被另一个线程锁定。 (我会选择这个选项)