Read committed behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding Read Committed Isolation Level

Consider two transactions running concurrently under the Read Committed isolation level in PostgreSQL.

Transaction 1 updates a row but has not committed yet. Transaction 2 tries to read the same row.

What will Transaction 2 see?

PostgreSQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction 1 has not committed yet

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

A Transaction 2 sees a partial update causing inconsistent data.

B Transaction 2 sees the updated balance even though Transaction 1 has not committed.

C Transaction 2 is blocked until Transaction 1 commits.

D Transaction 2 sees the original balance before the update.

💡 Hint

Read Committed isolation level only shows committed data to other transactions.

Under Read Committed, a transaction sees only data committed before the query began. Since Transaction 1 has not committed, Transaction 2 reads the original data.

❓ query_result

intermediate

Effect of Commit on Read Committed Reads

Transaction 1 updates a row and commits immediately. Transaction 2 reads the same row twice under Read Committed isolation.

What can Transaction 2 observe?

PostgreSQL

BEGIN;
UPDATE products SET price = price + 10 WHERE id = 5;
COMMIT;

-- Transaction 2
BEGIN;
SELECT price FROM products WHERE id = 5;
-- some time passes
SELECT price FROM products WHERE id = 5;
COMMIT;

A Both SELECTs return the updated price after the commit.

B Both SELECTs return the same original price before the update.

C The first SELECT returns the original price; the second returns the updated price.

D The first SELECT returns the updated price; the second returns the original price.

💡 Hint

Each query in Read Committed sees the latest committed data at its start.

Since Transaction 1 committed before Transaction 2's queries, both SELECTs see the updated price.

📝 Syntax

advanced

Detecting Read Committed Behavior in SQL

Which SQL statement correctly sets the transaction isolation level to Read Committed in PostgreSQL?

A BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

B SET ISOLATION LEVEL READ COMMITTED;

C SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

D SET SESSION TRANSACTION READ COMMITTED;

💡 Hint

Check the exact syntax for setting isolation level per transaction.

The correct syntax is SET TRANSACTION ISOLATION LEVEL READ COMMITTED; to set it for the current transaction.

🧠 Conceptual

advanced

Why Read Committed Prevents Dirty Reads

Why does the Read Committed isolation level prevent dirty reads in PostgreSQL?

A Because it locks all rows for reading until the transaction commits.

B Because it only reads data that has been committed by other transactions at the time of the query.

C Because it caches data locally to avoid reading uncommitted changes.

D Because it uses snapshot isolation to provide a consistent view of data.

💡 Hint

Think about what data is visible to a query under Read Committed.

Read Committed reads only committed data at query start, so uncommitted changes (dirty data) are never visible.

🔧 Debug

expert

Analyzing Non-Repeatable Read under Read Committed

Transaction 1 updates a row and commits. Transaction 2 reads the same row twice under Read Committed isolation level.

Transaction 2 notices the two reads return different values. What is this phenomenon called?

A Non-repeatable read

B Phantom read

C Dirty read

D Lost update

💡 Hint

It happens when a row changes between two reads in the same transaction.

Non-repeatable read occurs when a transaction reads the same row twice and sees different data due to another committed update.