避免“丢失更新"的最小事务隔离级别. [英] Minimum transaction isolation level to avoid "Lost Updates"

问题描述

使用SQL Server的事务隔离级别，您可以避免某些不必要的并发问题，例如脏读等.

With SQL Server's transaction isolation levels, you can avoid certain unwanted concurrency issues, like dirty reads and so forth.

我现在最感兴趣的一个是丢失的更新-事实是两个事务可以覆盖彼此的更新而没有人注意到.我看到并听到关于至少必须选择哪种隔离级别来避免这种情况的矛盾声明.

The one I'm interested in right now is lost updates - the fact two transactions can overwrite one another's updates without anyone noticing it. I see and hear conflicting statements as to which isolation level at a minimum I have to choose to avoid this.

Kalen Delaney在她的"SQL Server Internals"一书中说(第10章-事务和并发-第592页):

Kalen Delaney in her "SQL Server Internals" book says (Chapter 10 - Transactions and Concurrency - Page 592):

在读未提交"隔离中，除了丢失的更新以外，所有先前描述的行为都是可能的.

In Read Uncommitted isolation, all the behaviors described previously, except lost updates, are possible.

另一方面，一位独立的SQL Server培训师给我们上了一堂课，告诉我们，我们至少需要可重复读"，以避免更新丢失.

On the other hand, an independent SQL Server trainer giving us a class told us that we need at least "Repeatable Read" to avoid lost updates.

那谁是对的?为什么?

推荐答案

本书中的示例是A职员和B职员收到小部件货物的情况.

The example in the book is of Clerk A and Clerk B receiving shipments of Widgets.

他们都检查当前库存，查看有25个库存.职员A有50个小部件，并且有75个更新，职员B有20个小部件，因此有45个更新覆盖了先前的更新.

They both check the current inventory, see 25 is in stock. Clerk A has 50 widgets and updates to 75, Clerk B has 20 widgets and so updates to 45 overwriting the previous update.

我认为她的意思是秘书A可以在所有隔离级别上避免这种现象

I assume she meant this phenomena can be avoided at all isolation levels by Clerk A doing

UPDATE Widgets
SET StockLevel = StockLevel + 50
WHERE ...

和秘书B做

UPDATE Widgets
SET StockLevel = StockLevel + 20
WHERE ...

当然，如果SELECT和UPDATE是作为单独的操作完成的，则需要repeatable read来避免这种情况，因此在事务期间将对行的S锁保持不变(这将导致死锁)在这种情况下)

Certainly if the SELECT and UPDATE are done as separate operations you would need repeatable read to avoid this so the S lock on the row is held for the duration of the transaction (which would lead to deadlock in this scenario)

这篇关于避免“丢失更新"的最小事务隔离级别.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！