如何在SQL Server中实现可序列化隔离级别

Question

我需要在SQL服务器中实现一个可序列化的隔离级别，但是我已经尝试了很多方法，但我没有得到它。

我需要在一个事务中锁定 1 行（是否锁定整个表并不重要）。因此，另一个事务甚至无法选择该行（不读取）。

我尝试的最后一件事：

对于交易1:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SELECT code FROM table1 WHERE code = 1

-- Here I select in another instance the same row

COMMIT TRAN

对于交易2:

BEGIN TRAN
SELECT code FROM table1 WHERE code = 1
COMMIT TRAN

我希望事务2一直等到事务1提交操作，但是事务2给了我行。

如果我错过了什么，谁能解释我？

Answer 1

也许你可以用这样的方法解决这个问题？

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
UPDATE someTableForThisHack set val = CASE WHEN val = 1 THEN 0 else 1 End
SELECT code from table1.....
COMMIT TRANSACTION

因此，您创建一个表格someTableForThisHack并插入一行。

Answer 2

根据文件：

SERIALIZABLE指定了以下内容:

< li >语句无法读取已被修改但尚未被其他事务提交的数据。 < li >在当前事务完成之前，任何其他事务都不能修改当前事务已读取的数据。 < li >在当前事务完成之前，其他事务不能插入键值在当前事务中任何语句读取的键范围内的新行。

如果您没有在事务1中使用INSERT、UPDATE或 对数据进行任何更改，SQL将在读取完成后释放共享锁。

您可能希望尝试添加一个表命中，以防止行锁在事务1结束之前被释放。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

SELECT code 
FROM table1 WITH(ROWLOCK, HOLDLOCK)
WHERE code = 1

COMMIT TRAN

Answer 3

SQL Server 符合可序列化查询的严格定义。也就是说，如果两个查询都按串行顺序运行，则必须有一个逻辑上可以生成的结果 - 事务 1 在事务 2 开始之前完成，反之亦然。

这会产生一些不同于预期的效果。对于SQLPerformance的可串行化隔离级别有一个很好的解释。com，这清楚地说明了这种逻辑可串行性最终意味着什么。（这个网站很有用。）

对于上面的查询，没有逻辑要求阻止第二个查询读取与第一个查询相同的行。无论查询以什么顺序运行，它们都将返回相同的数据，而不进行修改。因为查询分析器可以识别这一点，所以没有理由对数据设置读锁。但是，如果其中一个查询对数据执行了更新，那么(这里的警告逻辑假设，因为我实际上不知道SQL Server如何处理这个问题的内部机制)QA将对所选行设置更强的锁。

TL；DR - SQL Server希望最大限度地减少阻塞，因此它使用逻辑分析来查看可序列化隔离级别需要什么类型的锁，并且它(尝试)使用实现其目标所需的最小数量和强度的锁。

现在我们已经解决了这个问题——我能想到的只有两种方法来锁定一行，这样其他人就不能读取它:使用XLOCK TABLOCK(锁定整个表——不推荐的做法)或者在每一行上有某种形式的字段，当您开始您的进程时它会更新——类似于SPID字段，或者锁定的位标志。当您在事务中更新它时，只有带有NOLOCK提示的SELECTs才能读取它。

显然，这两种情况都不是最佳的。我建议使用“此行繁忙 - 离开”标志，因为这可能是我（几乎）绝对锁定一行的方法。