Just Code‎ > ‎

TSQL - READ COMMITTED SNAPSHOT Demo

posted Sep 30, 2011, 1:04 AM by Peter Henell
Setup:
IF DB_ID('Snapshot_Demo') IS NULL
    CREATE DATABASE Snapshot_Demo
GO

USE Snapshot_Demo


ALTER DATABASE Snapshot_Demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Snapshot_Demo SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE Snapshot_Demo SET READ_COMMITTED_SNAPSHOT ON

ALTER 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 TRAN


UPDATE dbo.Transactions SET TransactionComment = 'Peter was here'
WHERE TransactionId = 4444


select * from dbo.Transactions where TransactionId = 4444


SELECT     
    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 value
select * from dbo.Transactions 
where TransactionId between 4440 and 4450

-- Getting blocked until other sessions have released their exclusive locks
select * from dbo.Transactions with(updlock, rowlock)
where TransactionId between 4440 and 4450


-- Ignoring the Snapshot, reading the uncommited data from the table
select * 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 2
update 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 2
update 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 blocked
select 
    '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 dirty
select 
    '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 blocked
select 
    'DEFAULT ISOLATION LEVEL', * 
from 
    dbo.Transactions (repeatableread)
where 
    Created = '2010-04-02 16:59:23.497'
option(maxdop 1)
Comments