Transaction isolation levels in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of READ COMMITTED isolation on concurrent transactions

Consider two concurrent transactions in PostgreSQL with the default READ COMMITTED isolation level. Transaction A updates a row but does not commit yet. Transaction B tries to read the same row.

What will Transaction B see when it reads the row before Transaction A commits?

PostgreSQL

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- Transaction A
-- Transaction A does not commit yet

-- Transaction B starts
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Transaction B reads
COMMIT;

A Transaction B sees the updated balance immediately.

B Transaction B gets an error due to concurrent update.

C Transaction B's read blocks until Transaction A commits.

D Transaction B sees the old balance before Transaction A's update.

💡 Hint

Think about how READ COMMITTED isolation works in PostgreSQL regarding visibility of uncommitted changes.

In READ COMMITTED isolation, each statement sees only data committed before it began. Since Transaction A has not committed, Transaction B sees the old balance.

🧠 Conceptual

intermediate

Understanding phantom reads in REPEATABLE READ isolation

Which of the following phenomena can still occur under the REPEATABLE READ isolation level in PostgreSQL?

A Phantom reads

B Non-repeatable reads

C Dirty reads

D Lost updates

💡 Hint

Recall the differences between isolation levels and what each prevents.

PostgreSQL's REPEATABLE READ prevents dirty and non-repeatable reads but does not prevent phantom reads, which are new rows matching a query's condition appearing in subsequent reads.

📝 Syntax

advanced

Setting SERIALIZABLE isolation level in PostgreSQL

Which of the following SQL commands correctly sets the transaction isolation level to SERIALIZABLE for the current session in PostgreSQL?

A SET ISOLATION LEVEL SERIALIZABLE;

B SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

C SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

D ALTER SESSION SET ISOLATION LEVEL SERIALIZABLE;

💡 Hint

Consider the correct syntax for setting isolation level for the session versus a single transaction.

To set the isolation level for the entire session, use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;. Option B sets it only for the next transaction.

❓ optimization

advanced

Avoiding serialization failures in SERIALIZABLE isolation

In PostgreSQL, when using SERIALIZABLE isolation, transactions may fail with serialization errors. Which approach helps reduce these failures?

A Use optimistic concurrency control by retrying failed transactions.

B Switch to READ UNCOMMITTED isolation level.

C Increase the statement timeout to avoid aborts.

D Use explicit locking with <code>FOR UPDATE</code> to serialize access.

💡 Hint

Think about how PostgreSQL handles conflicts under SERIALIZABLE isolation.

PostgreSQL uses optimistic concurrency control for SERIALIZABLE isolation. When serialization failures occur, the recommended approach is to retry the transaction.

🔧 Debug

expert

Diagnosing unexpected dirty reads in PostgreSQL

A developer reports that a transaction running under READ COMMITTED isolation level is seeing uncommitted changes from another transaction (dirty reads). Which is the most likely cause?

A The developer is querying a materialized view that is not refreshed.

B The transaction is using explicit locking that bypasses isolation.

C The database is using a non-standard isolation level like READ UNCOMMITTED.

D The developer is reading from a different database instance with replication lag.

💡 Hint

Recall PostgreSQL's default isolation levels and what it supports.

PostgreSQL does not support READ UNCOMMITTED; if dirty reads occur, it means the isolation level is set to a non-standard or unsupported level, or the client is connected to a different system.

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