Transaction Isolation Levels in SQL Server 2005

If the effects of your transactions are important to you, its important to understand what problems can arise from concurrency and what steps must be taken to avoid these consistency problems. Below I've listed the various Transaction Isolation Levels available to you in SQL Server 2005


Uncommitted Read
In Uncommitted Read isolation, all the dependency/consistency problems/behaviors except lost updates can occur. Queries will read uncommitted data, and both non-repeatable reads and phantoms are possible. Uncommitted read Is implemented by allowing read operations to not take any locks, since no locks are requested, it won’t be blocked by conflicting locks and acquired by other processes. Using uncommitted reads, you trade off strongly consistent data for high concurrency of the system.

Read Committed (locking / pessimistic)
Read committed isolation ensures that an operation never reads data that another application has changed has not yet committed. With read committed (locking) if another transaction is updating data and consequently has exclusive locks on data rows, your transaction must wait for those locks to be released before you can use that data (whether or not you are reading or writing that data). Also your transaction must put share locks on the data that will be visited, however these locks can be removed as soon as the data is read rather then waiting till the end of the transaction.

Read Committed (snapshot / optimistic)
Read committed (snapshot) also ensures that an operation never reads uncommitted data, but not by forcing other processes to wait: every time a row is updated the SQL server generates a version of the changed row with its previous committed values. The data being changed is still locked but other processes can see the previous versions of the data as it was before the update operation began.

Repeatable Read
This isolation level adds to the properties of committed read by ensuring that if a transaction revists data or a query is reissued the data will not have changed. The cost of this extra safeguard is that all shared locks in a transaction must be held until completion (either COMMIT or ROLLBACK) of the transaction.

Snapshot
Snapshot is a optimistic isolation level, like read commuted (snapshot) it allows processes to read older versions of committed data if the current version is locked, the difference between snapshot and read committed (snapshot) has to do with how old the older versions have to be. Although behaviors prevented by snapshot isolation are the same as those prevented by Serializable snapshot is not truly a Serializable isolation level. With snapshot isolation it is possible to have two transactions executing simultaneously that give us a result that is not possible in serial execution
Ex/

Tranasaction 1	Transaction 2
Use pubs declare @price money begin transaction	Use pubs declare @price money begin transaction
Select @price = price From titles Where title_id = 1	Select @price = price From titles Where title_id = 2
Update titles set price = @price Where title_id = 2	Update titles set price = @price Where title_id = 1
Commit Transaction	Commit Transaction

There is no serial execution for these 2 queries wher the 2 titles won’t end up with the same price

Serializable
The Serializable isolation level adds to the properties of repeatable read by ensuring that if a query is reissued, rows will not have been added in the interim, IE phantoms will not appear. Serializable is the strongest of the pessimistic isolation levels because it prevents all the possible undesirable behaviors. The cost of the added safeguard of preventing phantoms is similar to the repeatable read, all the shared locks in the transaction must be held until completion of the transaction. In addition, enforcing the Serializable isolation level requires that you not only lock data that has been read, but also lock data that does not exist. The Serializable name comes from the fact that running multiple Serializable transactions at the same time is the equivalent of running them one at a time (serially)