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

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

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

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

Hint

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

Practice

1. Which transaction isolation level in PostgreSQL allows a transaction to see only committed data at the time each query starts, but can see different data if the same query is run again within the same transaction?

easy

A. SERIALIZABLE

B. REPEATABLE READ

C. READ COMMITTED

D. READ UNCOMMITTED

Solution

1. Step 1: Understand READ COMMITTED behavior

   READ COMMITTED shows only data committed before each query starts, so data can change between queries in the same transaction.

2. Step 2: Compare with other levels

   REPEATABLE READ and SERIALIZABLE keep a consistent snapshot for the whole transaction, so data does not change between queries.

3. Final Answer:

   READ COMMITTED -> Option C

4. Quick Check:

   READ COMMITTED = sees committed data per query [OK]

Hint: READ COMMITTED sees latest committed data per query [OK]

Common Mistakes:

• Confusing REPEATABLE READ with READ COMMITTED
• Thinking SERIALIZABLE allows data changes mid-transaction
• Assuming READ UNCOMMITTED exists in PostgreSQL

2. Which of the following is the correct SQL command to set the transaction isolation level to SERIALIZABLE in PostgreSQL?

easy

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B. SET ISOLATION LEVEL = SERIALIZABLE;

C. BEGIN TRANSACTION ISOLATION SERIALIZABLE;

D. SET TRANSACTION LEVEL SERIALIZABLE;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   The correct syntax is SET TRANSACTION ISOLATION LEVEL followed by the level name.

2. Step 2: Check each option

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches the correct syntax exactly. Others have incorrect keywords or missing parts.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option A

4. Quick Check:

   Correct SET TRANSACTION syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; [OK]

Hint: Use full phrase: SET TRANSACTION ISOLATION LEVEL [OK]

Common Mistakes:

• Omitting 'TRANSACTION' keyword
• Using '=' sign incorrectly
• Starting with BEGIN instead of SET

3. Consider two concurrent transactions in PostgreSQL using the REPEATABLE READ isolation level. Transaction A reads a row, then Transaction B updates and commits that row. What will Transaction A see if it reads the same row again before committing?

medium

A. The updated row from Transaction B

B. The original row before Transaction B's update

C. An error due to concurrent update

D. No row, because it is locked

Solution

1. Step 1: Understand REPEATABLE READ snapshot

   REPEATABLE READ provides a consistent snapshot for the whole transaction, so it sees data as it was at the start.

2. Step 2: Apply to scenario

   Transaction A will see the original row even after Transaction B commits an update, because its snapshot does not change.

3. Final Answer:

   The original row before Transaction B's update -> Option B

4. Quick Check:

   REPEATABLE READ = consistent snapshot [OK]

Hint: REPEATABLE READ shows data as of transaction start [OK]

Common Mistakes:

• Assuming it sees latest committed data mid-transaction
• Expecting an error or lock blocking read
• Confusing with READ COMMITTED behavior

4. You wrote this command to set the isolation level but get an error: SET TRANSACTION LEVEL = READ COMMITTED; What is the error and how to fix it?

medium

A. Syntax error: remove '=' and use 'ISOLATION' keyword

B. Wrong isolation level name, use 'READ COMMIT' instead

C. Missing semicolon at end of statement

D. SET TRANSACTION cannot be used inside a transaction

Solution

1. Step 1: Identify syntax error

   The command incorrectly uses '=' and omits 'ISOLATION' keyword.

2. Step 2: Correct syntax

   The correct command is SET TRANSACTION ISOLATION LEVEL READ COMMITTED; without '='.

3. Final Answer:

   Syntax error: remove '=' and use 'ISOLATION' keyword -> Option A

4. Quick Check:

   Correct syntax requires 'ISOLATION' and no '=' [OK]

Hint: No '=' sign; use 'ISOLATION' keyword in SET TRANSACTION [OK]

Common Mistakes:

• Using '=' sign in SET TRANSACTION
• Misspelling isolation level names
• Trying to set isolation level outside allowed scope

5. You want to ensure that two concurrent transactions in PostgreSQL never see inconsistent data and avoid phantom reads. Which isolation level should you choose and why?

hard

A. READ UNCOMMITTED, because it allows maximum concurrency

B. REPEATABLE READ, because it prevents non-repeatable reads but allows phantoms

C. READ COMMITTED, because it is fastest and avoids dirty reads

D. SERIALIZABLE, because it fully isolates transactions preventing phantoms

Solution

1. Step 1: Understand phantom reads and isolation levels

   Phantom reads occur when new rows appear in repeated queries within a transaction.

2. Step 2: Match isolation level to requirement

   SERIALIZABLE prevents phantom reads by fully isolating transactions, ensuring consistency.

3. Final Answer:

   SERIALIZABLE, because it fully isolates transactions preventing phantoms -> Option D

4. Quick Check:

   SERIALIZABLE = no phantoms, full isolation [OK]

Hint: Use SERIALIZABLE to prevent phantom reads fully [OK]

Common Mistakes:

• Choosing REPEATABLE READ and expecting no phantoms
• Thinking READ COMMITTED prevents phantoms
• Confusing READ UNCOMMITTED as safe option