Sunday, October 17, 2010

Isolation Level

  • A degree to which one transaction is isolated from another transactions.
  • Lower the transaction, increases concurrency and decreases data correctness.
  • Total 5 type of Isolation Level
    • READ UNCOMMITTED
      • Lowest level 
      • does not issue SHARED LOCK - means other transactions can see the data 
      • are not blocked by EXCLUSIVE LOCK - means they can read uncommitted modifications, this is same as using NOLOCK
      • This level produces "dirty read"
    • READ COMMITTED
      • Default level in SQL Server
      • cannot read the data which has been modified but not committed
      • it prevents "dirty read"
      • it produces "non repeatable read"
    • REPEATABLE READ
      • contains features of READ COMMITTED+ cannot modify data that has been read by other transaction until it gets completed
    • SERIALIZABLE
      • strongest level
      • contains features of REPEATABLE READ + cannot insert data if a table is in use by some other transaction (read/update)
    • SNAPSHOT
      • introduced in 2005
  • here is the comparison of basic 4 types of isolation levels how it behaves differently for different DML Operation





Session 1
(Within Transaction)

Session 2



SELECT
UPDATE /
DELETE
INSERT
READ UNCOMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
Yes
No
Yes
INSERT
Yes
No
Yes






READ COMMITTED
SELECT
Yes
Yes
Yes
UPDATE / DELETE
No
No
Yes
INSERT
No
No
Yes






REPEATABLE READ
SELECT
Yes
No
Yes
UPDATE
No
No
Yes
INSERT
No
No
Yes






SERIALIZABLE
SELECT
Yes
No
No
UPDATE
No
No
No
INSERT
No
No
Yes



  • SET TRANSACTION LEVEL REPEATABLE READ. This t-sql command sets different transactional level
  • only 1 isolation level can be set at any point of time
  • Any isolation level can be switched to any isolation level during a transaction (exception: SNAPSHOT)
  • If isolation level is set inside stored procedure, then at the end of execution of SP, isolation level will be set to original what it was before execution of SP.
For more info about Locks


1 comment:

  1. Hi Nisarg,

    Can you add some details related to Database access using Enterprise Library ?

    Thanks

    ReplyDelete