December 9, 2013 at 2:34 am
from http://technet.microsoft.com/en-us/library/ms173763.aspx, it does not have much examples to explain the concepts correctly.
from
I can understand the difference BETWEEN READ COMMITTED ISOLATION using lock (the default setting) and READ COMMITTED ISOLATION using snapshot,
when I modified the script to investigate the difference between READ_COMMITTED_SNAPSHOT AND SNAPSHOT_ISOLATION
frankly speaking I still could not differentiate between READ COMMITTED SNAPSHOT AND SNAPSHOT ISOLATION
here's how I do it, please comment if I did it wrongly,
--initial session start
CREATE DATABASE IsoTest;
GO
ALTER DATABASE IsoTest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
USE IsoTest;
GO
CREATE TABLE dbo.marbles
(
id INT PRIMARY KEY ,
color CHAR(5)
);
GO
INSERT dbo.marbles VALUES ( 1, 'Black' );
INSERT dbo.marbles VALUES ( 2, 'White' );
GO
--initial session end
in session 1
--session 1 start
USE IsoTest;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DECLARE @id INT;
BEGIN TRAN
SELECT @id = MIN(id)
FROM dbo.marbles
WHERE color = 'Black';
UPDATE dbo.marbles
SET color = 'White'
WHERE id = @id;
--session 1 end
in session 2
USE IsoTest;
GO
SET TRANSACTiON ISOLATION LEVEL SNAPSHOT
DECLARE @id INT;
BEGIN TRAN
SELECT @id = MIN(id)
FROM dbo.marbles
WHERE color = 'Black';
UPDATE dbo.marbles
SET color = 'Red'
WHERE id = @id;
COMMIT TRAN
GO
go back to session 1
--session 1 start
COMMIT TRAN
GO
--session 1 end
Now as in READ COMMITTED SNAPSHOT
the result in both session are the same i.e.
1> select * from dbo.marbles
2> go
id color
----------- -----
1 Red
2 White
(2 rows affected)
1>
Would appreciate if someone could enlighten me with a better example or point out what is wrong with my way of testing.
thanks a lot!
December 9, 2013 at 4:54 am
mssqlnoob (12/9/2013)
frankly speaking I still could not differentiate between READ COMMITTED SNAPSHOT AND SNAPSHOT ISOLATION
The main difference is that to use snapshot isolation the query must set the option
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
With READ COMMITTED SNAPSHOT the transaction only has to be using the default of
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Since it's the default it's not required to specify it 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply