Transaction Isolation Levels

Any study of how transactions impact performance must include transactional integrity, which refers to the quality, or fidelity, of the transaction. There are three types of side-effects of isolation levels that can violate transactional integrity: dirty reads, nonrepeatable reads, and phantom rows.

The level of isolation between transactions can be adjusted to control which transactional faults are permitted. The ANSI SQL-92 committee specified four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

SQL Server 2005 introduced two additional row versioning “SQL Server 2005 introduced row versioning, “ which enables two levels of optimistic transaction isolation: Snapshot, and Read Committed Snapshot. All six transaction isolation levels are listed in Table 47.3 and then detailed in this section.

Table 47.3 ANSI-92 Isolation Levels


Internally, SQL Server uses locks for isolation (locks are still used in snapshot-based isolation levels; the difference is that they typically do not block other operations)), and the transaction isolation levels determines the duration of the share lock or exclusive lock for the transaction, as listed in Table 47.4.

Table 47.4 Isolation Levels and Lock Duration

Isolation Level Share-Lock Duration Exclusive-Lock Duration
Read Uncommitted None Held only long enough to prevent physical ...

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.