TRANSACTION ISOLATION LEVEL

TRANSACTION ISOLATION LEVEL

·         Transaction Isolation level decides how is one process isolated from other process.

·         Using transaction levels you can implement locking in SQL SERVER.

·         There are four transaction levels in SQL SERVER.

·         You may only have one level set at a time.

READ COMMITTED (Default)	-       The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. -       Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.
READ UNCOMMITTED	-       No shared locks and no exclusive locks are honored. -       This is the least restrictive isolation level resulting in the best concurrency but the least data integrity.
REPEATABLE READ	-       This setting disallows dirty and non-repeatable reads. -       However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be read by the transaction.
SERIALIZABLE	-       This is the most restrictive setting holding shared locks on the range of data. -       This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

Syntax

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Example

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION Select Count(*) From Mytable INSERT INTO MYTABLE VALUES (……)

-       This example begins a transaction that makes a Select. -       Since the isolation level is set to 'Serializazble' and we did not commited the transaction, range locks will be held on the table's rows. -       Now, If you attempt to insert rows within the range of the same table, you will wait indefinitely. -       The little Query Analyzer globe will spin until the original transaction commits or rolls back

Hope this helps.           

Regards,

Arun Manglick