TSQL - READ COMMITTED SNAPSHOT Demo

Post date: Sep 30, 2011 8:04:13 AM

Setup:

IF DB_ID('Snapshot_Demo') IS NULL CREATE DATABASE Snapshot_Demo GOUSE Snapshot_Demo ALTER DATABASE Snapshot_Demo SET SINGLE_USER WITH ROLLBACK IMMEDIATEALTER DATABASE Snapshot_Demo SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE Snapshot_Demo SET READ_COMMITTED_SNAPSHOT ONALTER DATABASE Snapshot_Demo SET MULTI_USER IF OBJECT_ID('dbo.Transactions') IS NOT NULL DROP TABLE dbo.Transactions CREATE TABLE Transactions ( TransactionId BIGINT PRIMARY KEY IDENTITY(1, 1), Created DATETIME NOT NULL, TransactionTypeId INT NOT NULL, TransactionComment VARCHAR(8000)) ;WITH Nbrs_3 AS ( SELECT 1 AS n UNION SELECT 0 ), Nbrs_2 AS ( SELECT 1 AS n FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ), Nbrs_1 AS ( SELECT 1 AS n FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ), Nbrs_0 AS ( SELECT 1 AS n FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ), Nbrs AS ( SELECT 1 AS n FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 ) INSERT dbo.Transactions ( Created , TransactionTypeId , TransactionComment )SELECT o1.create_date , o1.object_id , o1.name FROM sys.objects o1, Nbrs

Example 1, run this in session 1:

BEGIN TRANUPDATE dbo.Transactions SET TransactionComment = 'Peter was here'WHERE TransactionId = 4444select * from dbo.Transactions where TransactionId = 4444SELECT resource_type , resource_description , request_mode , request_type , request_status FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND request_session_id = @@SPID --rollback --select * from dbo.Transactions where %%lockres%% = '(b0fa1b7fce1c) '

Example 1 run this in session 2:

-- Reading the snapshot valueselect * from dbo.Transactions where TransactionId between 4440 and 4450-- Getting blocked until other sessions have released their exclusive locksselect * from dbo.Transactions with(updlock, rowlock)where TransactionId between 4440 and 4450-- Ignoring the Snapshot, reading the uncommited data from the tableselect * from dbo.Transactions (readuncommitted)where TransactionId between 4440 and 4450-- When in a transaction and you want to have analytical correctness,-- will put require shared lock on all the rows read.select * from dbo.Transactions (repeatableread)where TransactionId between 4440 and 4450

Example 2 run this in session 1 (first find dates that you can use in your sample data):

---- Prepare the data before starting the query in session 2update dbo.Transactions set TransactionComment = 'peter was not here'where Created = '2010-04-02 16:59:23.497'-- Start this in Session 1 and then quickly start the other query in session 2update dbo.Transactions set TransactionComment = REPLICATE(NEWID(), 200)where Created = '2010-04-02 16:59:23.497'option (maxdop 1)

Example 2 run this in session 2 (first find dates that you can use in your sample data):

-- Show why it is called statement based snapshot isolation level-- Values are returned as they were when the statement began-- Values are read from the snapshot, and we are not blockedselect 'SNAPSHOT', * from dbo.Transactions where Created = '2010-04-02 16:59:23.497'option(maxdop 1)-- Values are read from the table even if they are dirtyselect 'DIRTY', * from dbo.Transactions (readuncommitted)where Created = '2010-04-02 16:59:23.497'option(maxdop 1)-- Reader is blocked until the update is completed-- "Equal" to the default TRANSACTION ISOLATION LEVEL in the sense that is will be blockedselect 'DEFAULT ISOLATION LEVEL', * from dbo.Transactions (repeatableread)where Created = '2010-04-02 16:59:23.497'option(maxdop 1)