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
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:
No comments: