Database Reference
In-Depth Information
Figure 21-5.
Snapshot isolation level and writers behavior (1)
Session 2 wants to update all rows where
Cancelled = 1
. It starts to scan the table, and when it needs to read the
data for
OrderId = 10
, it reads the row from the version store; that is, the last committed version before the Session 2
transaction started. This version is the original (non-updated) version of the row and it has
Cancelled = 0
, so
Session 2 does not need to update it. Session 2 continues scanning the rows without being blocked by update (U) and
exclusive (X) locks incompatibility.
Similarly, Session 3 wants to update all rows with
Amount = 29.95
. When it reads the version of the row from the
version store, it determines that the row needs to be updated. Again, it does not matter that Session 1 also changes
the amount for the same row. At this point, a “new version” of the row has not been committed and it is invisible to
the other sessions. Now Session 3 wants to update the row in the database, tries to acquire exclusive (X) lock, and is
blocked because Session 1 already has exclusive (X) lock there.
There is another possibility, however. Let's consider the following scenario, keeping in mind the transaction
consistency that a snapshot isolation level guarantees.
In the example shown in Figure
21-6
, Session 1 starts a transaction and updates one of the rows. In the next step,
Session 2 starts another transaction. In fact, it does not really matter what session starts the transaction first, as long as
a new version of the row with
OrderId = 10
is not committed.
