Transaction isolation levels in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of transaction isolation levels in a database?

Transaction isolation levels control how and when the changes made by one transaction become visible to other concurrent transactions, helping to prevent data inconsistencies.

beginner

Name the four standard transaction isolation levels defined by SQL.

The four standard isolation levels are: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

intermediate

What phenomenon does the Read Committed isolation level prevent?

Read Committed prevents dirty reads, meaning a transaction cannot read data that another uncommitted transaction has modified.

advanced

Explain the difference between Repeatable Read and Serializable isolation levels.

Repeatable Read ensures that if a transaction reads data twice, it sees the same data both times, preventing non-repeatable reads. Serializable is stricter; it makes transactions appear as if they run one after another, preventing all concurrency anomalies including phantom reads.

intermediate

What is a phantom read in the context of transaction isolation?

A phantom read happens when a transaction reads a set of rows matching a condition, but a subsequent read in the same transaction finds additional rows that were inserted by another committed transaction.

Which isolation level allows dirty reads?

A Repeatable Read

B Read Committed

C Read Uncommitted

D Serializable

Read Uncommitted is the lowest isolation level and allows dirty reads, meaning it can read uncommitted changes from other transactions.

Which isolation level prevents phantom reads in PostgreSQL?

A Read Committed

B Read Uncommitted

C Repeatable Read

D Serializable

Serializable isolation level prevents phantom reads by ensuring transactions behave as if executed sequentially.

In PostgreSQL, what is the default transaction isolation level?

A Read Committed

B Read Uncommitted

C Repeatable Read

D Serializable

PostgreSQL uses Read Committed as the default isolation level.

Which isolation level guarantees that repeated reads within the same transaction see the same data?

A Read Uncommitted

B Repeatable Read

C Serializable

D Read Committed

Repeatable Read ensures that if you read the same data twice in a transaction, it will not change.

What problem does the Serializable isolation level solve that Repeatable Read does not?

A Phantom reads

B Non-repeatable reads

C Dirty reads

D Lost updates

Serializable isolation prevents phantom reads, which can still occur under Repeatable Read.

Describe the four standard transaction isolation levels and what concurrency problems each prevents.

Think about how visible changes from other transactions affect your current transaction.

Explain why choosing the right transaction isolation level is important in database applications.

Consider real-life situations where multiple people access and change shared data.

Practice

1. Which transaction isolation level in PostgreSQL allows a transaction to see only committed data at the time each query starts, but can see different data if the same query is run again within the same transaction?

easy

A. SERIALIZABLE

B. REPEATABLE READ

C. READ COMMITTED

D. READ UNCOMMITTED

Solution

1. Step 1: Understand READ COMMITTED behavior

   READ COMMITTED shows only data committed before each query starts, so data can change between queries in the same transaction.

2. Step 2: Compare with other levels

   REPEATABLE READ and SERIALIZABLE keep a consistent snapshot for the whole transaction, so data does not change between queries.

3. Final Answer:

   READ COMMITTED -> Option C

4. Quick Check:

   READ COMMITTED = sees committed data per query [OK]

Hint: READ COMMITTED sees latest committed data per query [OK]

Common Mistakes:

• Confusing REPEATABLE READ with READ COMMITTED
• Thinking SERIALIZABLE allows data changes mid-transaction
• Assuming READ UNCOMMITTED exists in PostgreSQL

2. Which of the following is the correct SQL command to set the transaction isolation level to SERIALIZABLE in PostgreSQL?

easy

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

B. SET ISOLATION LEVEL = SERIALIZABLE;

C. BEGIN TRANSACTION ISOLATION SERIALIZABLE;

D. SET TRANSACTION LEVEL SERIALIZABLE;

Solution

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

   The correct syntax is SET TRANSACTION ISOLATION LEVEL followed by the level name.

2. Step 2: Check each option

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; matches the correct syntax exactly. Others have incorrect keywords or missing parts.

3. Final Answer:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -> Option A

4. Quick Check:

   Correct SET TRANSACTION syntax = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; [OK]

Hint: Use full phrase: SET TRANSACTION ISOLATION LEVEL [OK]

Common Mistakes:

• Omitting 'TRANSACTION' keyword
• Using '=' sign incorrectly
• Starting with BEGIN instead of SET

3. Consider two concurrent transactions in PostgreSQL using the REPEATABLE READ isolation level. Transaction A reads a row, then Transaction B updates and commits that row. What will Transaction A see if it reads the same row again before committing?

medium

A. The updated row from Transaction B

B. The original row before Transaction B's update

C. An error due to concurrent update

D. No row, because it is locked

Solution

1. Step 1: Understand REPEATABLE READ snapshot

   REPEATABLE READ provides a consistent snapshot for the whole transaction, so it sees data as it was at the start.

2. Step 2: Apply to scenario

   Transaction A will see the original row even after Transaction B commits an update, because its snapshot does not change.

3. Final Answer:

   The original row before Transaction B's update -> Option B

4. Quick Check:

   REPEATABLE READ = consistent snapshot [OK]

Hint: REPEATABLE READ shows data as of transaction start [OK]

Common Mistakes:

• Assuming it sees latest committed data mid-transaction
• Expecting an error or lock blocking read
• Confusing with READ COMMITTED behavior

4. You wrote this command to set the isolation level but get an error: SET TRANSACTION LEVEL = READ COMMITTED; What is the error and how to fix it?

medium

A. Syntax error: remove '=' and use 'ISOLATION' keyword

B. Wrong isolation level name, use 'READ COMMIT' instead

C. Missing semicolon at end of statement

D. SET TRANSACTION cannot be used inside a transaction

Solution

1. Step 1: Identify syntax error

   The command incorrectly uses '=' and omits 'ISOLATION' keyword.

2. Step 2: Correct syntax

   The correct command is SET TRANSACTION ISOLATION LEVEL READ COMMITTED; without '='.

3. Final Answer:

   Syntax error: remove '=' and use 'ISOLATION' keyword -> Option A

4. Quick Check:

   Correct syntax requires 'ISOLATION' and no '=' [OK]

Hint: No '=' sign; use 'ISOLATION' keyword in SET TRANSACTION [OK]

Common Mistakes:

• Using '=' sign in SET TRANSACTION
• Misspelling isolation level names
• Trying to set isolation level outside allowed scope

5. You want to ensure that two concurrent transactions in PostgreSQL never see inconsistent data and avoid phantom reads. Which isolation level should you choose and why?

hard

A. READ UNCOMMITTED, because it allows maximum concurrency

B. REPEATABLE READ, because it prevents non-repeatable reads but allows phantoms

C. READ COMMITTED, because it is fastest and avoids dirty reads

D. SERIALIZABLE, because it fully isolates transactions preventing phantoms

Solution

1. Step 1: Understand phantom reads and isolation levels

   Phantom reads occur when new rows appear in repeated queries within a transaction.

2. Step 2: Match isolation level to requirement

   SERIALIZABLE prevents phantom reads by fully isolating transactions, ensuring consistency.

3. Final Answer:

   SERIALIZABLE, because it fully isolates transactions preventing phantoms -> Option D

4. Quick Check:

   SERIALIZABLE = no phantoms, full isolation [OK]

Hint: Use SERIALIZABLE to prevent phantom reads fully [OK]

Common Mistakes:

• Choosing REPEATABLE READ and expecting no phantoms
• Thinking READ COMMITTED prevents phantoms
• Confusing READ UNCOMMITTED as safe option