Database Reference
In-Depth Information
By default, using SNAPSHOT isolation level is prohibited. You must enable it with an
ALTER DATABASE SET
ALLOW_SNAPSHOT_ISOLATION ON
statement. This statement does not require exclusive database access, and it can be
executed with other users connected to the database.
A SNAPSHOT isolation level provides transaction-level consistency. Transactions will see a
snapshot
of the data
at the moment when transaction started regardless of how long the transaction is active and how many data changes
were made in other transactions during that time.
In the example shown in Figure
21-4
, we have a Session 1 that starts the transaction and reads the row at time T1.
At time T2, we have a Session 2 that modifies the row in the implicit transaction. At this moment, the old (original)
version of the row moved to the version store in tempdb.
Figure 21-4.
Snapshot isolation level and readers behavior
In the next step, we have a Session 3 that starts another transaction and reads the same row at time T3. It sees the
version of the row as modified and committed by Session 2 (at time T2). At time T4, we have a Session 4 that modifies
the row in the implicit transaction again. At this time, we have two versions of the rows in the version store—one that
existed between T2 and T4 and the original version that existed before T2. Now, if Session 3 runs the select again, it
would use the version that existed between T2 and T4 because this version was committed at the time as the Session 3
transaction started. Similarly, Session 1 would use the original version of the row that existed before T2. At some
point, after Session 1 and Session 3 are committed, the version store clean up task would remove both records from
the version store, assuming, of course, that there are no other transactions that need them.
A SNAPSHOT isolation level provides similar data consistency as a SERIALIZABLE isolation level with no
blocking involved, although it could generate an enormous amount of data in the tempdb. If you have a session that
deletes millions of rows from the table, all of those rows need to be copied to the version store, even if the original
delete statement is running in a non-snapshot isolation mode, just to preserve the state of the data for possible
snapshot or RCSI transactions.
Now let's examine the writer's behavior. Let's assume that Session 1 starts the transaction and updates one of the
rows. That session holds an exclusive (X) lock there, as shown in Figure
21-5
.
