DIFFERENT ISOLATION LEVELS AND ITS BEHAVIOUR.
- READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.
- READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.
- REPEATABLE READ is a more restrictive isolation level than READ COMMITTED. It encompasses READ COMMITTED and additionally specifies that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits. Concurrency is lower than for READ COMMITTED because shared locks on read data are held for the duration of the transaction instead of being released at the end of each statement.
- SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.
/*
This value will show you the old value if you are
trying to update a value with BEGIN TRAN and you are trying to see the result in other session.
*/
SET ALLOW_SNAPSHOT_ISOLATION ON
/*
While doing this ensure thatno other seession is opened on the database.Kill other database sessions
before you are trying to change the database isolation. This value will show you the old value if you are
trying to update a value with BEGIN TRAN and you are trying to see the result in other session.If this isolation
SET READ_COMMITED_SNAPSHOT ON you can use WITH(Tablock) table lock on the table. Until you update this table,you
can not update another value in the same table.READ COMMITTED means internally it si READ_COMMITTED_SNAPSHOT_ON/OFF.
*/
ALTER DATABASE Demo
SET READ_COMMITTED_SNAPSHOT ON --(/*2005 on wards*/)
BEGIN TRAN
UPDATE DemoData
WITH(Tablock)
SET Val3='Ramesh'
WHERE Id=10
/*
REPEATABLE READ
This will show you the old value till BEGIN TRAN completes with COMMIT OR rollback.
As per BOL:Sepcifies that statements can not read data that has been modified but not yet committed by other transactions
and that no other transaction can not modify data that has been read by the current transaction completes.
*/
USE Demo
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
/*
Statements can not read the data that has been modifed but not yet committed by other transaction
No other transaction can modify data that has been read by the current transactions until current transaction completes
Other transactions can not insert rows with key values that would fail in the range of keys
read by any statements in the current transaction untill current transaction completes
*/
USE Demo
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/*
Session 1
*/
BEGIN TRAN
UPDATE DemoData
WITH(Tablock)
SET Val3='Ramesh'
WHERE Id=10
/*
Session 2
This will not complete till first session completes on ISOLATION LEVEL SERIALIZABLE
*/
UPDATE DemoData
WITH(Tablock)
SET Val3='Madhu'
WHERE Id=10
Comments