Friday 6 August 2010

Isolation levels in SQL Server

Isolation defines how or when changes made by an operation become visible to other concurrent operations. In other words you can use Isolation levels to isolate a resource for a transaction and protect that resource from modification by other transactions.

Resources are protected through the use of locks. SQL Server decides what locks to take out (and when) by looking at the Isolation Level for the transaction. Lower levels of isolation will allow multiple access to a resource simultaneously but may introduce concurrency related problems (e.g. dirty-reads, data inaccuracy). Higher levels of isolation should eliminate concurrency problems but risk introducing blocking.

Serializable

All transactions occur in a completely isolated fashion.

Places a range lock on the data set preventing other users from updating or inserting rows into the data set until the transaction is complete.

This is the most restrictive of the four isolation levels.

Repeatable read

All data records read by a SELECT statement cannot be changed. The transaction acquires read locks on all retrieved data, but does not acquire range locks.

Locks are placed on all data that is used in a query preventing other users from updating the data. However, new ‘phantom’ rows can be inserted into the data set by another user and will be included in later reads in the current transaction.

Read committed

Records retrieved by a query are not prevented from modification. Non-repeatable reads may occur (data retrieved in a SELECT statement may be modified by some other transaction when it commits). Read locks are acquired on selected data but they are released immediately. Write locks are released at the end of the transaction.

Shared locks are held while the data is being read to avoid dirty reads but the data can be changed before the end of the transaction.

This is the default Isolation Level of SQL Server. It eliminates dirty-reads but other concurrency problems remain.

Read uncommitted

Dirty reads are allowed. One transaction may see uncommitted changes made another transaction.

It is possible to read uncommitted or dirty data but values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.