Database Simplified Headline Animator

Database Simplified

Thursday 28 July 2011

Read Committed ISOLATION LEVEL

In my last post about Read Phenomenon against each isolation level .

We saw Dirty, Repeatable & Phantom Reads occurrence. And these phenomenon exists in Read Uncommitted Isolation.

Before Starting Read Uncommitted Isolation level lets Understand functionality of default Isolation Level(i.e. READ COMMITTED)

As I explained in my last blog the default isolation level is Read Committed. You can check isolation level by running following query.


DBCC UserOptions

Go

image

How to change the isolation level ?

Following Setting can be used to set Isolation level.

SET TRANSACTION ISOLATION LEVEL  <READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE | SNAPSHOT>

GO

Note : Isolation Level Setting Scope is connection based, You cannot set Isolation level on Database Level. Once Connection is closed isolation level is lost.

ok lets see the effect of Default Isolation Level.

--Check the current isolation level (which must be READ COMMITTED)

DBCC UserOptions

Go

-- and as expected the isolation level is READ COMMITTED (because its default)

-- Create the following table.


If (Select Object_Id('CheckIsolationLevel')) is not null
Drop Table CheckIsolationLevel

Create Table CheckIsolationLevel(id int Identity,Name Varchar(20),EncodedDate DateTime)

Insert Into CheckIsolationLevel Values('Pankaj',GETDATE()),('Santosh',GETDATE()),('Hitesh',GETDATE())

Select * From CheckIsolationLevel
Go
Id Name EncodedDate
1 Pankaj 2011-07-28 20:50:53.247
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

The above table is a Heap Table (coming soon on MSSQL GUIDE) since there is not cluster index. let’s work on READ COMMITTED Isolation level with Heap Table.

 

CASE -1

CASE – 1 ( Does READ COMMITTED ISOLATION Prevents Dirty Read ?)

Query Window 1


Begin Transaction
Update CheckIsolationLevel Set Name='MSSQLGUIDE' Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued


 

Lets Open Another Connection

Query Window 2


Select * From CheckIsolationLevel Where Id=2

 

--So Query waits as the table is locked by pending transaction at Query Window 1. There is one question since query at window 1 is updating record with id=1 then why query at window 2 is locked while retrieving data for id=2. This is because table is HEAP TABLE and query at window 2 scans whole table to find record where id=2. AND query waits till pending transaction at query window 1 is committed or rolledback.

Note: But The Above Query At Window 2 will give result(will not wait) if you have cluster index on the table(called cluster table).

You can now commit the transaction at Query Window 1 and check the Query Window 2

This is how READ COMMITTED Isolation prevents Dirty Reads.

Conclusion of CASE-1 (“YES”, Read Committed Isolation Prevents Dirty Read.)

 

CASE -2

 

CASE – 2 (Does Read Committed Isolation Prevents Non-Repeatable Read ?)

Drop And Recreate Table and Data

Query Window 1

Begin Transaction

Select * From CheckIsolationLevel Where Id=1

--Note the transaction is still pending since no commit and rollback has been issued

 

Let’s  Go To Query Window 2

Query Window 2

Begin Transaction

  Update CheckIsolationLevel Set Name=’MSSQLGUIDE’ Where Id=1

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go Back to Query Window 1

Query Window 1

--Let’s Fire The Same Query Again

Select * From CheckIsolationLevel Where Id=1

--As Expected Data is Changed even executing identical query within a transaction ( This is forming a Non-Repeatable Read).

Conclusion Of CASE –2 ( “NO” , Read Committed Isolation Doesn’t Prevent Non-Repeatable Read)

 

CASE – 3

CASE – 3 (Does Read Committed Isolation Prevents Phantom Read ?)

Drop And Recreate Table and Data

Query Window 1


Begin Transaction

Select * From CheckIsolationLevel Where Id Between 2 and 5
--Note the transaction is still pending since no commit and rollback has been issued and Following Is Your Result

 


Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247

 

Let Go To Next Query Window 2

Query Window 2


Begin Transaction

Insert Into CheckIsolationLevel 
Values(‘Sandeep’,GetDate()),(‘Naren’,Getdate())

Commit Transaction

--Note The Transaction is Committed

 

Let’s Go To Query Window 1

Query Window 1

--Run the Same Query and See the Result

Select * From CheckIsolationLevel  Where Id Between 2 and 5

--Following result is Retrieved

Id Name EncodedDate
2 Santosh 2011-07-28 20:50:53.247
3 Hitesh 2011-07-28 20:50:53.247
4 Sandeep 2011-07-28 21:17:25.820
5 Naren 2011-07-28 21:17:25.820

 

As You Can See The Above result with 2 New Rows which were not there in First Result (Causing Phantom Read)

Conclusion Of CASE – 3 (“NO”,Read Committed Isolation Does Not Prevents Phantom Read)




We Will See These Read Phenomenon Against Uncommitted Isolation Level In My Next Blog.

Thanks You All

Tuesday 26 July 2011

Isolation level in SQL Server With Read Phenomenon

ISOLATION : I  From ACID Of Database, Means every transaction should be separated from each other until they are finished . (committed or rolled back)
SQL Server 2008 Has Following ISOLATION LEVELS.
  • Read UnCommitted
  • Read Committed   (Default Isolation Level In Sql Server)
  • Repeatable Read
  • Serializable
  • Snapshot (Since 2005)

Before Starting Isolation Levels I’ll Share Some Information About Dirty,Non Repeatable and Phantom Reads.


Dirty Read : As the Name Says Read, Means Reading of those data which has been modified by an in-flight transaction (The Transaction which has not been Committed or Rolled Back).
Check the Following Example For Dirty Read.
Table Name : Employee
EmployeeId EmployeeName
1 A Purvs
2 Samantha Blackett
3 Shane


Query Window 1
Begin Transaction

Update Employee Set EmployeeName=’Amanda Purvs’ Where EmployeeId=1
Note :  Transaction Is In-Flight (Not Committed Yet)

Query Window 2

Select * From Employee Where EmployeeId=1
Output Of Query Window 2 :
EmployeeId EmployeeName
1 Amanda Purvs


Now No One Can Guarantee The Correctness of Data User Looking at  Query Window 2
If Transaction At Query Window 1 is Committed The Result of Query Window 2 is Correct, But What If Transaction At Query Window 1 is Rolledback then Result returned by Query Window 2 Is Incorrect (Which Is a Form Of Dirty Read)

Non Repeatable Read : Non Repeatable Read occurs when a Transaction reads data twice with same condition Or Clause and gets different results. Let’s See following example to make Non Repeatable Read More Clear.
Table Name : BillDetails
BillId BillAmount
1 250
2 160
3 690
Query Window 1
Begin Transaction

Select Sum(BillAmount) As TotalAmount From BillDetails
--You Get output of Above Query : 1100 And Transaction is Still Pending Since No Commit and Rollback has been issued


Query Window 2
Begin Transaction
Update BillDetails Set BillAmount=560 Where BillId=2
Commit Transaction
--Note Transaction Has Been Committed

Query Window 1
Following Is Your Old Query Which You Wrote Before Modification
Begin Transaction
Select Sum(BillAmount) As TotalAmount From BillDetails
--You Get output of Above Query : 1100 And Transaction is Still Pending Since No Commit and Rollback has been issued
Let’s Fire The Same Query Again And See The Result Note
Select Sum(BillAmount) As TotalAmount From BillDetails

--Now Your result is : 1500 Which is different From Previous Result

So As You can see in Above Example User At Query Window 1 Gets Different Results Within One Transaction On Executing Same Query (This Phenomenon Is Known As Non Repeatable Read )

Phantom Read : This Phenomenon occurs When Two Identical Query (With same Condition or same Where clause)  is executed and returns different no of rows.

See The Following Example:

Table Name : Employee

EmployeeID EmployeeName Age
1 Sachin 12
2 Vikas 15
3 Sandeep 11
4 Ramesh 19

Query Window 1

Begin Transaction
   Select * From Employee Where Age Between 10 and 18

--Returns Following Output
EmployeeID EmployeeName Age
1 Sachin 12
2 Vikas 15
3 Sandeep 11


Query Window 2
Begin Transaction

Insert Into Employee(EmployeeId,EmployeeName,Age) 
Values(5,’Harinder’,16)

Commit Transaction

--Note Transaction Is Committed
Now Let’s Go Back To Query Window 1

Query Window 1

Your Old Query
Begin Transaction
   Select * From Employee Where Age Between 10 and 18
EmployeeID EmployeeName Age
1 Sachin 12
2 Vikas 15
3 Sandeep 11
The First Query Gave You Above Result, note that the transaction is still pending.
Now Let’s Fire The Same Query Again
Select * From Employee Where Age Between 10 and 18
EmployeeID EmployeeName Age
1 Sachin 12
2 Vikas 15
3 Sandeep 11
5 Harinder 16
As You can see the result returned by second query has one extra row. So that Extra Row Is Nothing but Phantom Read.
Now I Hope From the Above Example Dirty, Non Repeatable and Phantom Read Must Be Clear To You All.

See The Following Table To Get information About Read Phenomenon Against Each Isolation Level
Isolation Levels
Dirty Read
Non Repeatable Read
Phantom Read
Read UnCommited
  Yes
Yes
Yes
Read Committed
No
Yes
Yes
Repeatable Read
No
No
Yes
Serializable
No
No
No
Snapshot
No
No
No



I’ll Write About Isolation Levels In My Next Blog.