Isolation Level

At the beginning of this chapter, you saw the ACID properties used to describe transactions. The letter I (Isolation) of ACID is not always fully required. For performance reasons, you might reduce isolation requirements, but you must be aware of the issues you encounter if you change the isolation level.

The problems you can encounter if you don’t completely isolate the scope outside the transaction can be divided into three categories:

• Dirty reads - With a dirty read, another transaction can read records that are changed within the transaction. Because the data that is changed within the transaction might roll back to its original state, reading this intermediate state from another transaction is considered “dirty” - the data has not been committed. This can be avoided by locking the records to be changed.

• Nonrepeatable reads - Nonrepeatable reads occur when data is read inside a transaction, and while the transaction is running another transaction changes the same records. If the record is read once more inside the transaction, the result is different - nonrepeatable. This can be avoided by locking the read records.

• Phantom reads - Phantom reads happen when a range of data is read; for example, with a WHERE clause. Another transaction can add a new record that belongs to the range that is read within the transaction. A new read with the same WHERE clause returns a different number of rows. Phantom reads can be a specific problem when doing an UPDATE of a range of rows. For example, UPDATE Addresses SET Zip=4711 WHERE (Zip=2315) updates the zip code of all records from 2315 to 4711. After doing the update, there may still be records with a zip code of 2315 if another user added a new record with zip 2315, while the update was running. This issue can be avoided by doing a range lock.

When defining the isolation requirements, you can set the isolation level. The isolation level is set with an IsolationLevel enumeration that is configured when the transaction is created (either with the constructor of the CommittableTransaction class or with the constructor of the TransactionScope class). The IsolationLevel defines the locking behavior. The next table lists the values of the IsolationLevel enumeration.

Open table as spreadsheet

IsolationLevel	Description
ReadUncommitted	With ReadUncommitted transactions are not isolated from each other. With this level, there’s no wait for locked records from other transactions. This way uncommitted data can be read from other transactions - dirty reads. This level is usually used just for reading records where it doesn’t matter if you read interim changes; for example reports.
ReadCommitted	ReadCommitted waits for records with a write-lock from other transactions. This way a dirty read cannot happen. This level sets a read lock for the current record read and a write lock for the records being written until the transaction is completed. Because with the records read each record is unlocked when moving to the next record, nonrepeatable reads can happen.
RepeatableRead	RepeatableRead holds the lock for the records read until the transaction is completed. This way the problem of nonrepeatable reads is avoided. Phantom reads can still occur.
Serializable	Serializable holds a range lock. While the transaction is running, it is not possible to add a new record that belongs to the same range from which the data is being read.
Snapshot	The isolation level Snapshot is only possible with SQL Server 2005. This level reduces the locks as modified rows are copied. This way, other transactions can still read the old data without the need to wait for an unlock.
Unspecified	The level Unspecified indicates that the provider is using a different isolation level value.
Chaos	The level Chaos is similar to ReadUncommitted, but in addition to performing the actions of the ReadUncommitted value, Chaos doesn’t lock updated records.

The next table gives you a summary of the problems that can occur as a result of setting the most commonly used transaction isolation levels.

Open table as spreadsheet

Isolation Level	Dirty Reads	Nonrepeatable Reads	Phantom Reads
Read Uncommitted	Y	Y	Y
Read Committed	N	Y	Y
Repeatable Read	N	N	Y
Serializable	N	N	N

The following code segment shows how the isolation level can be set with the TransactionScope class. With the constructor of TransactionScope, you can set the TransactionScopeOption that was discussed earlier and the TransactionOptions. The TransactionOptions class allows you to define the IsolationLevel and the Timeout.

  TransactionOptions options = new TransactionOptions(); options.IsolationLevel = IsolationLevel.ReadUncommitted; options.Timeout = TimeSpan.FromSeconds(90); using (TransactionScope scope =       new TransactionScope(TransactionScopeOption.Required,       options)) {    // Read data without waiting for locks from other transactions,    // dirty reads are possible. }