MSSQL – Understanding Isolation Level By Example (Repeatable Read)

Example SQL Statements below used in the video, you can Copy and Paste for Transaction Isolation Level of Serializable, Read Committed, Read Uncommitted, Repeatable Read

–=====================================
— Windows/Session #1
–=====================================
SELECT @@SPID

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ‘SampleTable’)
DROP TABLE SampleTable

CREATE TABLE [SampleTable]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Value] [varchar](100) NULL,
[DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED ([Id] ASC)
)

INSERT INTO SampleTable(Name, Value)
SELECT ‘Name1’, ‘Value1’
UNION ALL
SELECT ‘Name2’, ‘Value2’
UNION ALL
SELECT ‘Name3’, ‘Value3’

SELECT * FROM SampleTable
BEGIN TRAN
INSERT INTO SampleTable(Name, Value) VALUES(‘Name4’, ‘Value4’)
–UPDATE SampleTable SET Name = Name + Name
–UPDATE SampleTable SET Name = Name + Name WHERE Name = ‘Name1′
UPDATE SampleTable SET Name = Name + Name WHERE ID = 2
DELETE FROM SampleTable WHERE ID = 4
WAITFOR DELAY ’00:0:10′
COMMIT TRAN

–=====================================
— Windows/Session #2
–=====================================

—————————————————
— This window/session is default READ COMMITTED —
—————————————————
SELECT @@SPID

BEGIN TRAN
SELECT * FROM SampleTable
WAITFOR DELAY ’00:00:10′
SELECT * FROM SampleTable
WAITFOR DELAY ’00:00:10′
SELECT * FROM SampleTable
ROLLBACK

SELECT b.name, c.name, a.*
FROM sys.dm_tran_locks a
INNER JOIN sys.databases b ON a.resource_database_id = database_id
INNER JOIN sys.objects c ON a.resource_associated_entity_id = object_id

–=====================================
— Windows/Session #3
–=====================================

—————————————————–
— This window/session is default REPEATABLE READ —
—————————————————–
SELECT @@SPID

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM SampleTable
WAITFOR DELAY ’00:00:10′
SELECT * FROM SampleTable
WAITFOR DELAY ’00:00:10’
SELECT * FROM SampleTable
COMMIT TRAN