O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

c47tnt003

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required