PostgreSQL uses Multi Version Concurrency Control (MVCC) to manage concurrent data access. It minimizes lock contention and improves performance compared to traditional locking. A key benefit is that reading data never blocks writing, and vice-versa.

Transaction Isolation

https://www.postgresql.org/docs/current/transaction-iso.html

All isolation mode avoids Dirty Read: Reading data written by a concurrent uncommitted transaction.

Set at the Session Level (DB connection)

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Set at the Transaction Level

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Other transaction queries here...
COMMIT;

Read Committed (Default)

A new snapshot is taken for each SQL statement.

ISSUE: Phantom Reads

If new rows are added/deleted by other transactions, subsequent queries can see those.

This anomaly falls under Serialization Anomalies.

Example

• Multiple Reads Calculation (Pagination)

<aside> 💡

Solutions

• Use Repeatable Read isolation mode for the transaction. Even thought other transactions add data, but this transaction remain consistent.
• ❌  If we only use share locks, they can prevent DELETE but we still can see new rows in later queries. </aside>

ISSUE: Non-Repeatable Reads (Read Skew)

When querying the same data multiple times, you might get different results due to other transactions modifying the data in between your reads.

This anomaly falls under Serialization Anomalies.

Example