Transaction Isolation Levels in SQL Server

Isolation level = how much one transaction is protected from changes made by OTHER transactions running at the same time.

* default

• Dirty read: reading data another transaction has NOT committed yet.
• Non-repeatable read: reading the same row twice gives different values.
• Phantom read: running the same WHERE twice returns NEW rows.

HOW TO PRACTICE: Open TWO query windows in SSMS (Window 1 and Window 2), run "USE SqlLearningDb" in both, then follow each demo step by step. Only PART A (setup) and PART G (check) are meant to be run as a whole.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Part A: Setup (run this part once)

DROP TABLE IF EXISTS dbo.FlightSeats;

CREATE TABLE dbo.FlightSeats
(
    SeatNo     VARCHAR(4)    PRIMARY KEY,
    FlightNo   VARCHAR(10)   NOT NULL,
    Class      VARCHAR(10)   NOT NULL,
    Price      DECIMAL(10,2) NOT NULL,
    IsBooked   BIT           NOT NULL DEFAULT 0,
    BookedBy   VARCHAR(50)   NULL
);

INSERT INTO dbo.FlightSeats (SeatNo, FlightNo, Class, Price, IsBooked, BookedBy) VALUES
('1A', 'AI101', 'Business', 25000, 1, 'Ravi'),
('1B', 'AI101', 'Business', 25000, 0, NULL),
('2A', 'AI101', 'Business', 25000, 0, NULL),
('10A','AI101', 'Economy',   6000, 1, 'Sita'),
('10B','AI101', 'Economy',   6000, 0, NULL),
('10C','AI101', 'Economy',   6000, 0, NULL),
('11A','AI101', 'Economy',   5500, 0, NULL),
('11B','AI101', 'Economy',   5500, 1, 'John');
GO

-- Enable snapshot-based isolation for this database (needed for PART F)
ALTER DATABASE SqlLearningDb SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Current isolation level of THIS session
SELECT CASE transaction_isolation_level
            WHEN 1 THEN 'READ UNCOMMITTED' WHEN 2 THEN 'READ COMMITTED'
            WHEN 3 THEN 'REPEATABLE READ'  WHEN 4 THEN 'SERIALIZABLE'
            WHEN 5 THEN 'SNAPSHOT' END AS MyIsolationLevel
FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
GO

(8 rows affected)

Part B: READ UNCOMMITTED -> Dirty Read

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1) : change price but do NOT commit
BEGIN TRAN;
    UPDATE dbo.FlightSeats SET Price = 1 WHERE SeatNo = '10B';

-- WINDOW 2 (step 2) : read with READ UNCOMMITTED -> sees Price = 1 (dirty!)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '10B';
-- same as:  SELECT ... FROM dbo.FlightSeats WITH (NOLOCK) WHERE SeatNo = '10B';

-- WINDOW 2 (step 3) : default level -> query WAITS (blocked) until window 1 finishes
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '10B';

-- WINDOW 1 (step 4) : undo -> the price "1" never really existed
ROLLBACK TRAN;

Part C: READ COMMITTED -> NON-REPEATABLE READ

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRAN;
    SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '10C';   -- 6000

-- WINDOW 2 (step 2) : not blocked, because window 1 released its shared lock
UPDATE dbo.FlightSeats SET Price = 7000 WHERE SeatNo = '10C';

-- WINDOW 1 (step 3) : same query, different answer!
    SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '10C';   -- 7000
COMMIT;

-- reset : UPDATE dbo.FlightSeats SET Price = 6000 WHERE SeatNo = '10C';

Part D: REPEATABLE READ -> prevents non-repeatable read, still Phantoms

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;
    SELECT * FROM dbo.FlightSeats WHERE Class = 'Economy' AND IsBooked = 0;   -- 3 rows

-- WINDOW 2 (step 2a) : update a row window 1 read -> BLOCKED (waits)
UPDATE dbo.FlightSeats SET Price = 9999 WHERE SeatNo = '10B';
-- (cancel it with the Stop button, then try step 2b)

-- WINDOW 2 (step 2b) : insert a NEW matching row -> NOT blocked
INSERT INTO dbo.FlightSeats (SeatNo, FlightNo, Class, Price) VALUES ('12A', 'AI101', 'Economy', 5000);

-- WINDOW 1 (step 3) : a new "phantom" row appears -> 4 rows
    SELECT * FROM dbo.FlightSeats WHERE Class = 'Economy' AND IsBooked = 0;
COMMIT;

-- reset : DELETE FROM dbo.FlightSeats WHERE SeatNo = '12A';

Part E: SERIALIZABLE -> no phantoms (key-range locks)

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;
    SELECT * FROM dbo.FlightSeats WHERE SeatNo BETWEEN '10A' AND '11Z';

-- WINDOW 2 (step 2) : insert inside the range -> BLOCKED until window 1 ends
INSERT INTO dbo.FlightSeats (SeatNo, FlightNo, Class, Price) VALUES ('10D', 'AI101', 'Economy', 6000);

-- WINDOW 1 (step 3)
COMMIT;      -- now window 2's insert finishes

-- reset : DELETE FROM dbo.FlightSeats WHERE SeatNo = '10D';

Real use : "check then insert" must be SERIALIZABLE (or UPDLOCK + HOLDLOCK)
so two users cannot book the same seat at the same time.

Part F: SNAPSHOT -> readers don't block writers, writers don't block readers

Run these steps in two query windows in SSMS (Window 1 and Window 2), one step at a time, to see how the sessions affect each other.

-- WINDOW 1 (step 1)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRAN;
    SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '11A';   -- 5500

-- WINDOW 2 (step 2) : update and commit (NOT blocked)
UPDATE dbo.FlightSeats SET Price = 8000 WHERE SeatNo = '11A';

-- WINDOW 1 (step 3) : still sees 5500 - the version from when its transaction started
    SELECT SeatNo, Price FROM dbo.FlightSeats WHERE SeatNo = '11A';

-- WINDOW 1 (step 4) : try to update the same row -> UPDATE CONFLICT error 3960
    UPDATE dbo.FlightSeats SET Price = 5600 WHERE SeatNo = '11A';
-- (transaction is rolled back automatically)

-- reset : UPDATE dbo.FlightSeats SET Price = 5500 WHERE SeatNo = '11A';

READ COMMITTED SNAPSHOT (RCSI) makes the DEFAULT level use row versions:
    ALTER DATABASE SqlLearningDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
(Azure SQL Database has RCSI ON by default.)

Part G: Single-window Queries (safe to run as a whole)

Real isolation demos need two sessions (parts B-F above).

These queries check the settings and show a safe booking pattern.

Database level settings

SELECT name,
       snapshot_isolation_state_desc       AS SnapshotIsolation,
       is_read_committed_snapshot_on       AS RCSI_On
FROM sys.databases WHERE name = 'SqlLearningDb';

Safe "book a seat" pattern for concurrent users (UPDLOCK + HOLDLOCK in one statement)

DECLARE @Seat VARCHAR(4) = '10B', @User VARCHAR(50) = 'Meena';

BEGIN TRAN;
    UPDATE dbo.FlightSeats WITH (UPDLOCK, HOLDLOCK)
    SET IsBooked = 1, BookedBy = @User
    WHERE SeatNo = @Seat AND IsBooked = 0;

    IF @@ROWCOUNT = 0
        PRINT 'Seat already taken - choose another seat';
    ELSE
        PRINT 'Seat booked for ' + @User;
ROLLBACK TRAN;   -- demo only
GO

(1 row affected)
Seat booked for Meena

Version store usage (snapshot / RCSI keep old row versions in tempdb)

SELECT DB_NAME(database_id) AS DatabaseName, reserved_page_count, reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;

-- Always reset your session to the default level when done
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

Practice Questions

1. Repeat PART B, but in window 2 use WITH (NOLOCK) instead of changing the isolation level.
2. Try PART C with REPEATABLE READ in window 1. What happens to window 2's UPDATE?
3. Turn on RCSI and repeat PART B step 3. Is the reader still blocked?
4. Which isolation level would you choose for: a) a dashboard b) seat booking c) end-of-day report?