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

Understanding Transaction Isolation Levels in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. You want to understand how different transaction isolation levels affect concurrent transactions to keep data consistent and avoid problems like dirty reads or lost updates.

🎯 Goal: Learn how to set and test different transaction isolation levels in PostgreSQL by creating transactions that read and update book stock counts.

📋 What You'll Learn

Create a table called books with columns id (integer primary key), title (text), and stock (integer).

Insert three books with specific stock values.

Set a transaction isolation level using SET TRANSACTION ISOLATION LEVEL.

Write a transaction that reads and updates the stock of a book.

💡 Why This Matters

🌍 Real World

Transaction isolation levels are used in real databases to control how multiple users can safely read and write data at the same time without causing errors or inconsistencies.

💼 Career

Understanding transaction isolation is important for database administrators and backend developers to ensure data integrity and performance in multi-user applications.

1

Create the books table and insert data

Create a table called books with columns id as primary key integer, title as text, and stock as integer. Then insert these three rows exactly: (1, 'Learn SQL', 10), (2, 'PostgreSQL Basics', 5), and (3, 'Advanced Databases', 3).

PostgreSQL

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

Need a hint?

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

2

Set the transaction isolation level

Write a SQL statement to set the transaction isolation level to READ COMMITTED for the current transaction using SET TRANSACTION ISOLATION LEVEL READ COMMITTED;.

PostgreSQL

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

INSERT INTO books (id, title, stock) VALUES
  (1, 'Learn SQL', 10),
  (2, 'PostgreSQL Basics', 5),
  (3, 'Advanced Databases', 3);

-- Set the transaction isolation level to READ COMMITTED
-- Your code here

Need a hint?

Use the exact command SET TRANSACTION ISOLATION LEVEL READ COMMITTED; to set the isolation level.

3

Start a transaction and read stock

Begin a transaction with BEGIN; and then select the stock value for the book with id = 1 from the books table using SELECT stock FROM books WHERE id = 1;.

PostgreSQL

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

INSERT INTO books (id, title, stock) VALUES
  (1, 'Learn SQL', 10),
  (2, 'PostgreSQL Basics', 5),
  (3, 'Advanced Databases', 3);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Begin transaction and select stock for book id 1
-- Your code here

Need a hint?

Use BEGIN; to start the transaction and a SELECT statement to read the stock.

4

Update stock and commit the transaction

Within the same transaction, update the stock of the book with id = 1 to 9 using UPDATE books SET stock = 9 WHERE id = 1; and then commit the transaction with COMMIT;.

PostgreSQL

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

INSERT INTO books (id, title, stock) VALUES
  (1, 'Learn SQL', 10),
  (2, 'PostgreSQL Basics', 5),
  (3, 'Advanced Databases', 3);

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT stock FROM books WHERE id = 1;

-- Update stock to 9 for book id 1 and commit
-- Your code here

Need a hint?

Use UPDATE to change the stock and COMMIT; to save the transaction.