Database Reference
In-Depth Information
Chapter 21
Optimistic Isolation Levels
Optimistic transaction isolation levels
were introduced in SQL Server 2005 as a new way to deal with blocking problems
and to address data consistency issues. As opposed to pessimistic transaction isolation levels, with optimistic transaction
isolation levels, queries read “old” committed versions of rows while accessing the data modified by the other sessions,
rather than being blocked by the incompatibility of shared (S) and exclusive (X) locks.
Row Versioning Overview
With optimistic transaction isolation levels, when updates occur, SQL Server stores the old versions of the rows in a
special part of tempdb called
version store
. The original rows in the database are also updated with 14-byte pointers
that reference the old versions of the rows. Depending on the situation, you can have more than one version of the
records stored for the row. Figure
21-1
illustrates this behavior.
Figure 21-1.
Version store
Now when readers (and sometimes writers) access the row that holds exclusive (X) lock, they get the old version
from the version store rather than being blocked, as shown in Figure
21-2
.
