16.2 Isolation Levels

The transaction isolation level specifies the transaction locking level for a connection. It determines the extent to which changes to data within a transaction are visible outside that transaction while uncommitted.

Table 16-1 lists and describes problems that might occur if several users access data concurrently without locking.

Table 16-1. Concurrency problems
Condition	Description
Lost update	Two or more transactions select the same row and subsequently update the row. The transactions are unaware of each other and, as a result, updates overwrite one another, resulting in lost data.
Uncommitted dependency (dirty read)	A second transaction selects a row that has been updated, but not committed, by another transaction. The data being read might be further updated or rolled back by the original transaction, resulting in invalid data in the second transaction.
Inconsistent analysis (nonrepeatable read)	A second transaction reads different data each time the same row is read. The second transaction reads data that has been changed and committed by another transaction between the reads.
Phantom read	An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected within the transaction are missing the newly inserted rows and contain deleted rows that no longer exist.

Locks ensure transactional integrity and maintain database consistency by controlling how resources can be accessed by concurrent transactions. A lock is an object that indicates a user has some dependency on a resource. Other users are prevented from performing operations that would adversely affect the dependency of the user with the lock. Locks are managed internally by system software and acquired and released as a result of actions taken by users. Table 16-2 lists and describes resource lock modes used by ADO.NET.

Table 16-2. Resource lock modes
Lock mode	Description
Shared	Allows concurrent transactions to read the locked resource. Another transaction can't modify the locked data while the lock is held.
Exclusive	Prevents access, both read and modify, to a resource by concurrent transactions.

Isolation level is the level at which a transaction is prepared to accept inconsistent data; it is the degree to which one transaction must be isolated from other transactions. As the isolation level increases, access to current data increases at the expense of data correctness. Table 16-3 lists and describes the different isolations supported by ADO.NET. The first four levels are listed in order of increasing isolation.

Table 16-3. IsolationLevelEnumeration
Name	Description
ReadUncommitted	No shared locks are issued, and exclusive locks aren't honored. A dirty read is possible.
ReadCommitted	Shared locks are held while data is read by the transaction. Dirty reads aren't possible, but nonrepeatable reads or phantom rows can occur because data can be changed before it is committed.
RepeatableRead	Shared locks are placed on all data used in a query preventing other users from updating the data. Nonrepeatable reads are prevented, but phantom reads are still possible.
Serializable	A range lock, where the individual records and the ranges between records are covered, is placed on the data preventing other users from updating or inserting rows until the transaction is complete. Phantom reads are prevented.
Chaos	Pending changes from more highly isolated transactions can't be overwritten. Not supported by SQL Server.
`Unspecified`	A different isolation level than the one specified is being used, but that level can't be determined.

The isolation level can be changed programmatically at any time. If it is changed within a transaction, the new locking level applies to all remaining statements within the transaction.

The following example demonstrates how to set the isolation level for a transaction:

String connString = "Data Source=(local);Integrated security=SSPI;" + 
    "Initial Catalog=Northwind;";

SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlTransaction tran =
    conn.BeginTransaction(IsolationLevel.RepeatableRead);

// returns IsolationLevel.RepeatableRead
IsolationLevel il = tran.IsolationLevel;