Isolation Level

You can set the isolation level in SQL Server using a resource setting. The *isolationLevel resource setting defines the four levels of isolation for transactions. This resource setting can have one of the following values (not case-sensitive):

•READUNCOMMITTED (level 0)

Prevents other transactions from changing data that has already been modified.

•READCOMMITTED (level 1)

Prevents dirty read: If a record is modified in a transaction, and a second transaction needs to read that record, this second transaction waits until the first transaction completes. This is the default value.

•REPEATABLEREAD (level 2)

Prevents non-repeatable reads. Non-repeatable reads occur when one transaction reads a record, and a second transaction modifies that record. If the second transaction commits its changes, the first transaction subsequently reads results that are different from the original read.

•SERIALIZABLE (level 3)

Prevents phantom records. Phantom records occur when one transaction reads a set of records satisfying a search condition, and the data is changed in a second transaction. If the first transaction reads within the same query conditions, it obtains a different set of records.