Transaction isolation levels in SQL - Mini Project: Build & Apply

Understanding Transaction Isolation Levels in SQL

📖 Scenario: You are working as a junior database administrator for a small online bookstore. Your manager wants you to demonstrate how different transaction isolation levels affect data consistency when multiple users access the database simultaneously.

🎯 Goal: Build a simple SQL script that sets up a books table, inserts sample data, and shows how to set and use different transaction isolation levels to control concurrent access.

📋 What You'll Learn

Create a books table with columns id, title, and stock

Insert three specific book records into the books table

Declare a variable to hold the desired transaction isolation level

Write a SQL command to set the transaction isolation level using the declared variable

Write a simple transaction block that selects all books within the set isolation level

💡 Why This Matters

🌍 Real World

Transaction isolation levels are crucial in real-world databases to control how multiple users see and modify data at the same time, preventing errors like dirty reads or lost updates.

💼 Career

Understanding and configuring transaction isolation levels is a key skill for database administrators and backend developers to ensure data consistency and application reliability.

1

Create the books table and insert data

Write SQL commands to create a table called books with columns id (integer primary key), title (text), and stock (integer). Then insert these three rows exactly: (1, 'Learn SQL', 10), (2, 'Mastering Databases', 5), and (3, 'SQL for Beginners', 8).

SQL

-- Create the books table and insert three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Declare a variable for the transaction isolation level

Declare a variable called isolation_level and set it to the string 'READ COMMITTED' to represent the transaction isolation level.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 5),
(3, 'SQL for Beginners', 8);

-- Declare the isolation_level variable
-- Your code here

Need a hint?

Use DECLARE to create a variable and assign the string 'READ COMMITTED'.

3

Set the transaction isolation level using the variable

Write a SQL command to set the transaction isolation level using the variable @isolation_level. Use the syntax SET TRANSACTION ISOLATION LEVEL followed by the variable value.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 5),
(3, 'SQL for Beginners', 8);

DECLARE @isolation_level VARCHAR(20) = 'READ COMMITTED';

-- Set the transaction isolation level using @isolation_level
-- Your code here

Need a hint?

Use dynamic SQL execution with EXEC to set the isolation level using the variable.

4

Write a transaction block to select all books

Write a transaction block that begins with BEGIN TRANSACTION, selects all rows from the books table, and ends with COMMIT. This will run under the set transaction isolation level.

SQL

CREATE TABLE books (
    id INT PRIMARY KEY,
    title TEXT,
    stock INT
);

INSERT INTO books (id, title, stock) VALUES
(1, 'Learn SQL', 10),
(2, 'Mastering Databases', 5),
(3, 'SQL for Beginners', 8);

DECLARE @isolation_level VARCHAR(20) = 'READ COMMITTED';

EXEC('SET TRANSACTION ISOLATION LEVEL ' + @isolation_level);

-- Begin transaction, select all books, and commit
-- Your code here

Need a hint?

Use BEGIN TRANSACTION to start, then SELECT * FROM books, and finish with COMMIT.