Transaction Isolation in SQL

Create database DemoStart
GO
Use DemoStart
GO
Create Table TranTable
(Id int primary key, Name nchar(20),Address nVarchar(50))
GO

declare @id int=1
set nocount on
while @id<100
begin
insert into TranTable values
(@id ,'User '+cast(@id as char(3)),'city '+cast(@id as char(3)))

set @id=@id+2
end

-----------------------------------------------------------------------------------------------
--1) Dirty read
set transaction isolation level read uncommitted
GO

dbcc useroptions

Begin Tran Tran1

update TranTable set address='US' where id=1
--Before ending the transaction execute the following command in a second session
/*
dbcc useroptions
GO
set transaction isolation level read uncommitted
Go

begin tran tran2
select * from trantable where id=1
--AFTER ROLLBACK TRAN1 AGAIN EXECUTE SELECT COMMAND OF TRAN2 AND COMMIT IT

commit*/

rollback
GO

-------------------------------------------------------------------------------------------

--read committed solves it

-------------------------------------------------------------------------------------------
set transaction isolation level read committed
dbcc useroptions

-- NON Repeatable read

set transaction isolation level read committed
Go
dbcc useroptions
GO

begin tran tran3
select * from trantable where id=1
select XACT_STATE ()

--BEFORE COMMIT EXECUTE THE FOLLOWING UPDATE IN A DIFFERENT SESSION AND COMMIT IT
/*begin tran tran4
update TranTable set address='CANADA' where id=1
commit
*/

commit

-----------------------------------------------------------------------------------

--Repeatable read solves it

-----------------------------------------------------------------------------------

set transaction isolation level repeatable read
GO
dbcc useroptions

----------------------------------------------------------------------------------
--Phantom read

begin tran tran5
select * from trantable where id <50

select XACT_STATE ()
--EXECUTE THE FOLLOWING COMMAND IN A NEW SESSION
/*
set transaction isolation level  repeatable read
Go

begin tran tran6
insert into trantable values(20,'Ram','Delhi')
commit
*/
select * from trantable where id <50
commit

------------------------------------------------------------------------------
--serializable solves it


set transaction isolation level serializable
dbcc useroptions


Transaction Isolation in SQL Reviewed by Rupesh on 22:01 Rating: 5

No comments:

All Rights Reserved by Technology from Developers Eye © 2014 - 2015
Powered By Blogger, Designed by Aadics
Disclaimers:: The information provided within this blogsite is for general informational purposes only. While we try to keep the information up-to-date and correct, there are no representations or warranties, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the information, products, services, or related graphics contained in this blogsite for any purpose.The author does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause.

Contact Form

Name

Email *

Message *

Powered by Blogger.