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
–=====================================
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] 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 = 1
WAITFOR DELAY ’00:02:00’
ROLLBACK
–=====================================
— Windows/Session #2
–=====================================
—————————————————
— This window/session is default READ COMMITTED —
—————————————————
SELECT * FROM SampleTable
SELECT * FROM SampleTable WITH (NOLOCK)
SELECT * FROM SampleTable WHERE ID = 3
SELECT * FROM SampleTable WHERE Name = ‘Name2’
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 READ UNCOMMITTED —
—————————————————–
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM SampleTable
SELECT * FROM SampleTable
SELECT * FROM SampleTable WHERE ID = 2
SELECT * FROM SampleTable WHERE Name = ‘Name2’
SELECT * FROM SampleTable WITH (NOLOCK)
Comments are closed.