sql - SQL Server 锁定在这种情况下如何工作？

Question

假设我有一笔交易:

BEGIN TRANSACTION
DECLARE MONEY @amount
SELECT Amount AS @amount
  FROM Deposits
  WHERE UserId = 123
UPDATE Deposits
  SET Amount = @amount + 100.0
  WHERE UserId = 123
COMMIT

它在 2 个线程上执行，顺序为:

1. 线程 1 - 选择
2. 线程 2 - 选择
3. 主题 1 - 更新
4. 话题 2 - 更新

假设执行前Amount为0。

这种情况下，SQL Server的不同设置(未提交读、已提交读、可重复读、可序列化)会发生什么情况，最后的数量是多少，会出现死锁吗？

Answer 1

很好的陈述场景。我决定测试一下。

这是我的设置脚本:

CREATE TABLE Deposits(Amount Money, UserID int)
INSERT INTO Deposits (Amount, UserID)
SELECT 0.0, 123
--Reset
UPDATE Deposits
SET Amount = 0.00
WHERE UserID = 123

这是我的测试脚本。

SET TRANSACTION ISOLATION LEVEL Serializable
----------------------------------------
-- Part 1
----------------------------------------
BEGIN TRANSACTION
DECLARE @amount MONEY
SET @amount =
(
SELECT Amount
FROM Deposits
WHERE UserId = 123
)
SELECT @amount as Amount
----------------------------------------
-- Part 2
----------------------------------------
DECLARE @amount MONEY
SET @amount =  *value from step 1*
UPDATE Deposits
SET Amount = @amount + 100.0
WHERE UserId = 123
COMMIT
SELECT *
FROM Deposits
WHERE UserID = 123

我在两个查询分析器窗口中加载了这个测试脚本，并按照问题所述运行了每个部分。

所有读取都发生在任何写入之前，因此所有线程/场景都会将 0 值读入 @amount。

结果如下:

读取已提交

1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00

未提交的读取

1 T1.@Amount = 0.00
2 T1.@Amount = 0.00
3 Deposits.Amount = 100.00
4 Deposits.Amount = 100.00

可重复读取

1 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit.UserID = 123)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00

可序列化

1 T1.@Amount = 0.00 (locks out changes by others on Deposit)
2 T1.@Amount = 0.00 (locks out changes by others on Deposit)
3 Hangs until step 4. (due to lock in step 2)
4 Deadlock!
Final result: Deposits.Amount = 100.00

以下是对每种类型的解释，可用于通过思维模拟获得这些结果。

已提交读和未提交读，两者都不会锁定已读取的数据以防止其他用户修改。不同之处在于，未提交读取将允许您查看尚未提交的数据(缺点)，并且如果有数据被其他人锁定以防止读取(优点)，则不会阻止您的读取，这实际上是同一件事说了两次。

可重复读取和可序列化，两者的行为都类似于为读取而提交的读取。对于锁定，两者都会锁定已读取的数据以防止其他用户修改。不同之处在于，可序列化阻止的不仅仅是已读取的行，它还阻止会引入以前不存在的记录的插入。

因此，通过可重复读取，您可以在以后的读取中看到新记录(称为:幻像记录)。使用可序列化，您可以阻止这些记录的创建，直到您提交为止。

以上解释来 self 对此的解读msdn文章。