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.