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.

Practice

1.

What does Serializable isolation level guarantee in PostgreSQL?

easy

A. Transactions behave as if executed one after another, preventing anomalies.

B. Transactions can see uncommitted changes from others.

C. Transactions do not lock any rows during execution.

D. Transactions always run faster than other isolation levels.

Solution

1. Step 1: Understand Serializable Isolation Concept

   Serializable isolation ensures transactions appear to run sequentially, avoiding conflicts and anomalies.

2. Step 2: Compare Other Options

   Options B and C describe lower isolation levels or incorrect behavior; D is false as serializable can be slower due to locking.

3. Final Answer:

   Transactions behave as if executed one after another, preventing anomalies. -> Option A

4. Quick Check:

   Serializable isolation = sequential transaction behavior [OK]

Hint: Serializable means transactions act one by one, no surprises [OK]

Common Mistakes:

• Confusing Serializable with Read Committed
• Thinking Serializable allows dirty reads
• Assuming Serializable is always fastest

2.

Which of the following is the correct way to set the transaction isolation level to Serializable in PostgreSQL?

BEGIN;
-- What goes here?
COMMIT;

easy

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B. SET ISOLATION LEVEL TRANSACTION SERIALIZABLE;

C. SET TRANSACTION LEVEL SERIALIZABLE ISOLATION;

D. SET SERIALIZABLE TRANSACTION ISOLATION LEVEL;

Solution

1. Step 1: Recall Correct Syntax for Setting Isolation Level

   The correct syntax is SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; before running queries in the transaction.

2. Step 2: Eliminate Incorrect Syntax Options

   Options B, C, and D have incorrect word order or missing keywords, causing syntax errors.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option A

4. Quick Check:

   Correct syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [OK]

Hint: Remember: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [OK]

Common Mistakes:

• Mixing word order in SET command
• Omitting 'TRANSACTION' keyword
• Using invalid keywords or order

3.

Consider two concurrent transactions running under Serializable isolation in PostgreSQL:

-- Transaction 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- waits here

-- Transaction 2
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction 1 continues
COMMIT;

What will happen when Transaction 1 tries to commit?

medium

A. Transaction 1 is rolled back due to serialization failure and must retry.

B. Transaction 1 blocks indefinitely waiting for Transaction 2.

C. Transaction 1 commits successfully without errors.

D. Transaction 1 commits but with dirty reads.

Solution

1. Step 1: Understand Serializable Isolation Behavior

   Under Serializable isolation, PostgreSQL uses SSI which allows non-conflicting concurrent transactions to commit successfully without blocking or failing.

2. Step 2: Analyze the Scenario

   Transaction 1 updates id=1, Transaction 2 updates id=2 (different rows). No read-write conflicts or serialization anomalies possible, so Transaction 1 commits successfully.

3. Final Answer:

   Transaction 1 commits successfully without errors. -> Option C

4. Quick Check:

   Non-conflicting updates in Serializable succeed [OK]

Hint: Serializable allows independent concurrent transactions [OK]

Common Mistakes:

• Thinking all concurrent updates cause serialization failure
• Assuming blocking like in stricter locking modes
• Believing dirty reads happen in Serializable

4.

Given this PostgreSQL transaction block:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE products SET stock = stock - 1 WHERE id = 10;
COMMIT;

After running this, you get an error: ERROR: could not serialize access due to concurrent update. What is the best way to fix this?

medium

A. Ignore the error and continue.

B. Remove the COMMIT statement.

C. Change isolation level to Read Uncommitted.

D. Retry the entire transaction from the beginning.

Solution

1. Step 1: Understand Serialization Failure Cause

   The error means a concurrent transaction caused a conflict; PostgreSQL aborts the transaction to maintain correctness.

2. Step 2: Apply Recommended Fix

   The correct fix is to retry the entire transaction, as the conflict may not happen again on retry.

3. Final Answer:

   Retry the entire transaction from the beginning. -> Option D

4. Quick Check:

   Serialization errors require transaction retry [OK]

Hint: On serialization error, retry transaction fully [OK]

Common Mistakes:

• Ignoring the error and proceeding
• Lowering isolation level unsafely
• Removing COMMIT causing open transactions

5.

You have a banking app using PostgreSQL with Serializable isolation. You want to transfer money between accounts safely. Which approach best handles serialization failures?

-- Pseudocode
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- debit from source account
-- credit to target account
COMMIT;

What is the best way to ensure the transfer completes reliably?

hard

A. Set isolation level to Read Committed to avoid errors.

B. Wrap the transaction in a retry loop that restarts on serialization failure.

C. Use explicit table locks to prevent conflicts.

D. Ignore serialization errors and log them only.

Solution

1. Step 1: Recognize Need for Reliable Transaction Completion

   Serializable isolation can cause serialization failures; to handle this, retrying the transaction is necessary.

2. Step 2: Evaluate Options for Handling Failures

   Lowering isolation risks data anomalies; explicit locks add complexity; ignoring errors risks data loss. Retrying is best practice.

3. Final Answer:

   Wrap the transaction in a retry loop that restarts on serialization failure. -> Option B

4. Quick Check:

   Retry loop ensures reliable serializable transactions [OK]

Hint: Use retry loops to handle serialization failures safely [OK]

Common Mistakes:

• Switching to lower isolation unsafely
• Relying on manual locks instead of retries
• Ignoring errors risking inconsistent data