sql - SQL UPDATE 操作是否将数据读取到 "local memory"？

Question

This answer报价 this Technet article这解释了丢失更新的两种解释:

A lost update can be interpreted in one of two ways. In the first scenario, a lost update is considered to have taken place when data that has been updated by one transaction is overwritten by another transaction, before the first transaction is either committed or rolled back. This type of lost update cannot occur in SQL Server 2005 because it is not allowed under any transaction isolation level.

The other interpretation of a lost update is when one transaction (Transaction #1) reads data into its local memory, and then another transaction (Transaction #2) changes this data and commits its change. After this, Transaction #1 updates the same data based on what it read into memory before Transaction #2 was executed. In this case, the update performed by Transaction #2 can be considered a lost update.

所以看起来不同之处在于，在第一个场景中，整个更新发生在“本地内存”之外，而在第二个场景中，使用了“本地内存”，这有所不同。

假设我有以下代码:

UPDATE MagicTable SET MagicColumn = MagicColumn + 10 WHERE SomeCondition

这是否涉及“本地内存”？是否倾向于对丢失的更新进行第一种或第二种解释？

Answer 1

我想它会属于第二种解释。

然而，这种类型的 UPDATE 在 SQL Server 中实现的方式仍然不可能丢失更新。为更新读取的行受到 U 锁的保护(当行实际更新时转换为 X 锁)。

U 锁与其他U 锁(或X 锁)不兼容

因此在所有隔离级别上，如果两个并发事务要运行此语句，那么其中一个将最终被另一个事务的 U 锁或 X 锁阻塞，并且在该交易完成之前无法继续。

因此，在任何隔离级别的 SQL Server 中，使用此模式都不可能发生丢失更新。

要实现丢失的更新，你需要做类似的事情

BEGIN TRAN

DECLARE @MagicColumn INT;

/*Two concurrent transactions can both read the same pre-update value*/
SELECT @MagicColumn = MagicColumn FROM MagicTable WHERE SomeCondition

UPDATE MagicTable SET MagicColumn = @MagicColumn + 10 WHERE SomeCondition

COMMIT