Syntax : SET TRANSACTION ISOLATION LEVEL [ READ COMMITTED / READ UNCOMMITTED / REPEATABLE READ / SERIALIZABLE ]

Note : The isolation level will affect only transactions for current connection.

1.Read Committed ( Default )

a. It is a default isolation level.
b. Problems can be prevented by this isolation level is dirty reads.
c. It cannot problems like Non repeatable reads , phantoms and lost update.
d. The locks associated with the select statement are freed as soon as statement is complete.SQL Server does not wait for the end of the transaction.
e. In case of insert , update and delete queries , locks will be there till the end of transaction just in case rollback is required.

2.Read UnCommitted

a. It will give you highest performance in terms of speed.
b. This means do not set any locks.
c. With this we can possibly recieve all types of concurrency issues.
d. This means we do not block any parallel transactions.

3.Repeatable Read

a. It prevents dirty reads.
b. It prevents non repeatable reads.
c. It holds shared lock until end of the transaction. Due to this transactions can block users access to objects and hence productivity can be reduced.

3.Serializable

1. It prevents you from all forms of concurreny issues but lost update.
2. When you set the level to Serializable action queries like insert , update and delete to the table ot tables used by your transaction must not meet the where clause of the any statement in that transaction.
3. If any other user is going to so do something that you transaction is doing that it must wait till your transaction has been completed.
4. Update process works the same for multiple users as it would if all your user did one transaction at a time.Process things one after the other in a serial manner.

Note : My personal advice is do not change isolation level unless it is required for specific purpose.

Pawan Kumar
Pawankkmr@hotmail.com