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 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.
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.
So who's right?? And why??
7条答案
按热度按时间bgtovc5b1#
I dont know if it is too late to answer but I am just learning about transaction isolation levels in college and as part of my research I came across this link:
Microsoft Technet
Specifically the paragraph in question is:
Lost Update
*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.
So in essence both people are right.
Personally (and I am open to being wrong, so please correct me as I am just learning this) I take from this the following two points:
Again, please correct me if anything here is wrong as I would like to understand this too.
moiiocjp2#
The example in the book is of Clerk A and Clerk B receiving shipments of Widgets.
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.
I assume she meant this phenomena can be avoided at all isolation levels by Clerk A doing
and Clerk B doing
Certainly if the
SELECT
andUPDATE
are done as separate operations you would needrepeatable read
to avoid this so theS
lock on the row is held for the duration of the transaction (which would lead to deadlock in this scenario)zynd9foi3#
Lost updates may occur even if reads and writes are in separate transactions, like when users read data into Web pages, then update. In such cases no isolation level can protect you, especially when connections are reused from a connection pool. We should use other approaches, such as rowversion. Here is my canned answer.
xfyts7mz4#
My experience is that with
Read Uncommitted
you no longer get 'lost updates', you can however still get 'lost rollbacks'. The SQL trainer was probably referring to that concurrency issue, so the answer you're likely looking for isRepeatable Read
.That said, I would be very interested if anyone has experience that goes against this.
u0sqgete5#
As marked by Francis Rodgers, what you can rely on SQL Server implementation is that once a transaction updated some data, every isolation level always issue "update locks" over the data, and denying updates and writes from another transaction, whatever it's isolation level it is. You can be sure this kind of lost updates are covered.
However, if the situation is that a transaction reads some data (with an isolation level different than Repeatable Read), then another transaction is able to change this data and commits it's change, and if the first transaction then updates the same data but this time, based on the internal copy that he made, the management system cannot do anything for saving it.
Your answer in that scenario is either use Repeatable Read in the first transaction, or maybe use some read lock from the first transaction over the data (I don't really know about that in a confident way. I just know of the existence of this locks and that you can use them. Maybe this will help anyone who's interested in this approach Microsoft Designing Transactions and Optimizing Locking ).
wswtfjt76#
The following is quote from
70-762 Developing SQL Databases (p. 212)
:Another potential problem can occur when two processes read the same row and then update that data with different values. This might happen if a transaction first reads a value into a variable and then uses the variable in an update statement in a later step. When this update executes, another transaction updates the same data. Whichever of these transactions is committed first becomes a lost update because it was replaced by the update in the other transaction. You cannot use isolation levels to change this behavior, but you can write an application that specifically allows lost updates.
So, it seems that none of the isolation levels can help you in such cases and you need to solve the issue in the code itself. For example:
Create the table, the execute each part of the code in separate query windows.
Changing the isolation level does nothing. For example, the only difference between
read committed
andrepeatable read
is that the last, blocks the second transaction while the first is finished and then overwrites the value.The above can be demonstrated only under on
read committed
isolation level, as underrepeatable read
the first transaction is rollbacked as deadlock victim.xytpbqjk7#
Cloudy enough, what we mean on lost updates.
Suppose we mean we read something, then based on that value, later, execute an update based on that value. This can cause so called "lost update" unless the read operation takes and holds an S lock on the resource until the end of the transaction. This can be achieved by either elevate the isolation level at least repeatable reads (which does exactly this), or use some lock hint which has equivalent effect. However this introduces the increased probability of deadlock, see Martin Smith comment about it, below his answer. (so in case if one insist to read something, then keep it for a while, then use the previuosly read value, better to use update lock hint, to prevent deadlock)
However the statement below never will cause lost update, because it takes and holds an U->X lock on the resource, regardless of isolation level:
Probably the term "lost updates" can not interpreted without a particular code context. A particular code can cause lost updates because of incorrectly handling race conditions.