Serializable isolation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Serializable Isolation on Concurrent Updates

Consider two transactions running concurrently under serializable isolation in PostgreSQL. Both try to update the same row in a table accounts with columns id and balance. What will happen if both transactions commit?

PostgreSQL

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Transaction 1 waits
COMMIT;

A Both transactions abort due to deadlock errors.

B Both transactions commit successfully, and the balance increases by 200.

C One transaction commits successfully; the other aborts with a serialization failure error.

D Both transactions commit, but the balance only increases by 100.

💡 Hint

Serializable isolation prevents concurrent transactions from producing anomalies by aborting one if conflicts occur.

Under serializable isolation, concurrent conflicting updates cause one transaction to abort with a serialization failure to maintain consistency.

🧠 Conceptual

intermediate

Understanding Serializable Isolation Guarantees

Which of the following best describes the guarantee provided by serializable isolation in PostgreSQL?

A Transactions only prevent dirty reads but allow non-repeatable reads and phantom reads.

B Transactions appear to execute in some sequential order, preventing anomalies like dirty reads and phantom reads.

C Transactions execute without any locking or blocking, improving concurrency at the cost of consistency.

D Transactions can see uncommitted changes from other transactions, allowing faster reads.

💡 Hint

Serializable isolation is the strictest standard isolation level.

Serializable isolation ensures transactions behave as if executed one after another, preventing dirty reads, non-repeatable reads, and phantom reads.

📝 Syntax

advanced

Setting Serializable Isolation Level in PostgreSQL

Which SQL command correctly sets the transaction isolation level to serializable for the current session in PostgreSQL?

A SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

C SET ISOLATION LEVEL SERIALIZABLE;

D ALTER SESSION SET ISOLATION LEVEL SERIALIZABLE;

💡 Hint

Consider the scope of the setting: session vs transaction.

To set the isolation level for the entire session, use SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;. The command SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; applies only to the next transaction.

❓ optimization

advanced

Avoiding Serialization Failures in High Contention Scenarios

You have a high-traffic PostgreSQL application using serializable isolation. Serialization failures are frequent, causing many transaction retries. Which approach can reduce serialization failures without sacrificing serializability?

A Disable autocommit mode to batch multiple statements in one transaction.

B Switch to read committed isolation level to avoid serialization failures.

C Increase the max_connections setting to allow more concurrent transactions.

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

💡 Hint

Explicit locking can help control concurrency conflicts.

Using SELECT FOR UPDATE locks rows explicitly, reducing conflicts and serialization failures while maintaining serializability.

🔧 Debug

expert

Diagnosing Serialization Failure in a Complex Transaction

Given the following two transactions running concurrently under serializable isolation, which statement best explains why Transaction 2 fails with a serialization error?

-- Transaction 1
BEGIN;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 5;
COMMIT;

-- Transaction 2
BEGIN;
SELECT quantity FROM inventory WHERE product_id = 5;
UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 5;
COMMIT;

A Transaction 2 reads a stale value and tries to update based on it, causing a serialization conflict.

B Transaction 2 deadlocks because it tries to update before reading the row.

C Transaction 2 fails because it uses SELECT without FOR UPDATE, which is not allowed in serializable isolation.

D Transaction 2 fails because the inventory table has no primary key defined.

💡 Hint

Think about how concurrent reads and writes interact under serializable isolation.

Transaction 2 reads the quantity before Transaction 1 commits its update. When Transaction 2 tries to update based on the old read, PostgreSQL detects a serialization anomaly and aborts Transaction 2.