Repeatable read behavior in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does Repeatable Read isolation level guarantee in PostgreSQL?

It guarantees that all reads within a transaction see a consistent snapshot of the database as of the start of the transaction, preventing non-repeatable reads but not phantom reads.

beginner

What is a non-repeatable read?

A non-repeatable read happens when a transaction reads the same row twice and gets different data because another transaction modified it in between.

intermediate

Does Repeatable Read prevent phantom reads in PostgreSQL?

No, PostgreSQL's Repeatable Read prevents non-repeatable reads but does not prevent phantom reads; phantom reads are prevented only at the Serializable isolation level.

intermediate

How does PostgreSQL implement Repeatable Read isolation internally?

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to provide a consistent snapshot of the database at the start of the transaction for Repeatable Read isolation.

intermediate

What happens if two transactions try to update the same row under Repeatable Read isolation?

One transaction will succeed, and the other will wait or fail with a serialization error to maintain consistency.

Which isolation level in PostgreSQL guarantees a consistent snapshot for all reads within a transaction?

A Read Uncommitted

B Read Committed

C Repeatable Read

D Serializable

Repeatable Read ensures all reads see the same snapshot taken at the start of the transaction.

Can phantom reads occur under Repeatable Read isolation in PostgreSQL?

A Only with Read Uncommitted

B Yes

C No

D Only with Serializable

Phantom reads can occur under Repeatable Read in PostgreSQL; they are prevented only at the Serializable isolation level.

What concurrency control method does PostgreSQL use to implement Repeatable Read?

A Multi-Version Concurrency Control (MVCC)

B Lock-based concurrency control

C Timestamp ordering

D Two-phase locking

PostgreSQL uses MVCC to provide consistent snapshots for transactions.

If a transaction reads a row twice under Repeatable Read, what will it see?

A The same data both times

B Different data if another transaction updated it

C An error

D Only the first read is allowed

Repeatable Read guarantees the same data is seen on repeated reads within the same transaction.

What happens if two transactions update the same row simultaneously under Repeatable Read?

A Both updates succeed without conflict

B The second update overwrites the first silently

C The database crashes

D One transaction waits or fails with a serialization error

To maintain consistency, PostgreSQL will block or abort one transaction to avoid conflicts.

Explain how Repeatable Read isolation level works in PostgreSQL and what read phenomena it prevents or allows.

Think about what data a transaction sees when it reads the same row multiple times.

Describe what happens when two transactions try to update the same row under Repeatable Read isolation in PostgreSQL.

Consider how PostgreSQL handles conflicts to keep data correct.