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.

Practice

1. What does the Read Committed isolation level guarantee in PostgreSQL?

easy

A. It prevents phantom reads by locking the entire table.

B. It allows reading uncommitted (dirty) data from other transactions.

C. It only reads data that has been committed by other transactions.

D. It locks all rows read until the transaction ends.

Solution

1. Step 1: Understand Read Committed isolation

   Read Committed isolation level ensures that a transaction sees only data committed before the query began, avoiding dirty reads.

2. Step 2: Compare options with definition

   It only reads data that has been committed by other transactions. matches this definition exactly. The other options describe behaviors of other isolation levels or incorrect behaviors.

3. Final Answer:

   It only reads data that has been committed by other transactions. -> Option C

4. Quick Check:

   Read Committed = no dirty reads [OK]

Hint: Read Committed means no dirty reads, only committed data [OK]

Common Mistakes:

• Confusing Read Committed with Read Uncommitted
• Thinking it locks rows until transaction ends
• Assuming it prevents phantom reads

2. Which of the following is the correct way to set the transaction isolation level to Read Committed in PostgreSQL?

easy

A. SET TRANSACTION LEVEL READ COMMITTED;

B. SET ISOLATION LEVEL READ COMMITTED TRANSACTION;

C. BEGIN TRANSACTION ISOLATION READ COMMITTED;

D. SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Solution

1. Step 1: Recall correct syntax for setting isolation level

   The correct syntax in PostgreSQL is: SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

2. Step 2: Check each option

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED; matches the correct syntax exactly. The other options have incorrect word order or missing keywords.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -> Option D

4. Quick Check:

   SET TRANSACTION ISOLATION LEVEL READ COMMITTED; [OK]

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

Common Mistakes:

• Mixing order of keywords
• Omitting 'ISOLATION' or 'LEVEL'
• Using BEGIN with isolation level incorrectly

3. Consider two transactions running concurrently under Read Committed isolation. Transaction 1 updates a row but has not committed yet. Transaction 2 tries to read that same row. What will Transaction 2 see?

medium

A. The original data before Transaction 1's update.

B. The updated but uncommitted data from Transaction 1.

C. An error due to concurrent update conflict.

D. No data, the row is locked and invisible.

Solution

1. Step 1: Understand Read Committed behavior on concurrent reads

   Under Read Committed, a transaction sees only committed data. Uncommitted changes from other transactions are invisible.

2. Step 2: Apply to scenario

   Transaction 1's update is uncommitted, so Transaction 2 reads the original committed data before the update.

3. Final Answer:

   The original data before Transaction 1's update. -> Option A

4. Quick Check:

   Read Committed hides uncommitted changes [OK]

Hint: Uncommitted changes are invisible under Read Committed [OK]

Common Mistakes:

• Assuming dirty reads are allowed
• Thinking a read error occurs
• Believing the row is locked and unreadable

4. You wrote this code in PostgreSQL:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;
COMMIT;

But you notice the SELECT shows the updated balance even before COMMIT. Why?

medium

A. Because the transaction reads its own uncommitted changes under Read Committed.

B. Because the isolation level is set to Read Uncommitted.

C. Because SELECT statements ignore transaction boundaries.

D. Because the UPDATE was not executed properly.

Solution

1. Step 1: Understand transaction visibility in Read Committed

   In Read Committed, a transaction sees its own changes immediately, even if not committed yet.

2. Step 2: Apply to given code

   The SELECT inside the same transaction sees the updated balance from the UPDATE before COMMIT.

3. Final Answer:

   Because the transaction reads its own uncommitted changes under Read Committed. -> Option A

4. Quick Check:

   Transaction sees own changes before commit [OK]

Hint: A transaction always sees its own changes immediately [OK]

Common Mistakes:

• Confusing Read Committed with Read Uncommitted
• Thinking SELECT ignores transaction boundaries
• Assuming UPDATE failed without checking

5. You want to avoid phantom reads in a banking app using PostgreSQL. You currently use Read Committed isolation. Which approach best prevents phantom reads while keeping most benefits of Read Committed?

hard

A. Switch to Repeatable Read isolation level for the transaction.

B. Use explicit row-level locks with SELECT FOR UPDATE.

C. Increase the transaction timeout to avoid conflicts.

D. Use Read Committed but commit after every statement.

Solution

1. Step 1: Understand phantom reads and Read Committed

   Read Committed does not prevent phantom reads (new rows appearing during a transaction).

2. Step 2: Evaluate options to prevent phantom reads

   Switching to Repeatable Read prevents phantom reads but changes the isolation level and may reduce concurrency. Increasing transaction timeout or committing after every statement does not prevent phantom reads. Using explicit row-level locks with SELECT FOR UPDATE keeps Read Committed while preventing changes to selected rows, mitigating phantom reads.

3. Final Answer:

   Use explicit row-level locks with SELECT FOR UPDATE. -> Option B

4. Quick Check:

   Row-level locks prevent phantom reads under Read Committed [OK]

Hint: Use SELECT FOR UPDATE to lock rows and avoid phantoms [OK]

Common Mistakes:

• Assuming Repeatable Read is always best
• Ignoring phantom reads in Read Committed
• Thinking timeout affects phantom reads