MSSQL - Difference Between Dirty Read and Phantom Read
Example Code Below for SQL Server, Dirty Read and Phantom 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
DELETE FROM SampleTable WHERE ID = 1
WAITFOR DELAY '00:0:10'
INSERT INTO SampleTable(Name, Value) VALUES('Name4', 'Value4')
WAITFOR DELAY '00:0:10'
UPDATE SampleTable SET Name = Name + Name WHERE ID = 2
COMMIT TRAN
BEGIN TRAN
DELETE FROM SampleTable WHERE ID = 1
COMMIT TRAN
BEGIN TRAN
INSERT INTO SampleTable(Name, Value) VALUES('Name4', 'Value4')
COMMIT TRAN
BEGIN TRAN
UPDATE SampleTable SET Name = Name + Name WHERE ID = 2
COMMIT TRAN
--=====================================
-- Windows/Session #2
--=====================================
---------------------------------------------------
-- This window/session is default READ COMMITTED --
---------------------------------------------------
SELECT @@SPID
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
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 READ UNCOMMITTED --
-----------------------------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT * FROM SampleTable
--WAITFOR DELAY '00:00:10'
SELECT * FROM SampleTable
--WAITFOR DELAY '00:00:10'
SELECT * FROM SampleTable
ROLLBACK