Microsoft SQL Server 2012 Bible

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 ...