The level at which a transaction is ready to accept inconsistent data is termed as isolation level. A transaction is a set of operations that must either succeed or fail as a unit. The goal of a transaction is to ensure that data is always in a valid, consistent state. To know more about transaction, please refer to my article "Understanding Transaction in ADO.NET." The isolation level is the degree to which one transaction must be isolated from other transaction. The concept of isolation level is closely related to the concept of locks. SQL server uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users and also prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect and quarries executed against that data might produce unexpected results. By determining the isolation level for a given transaction we can determine which types of locks are required.

Shared locks and exclusive locks are two basic locks, where read operations acquire shared locks and writes operations acquire exclusive locks. Shared locks allow concurrent transactions to read (SELECT) a resource. No other transaction can modify the data where shared locks exist on the data. Shared locks on a resource are released as soon as the data has been read. A shared lock will exist until all rows that satisfy the query have been returned to the client. Exclusive locks prevent access to a resource by concurrent transactions. No other transaction can read or modify data with an exclusive lock. SQL server uses exclusive (write) locks for the INSERT, UPDATE and DELETE data modification statements. Only one transaction can acquire an exclusive lock on a resource. A transaction can neither acquire a shared lock on a resource that has an exclusive lock nor acquire an exclusive lock on a resource until all shared locks are released.

In SQL server stored procedure, we can set the isolation level using the SET TRANSACTION ISOLATION LEVEL command. In Listing 1 it is described. In ADO.NET the isolation level can be set by creating the transaction using an overload of the BeginTransaction() method of the Command or by setting the Isolation Level property of an existing Transaction object. In Listing 2 it is described.

Listing 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
SELECT * FROM emp
SELECT * FROM dept 
...
COMMIT TRANSACTION

Listing 2

myTransaction =myConnection.BeginTransaction(IsolationLevel.ReadUncommitted);

In the above code listing we can also specify SERIALIZABLE, READ COMMITTED and READ UNCOMMITTED like REPEATABLE READ. Only one of the options can be set at a time and it remains set for that connection until we change it explicitly.

READ COMMITTED

It specifies that shared locks are held while the data is being read by the transaction. This avoids dirty reads, but the data can be changed before the end of the transaction. This may result in no repeatable reads or phantom data. A phantom read takes place when a transaction is allowed to read a row for the first read but is unable to modify the same row due to another transaction might be performing a delete on the row in the table. Hence, when a read is executed again within a transaction, the resultant output shows rows missing due to the delete. This is the default level.

READ UNCOMMITTED

 It specifies that statements can read rows that have been modified by other transaction, but still to be committed. It implements dirty read (in this scenario one process modifies data during a transaction and another process comes in and reads data before the transaction is committed) or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

It specifies that shared locks are placed on all data that is used in a query. This prevents other users from updating the data and it also prevents non-repeatable reads. In this scenario a transaction is able to read the same row multiple times and gets a different value each time. But another user can insert new phantom rows into the data set.  

SERIALIZABLE

It places a range lock on the data set, preventing other users from updating or inserting rows that could fall in that range into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Locking on a higher level than the row/record level - the table level does this. This is the only isolation level that removes the possibility of phantom rows.

SNAPSHOT

It stores a copy of the data transaction accesses. As a result, the transaction will not see the changes made by other transaction. This approach reduces blocking. Because even if other transactions are holding locks on the data, a transaction with snapshot isolation will still be able to read a copy of the data. This option is supported only in SQL Server 2005 and needs to be enabled through a database level option.

The isolation levels in the following table are arranged from the least degree of locking to the highest degree of locking. The default, ReadCommitted, is a good compromise for most transactions.

Transact-SQL Reference - MSDN

T-SQL Enhancements in SQL Server 2005

When locking is used as the concurrency control mechanism, it solves concurrency problems. This allows all transactions to run in complete isolation of one another, although there can be more than one transaction running in SQL server at any time. Transaction must be run at an isolation level of repeatable read or higher to prevent lost updates. Lost updates occur when there are two transactions modify the same data at the same time and the transaction that completes first is lost. Lost update can not occur at the default isolation level of read committed.