Transaction isolation levels in PostgreSQL

Introduction

Transaction isolation levels control how changes made by one user are seen by others. They help keep data correct when many people use the database at the same time.

When you want to prevent errors caused by multiple users changing data at once.

When you need to balance between data accuracy and system speed.

When you want to avoid seeing incomplete or temporary data changes.

When you want to control how strict the database is about locking data.

When debugging issues related to data consistency in multi-user environments.

Syntax

PostgreSQL

SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | REPEATABLE READ | SERIALIZABLE };

You run this command at the start of a transaction to set how isolated it should be.

PostgreSQL defaults to READ COMMITTED if you don't set it.

Examples

This sets the transaction to READ COMMITTED, the default level where you see only committed data.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- your queries here
COMMIT;

This sets the transaction to SERIALIZABLE, the strictest level that avoids all concurrency problems.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- your queries here
COMMIT;

This level ensures that if you read data twice in the same transaction, it won't change.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- your queries here
COMMIT;

Sample Program

This example starts a transaction, sets the isolation level to REPEATABLE READ, then shows the current isolation level.

PostgreSQL

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT current_setting('transaction_isolation');
COMMIT;

Important Notes

Higher isolation levels reduce errors but can slow down the system because of more locking.

READ UNCOMMITTED is not supported in PostgreSQL; it behaves like READ COMMITTED.

Always set the isolation level at the start of a transaction.

Summary

Transaction isolation levels control how visible changes are between users.

PostgreSQL supports READ COMMITTED, REPEATABLE READ, and SERIALIZABLE levels.

Choose the level based on your need for accuracy versus speed.

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