January 13, 2014 at 1:27 pm
We recently changed from Read Committed Isolation to Read Committed Snapshot isolation. One nice thing about Read Committed could put do all your data changes in a transaction and just put a breakpoint on your "Commit tran" and then use SSMS along with (nolock) to look at your changed data. When you were done just simply stop/rollback your transaction and you could reproduce you last run without having to re-setup everything again. Is there anyway to do this with Read Committed Snapshot Isolation. I was hoping that (nolock) would still work but it makes sense it doesn't since i think the data is probably somewhere in tempdb/versionstore but its there an relatively easy way to query this (uncommited) data in the snapshot?
thanks...
January 13, 2014 at 10:46 pm
nolock seems to work fine, can you give an example?
----------- Read Committed Snapshot Isolation -----------------
CREATE DATABASE [test]
GO
USE [test]
CREATE TABLE dbo.aaa (id int)
GO
INSERT INTO dbo.aaa
VALUES (1)
GO
ALTER DATABASE test SET read_committed_snapshot ON
GO
SELECT snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'Test'
BEGIN TRAN
UPDATE dbo.aaa
SET id = id + 1
------------- start a new session window (2) --------------------
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT *
FROM test.dbo.aaa
-- Blocked if UPDATE statement started in Read Committed Isolation
-- Results = 1 if UPDATE started in RCSI
SELECT *
FROM test.dbo.aaa WITH (nolock)
-- Results = 2
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT *
FROM test.dbo.aaa
-- Results = 1 (if snapshot isolation is allowed, otherwise error)
SELECT *
FROM test.dbo.aaa WITH (nolock)
-- Results = 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT *
FROM test.dbo.aaa
-- Results = 2
SELECT *
FROM test.dbo.aaa WITH (nolock)
-- Results = 2
------------ complete session (2) then try Snapshot Isolation in session (1) --------------
ROLLBACK
GO
ALTER DATABASE test SET read_committed_snapshot OFF
GO
ALTER DATABASE test SET allow_snapshot_isolation ON
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases WHERE name = 'Test'
BEGIN TRAN
UPDATE dbo.aaa
SET id = id + 1
------------ retry session (2) then in session (1) --------------
ROLLBACK
GO
USE [master]
DROP DATABASE [test]
January 13, 2014 at 11:45 pm
Use the nolock hint or read uncommitted isolation level (same thing). Any queries running under that isolation level read the dirty (uncommitted) data
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2014 at 7:24 am
Thanks guys for the clarification. I got confused with another problem that was happening. You are in fact correct (i tested it). NOLOCK/READ Uncommited will still work with snapshot isolation and will read dirty data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply