Lv.2 BeginnerPostgreSQL

2026.05.2818 min readLv.2 Beginner

SeriesTransactions in PostgreSQL and MongoDB: What's the Difference? · Part 2View series hub

PostgreSQL Transactions Deep Dive: MVCC, Isolation Levels, Deadlocks, and VACUUM — Part 2

The secret to PostgreSQL's combination of high concurrency and strong consistency is MVCC. Row versions are managed through xmin and xmax, while three isolation levels — Read Committed, Repeatable Read, and Serializable — each provide different guarantees by controlling when a snapshot is taken. This post covers deadlock detection strategies, prevention principles, and VACUUM — the essential cleanup mechanism for Dead Tuples left behind by MVCC.

Series — Transactions in PostgreSQL and MongoDB: What's the Difference?

• Part 1 — Transaction Fundamentals and ACID Principles
• Part 2 — PostgreSQL Transactions Deep Dive (MVCC, Isolation Levels, Deadlocks, VACUUM) (current)
• Part 3 — MongoDB Transaction Evolution and Multi-Document Transactions
• Part 4 — Real-world Comparison — Performance, Scalability, and Which DB to Choose?

Table of Contents

1. Introduction — Why PostgreSQL Excels at Transactions
2. MVCC: The Engine Behind PostgreSQL Concurrency
   • 2-1. What Is MVCC?
   • 2-2. xmin / xmax — The Secret Behind Row Versioning
   • 2-3. How MVCC Works in Practice
3. Mastering Isolation Levels
   • 3-1. Read Committed (default)
   • 3-2. Repeatable Read
   • 3-3. Serializable (SSI)
4. Deadlocks: The Enemy of Transactions
   • 4-1. What Is a Deadlock?
   • 4-2. Real-world Deadlock Scenarios
   • 4-3. Deadlock Detection and Resolution Strategies
5. VACUUM: The Essential Janitor Created by MVCC
   • 5-1. What Are Dead Tuples?
   • 5-2. VACUUM vs AutoVacuum
   • 5-3. Transaction ID Wraparound
6. Transaction Patterns You'll Use in Production
7. Part 2 Summary
8. Practical application notes

1. Introduction — Why PostgreSQL Excels at Transactions

PostgreSQL has earned developer trust over decades, and at the center of that trust is a rock-solid transaction model.

This post goes beyond the ACID basics from Part 1 and digs into how PostgreSQL transactions actually work under the hood — MVCC mechanics, isolation level differences, deadlock scenarios you'll encounter in production, and VACUUM, the operational mechanism that comes with MVCC.

From the PostgreSQL official documentation: "PostgreSQL uses a multiversion model (MVCC) to maintain data consistency internally. Each SQL statement sees a snapshot of data as it was some time ago, regardless of the current state of the underlying data."

2. MVCC: The Engine Behind PostgreSQL Concurrency

2-1. What Is MVCC?

MVCC (Multi-Version Concurrency Control) is PostgreSQL's core mechanism for handling multiple concurrent transactions.

Traditional lock-based systems block reads while a transaction is writing. MVCC solves this in a fundamentally different way.

"Writers don't block readers, and readers don't block writers."

Each transaction sees a snapshot of the data as it existed when the transaction started. Even if another transaction modifies the data, the snapshot your transaction holds doesn't change.

2-2. xmin / xmax — The Secret Behind Row Versioning

PostgreSQL hides system columns in every row that are invisible in a normal SELECT *.

-- Inspect hidden system columns directly
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;

-- Sample output:
--  xmin  | xmax | ctid  | id | balance
-- -------+------+-------+----+---------
--  10023 |    0 | (0,1) |  1 | 100000

When xmax is 0, the row is still alive (a Live Tuple).

2-3. How MVCC Works in Practice

Here's what happens inside PostgreSQL when a single UPDATE is executed:

1. An existing row (xmin=100, xmax=0) is present

2. Transaction 200 executes UPDATE
   → Sets xmax = 200 on the existing row (logical deletion)
   → Inserts a new row (xmin=200, xmax=0)

3. Transaction 200 COMMITs
   → The new row becomes the official "current version"

4. A concurrent Transaction 150 that is already reading:
   → Continues reading the old version with xmin=100 (snapshot preserved)
   → Cannot see Transaction 200's changes

This temporary coexistence of an "old version" and a "new version" is the heart of MVCC. The old version — once no longer referenced by any transaction — becomes what PostgreSQL calls a Dead Tuple.

3. Mastering Isolation Levels

PostgreSQL supports three of the four SQL standard isolation levels in practice. READ UNCOMMITTED is accepted syntactically but behaves identically to READ COMMITTED internally.

-- Setting isolation level for a transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ... execute queries ...
COMMIT;

-- Changing the session-wide default
SET default_transaction_isolation = 'repeatable read';

3-1. Read Committed (default)

"Each SQL statement takes a fresh snapshot at the moment it executes."

The most commonly used isolation level. Guarantees that only committed data is ever read.

-- Session 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- result: 100000

-- (Session 2 commits balance = 90000 in between)

SELECT balance FROM accounts WHERE id = 1; -- result: 90000 (change is visible)
COMMIT;

Characteristics:

• Dirty Reads prevented (uncommitted data is never visible)
• Non-Repeatable Reads possible (the same query can return different results within one transaction)
• Phantom Reads possible
• Use case: most OLTP applications, services prioritizing write throughput

3-2. Repeatable Read

"The snapshot taken at transaction start is maintained until the transaction ends."

-- Session 1
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- result: 100000

-- (Session 2 commits balance = 90000 in between)

SELECT balance FROM accounts WHERE id = 1; -- result: 100000 (change NOT visible)
COMMIT;

Characteristics:

• Dirty Reads prevented
• Non-Repeatable Reads prevented
• Phantom Reads also prevented in PostgreSQL (unlike the SQL standard — a PostgreSQL strength)
• One of the two transactions may fail on write conflict
• Use case: report generation, long analytical queries, inventory and balance calculations

3-3. Serializable (SSI)

"Guarantees the same outcome as if all transactions were executed one by one in sequence."

PostgreSQL implements this not with simple locking but with SSI (Serializable Snapshot Isolation) — an advanced algorithm that provides serializability with minimal blocking.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... complex read/write operations ...
COMMIT;
-- On serialization conflict detection:
-- ERROR: could not serialize access due to concurrent update

Characteristics:

• Strongest data integrity guarantee
• Transactions that detect conflicts must be retried
• Use case: financial transactions, accounting systems, domains where correctness is non-negotiable

Summary comparison of the three isolation levels:

4. Deadlocks: The Enemy of Transactions

4-1. What Is a Deadlock?

A deadlock occurs when two or more transactions each hold a lock that the other needs, leaving both waiting indefinitely.

PostgreSQL automatically detects this situation and force-rolls back one of the two transactions to break the deadlock.

ERROR:  deadlock detected
DETAIL:  Process 101 waits for ShareLock on transaction 2002; blocked by process 102.
         Process 102 waits for ShareLock on transaction 2001; blocked by process 101.

4-2. Real-world Deadlock Scenarios

Pattern 1: Two transactions accessing tables in opposite order

-- Worker 1: updates A then B
BEGIN;
UPDATE accounts SET balance = balance - 100000 WHERE id = 'A'; -- acquires lock
UPDATE accounts SET balance = balance + 100000 WHERE id = 'B'; -- waiting...
COMMIT;

-- Worker 2: updates B then A (reversed!)
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE id = 'B';  -- acquires lock
UPDATE accounts SET balance = balance + 50000 WHERE id = 'A';  -- waiting...
COMMIT;
-- DEADLOCK!

Pattern 2: Overuse of SELECT FOR UPDATE

-- Session 1
BEGIN;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;    -- lock on row #10
SELECT * FROM inventory WHERE id = 5 FOR UPDATE;  -- waiting for row #5

-- Session 2
BEGIN;
SELECT * FROM inventory WHERE id = 5 FOR UPDATE;  -- lock on row #5
SELECT * FROM orders WHERE id = 10 FOR UPDATE;    -- waiting for row #10
-- DEADLOCK!

4-3. Deadlock Detection and Resolution Strategies

Deadlock monitoring query

-- Find processes currently waiting on locks
SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid    AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Four principles for deadlock prevention

Optimistic Locking — eliminating deadlocks at the root

-- Optimistic locking with a version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Read (no lock)
SELECT id, stock, version FROM products WHERE id = 42;

-- Update only when version matches
UPDATE products
SET stock = stock - 1,
    version = version + 1
WHERE id = 42 AND version = 7; -- version read earlier

-- If 0 rows affected → another transaction modified it first → retry

5. VACUUM: The Essential Janitor Created by MVCC

5-1. What Are Dead Tuples?

Recall how MVCC works. When UPDATE or DELETE runs, PostgreSQL doesn't physically erase the old row. It simply sets xmax to mark it as "logically deleted." Rows that remain on disk but are no longer referenced by any active transaction are called Dead Tuples.

[Table physical layout example]

Page 1:
  Tuple 1 (xmin=100, xmax=200)  <- Dead Tuple, no active references
  Tuple 2 (xmin=200, xmax=0)    <- Live Tuple (current version)
  Tuple 3 (xmin=150, xmax=300)  <- Dead Tuple
  Tuple 4 (xmin=300, xmax=0)    <- Live Tuple

As Dead Tuples accumulate:

• Disk space is wasted
• SELECT scans unnecessary Dead Tuples → performance degrades
• Indexes become bloated

5-2. VACUUM vs AutoVacuum

VACUUM reclaims Dead Tuple space and makes it available for reuse. It is a PostgreSQL-specific construct with no direct equivalent in Oracle, MySQL, or SQL Server.

-- Manual VACUUM (runs without a table lock)
VACUUM accounts;

-- VACUUM with statistics refresh
VACUUM ANALYZE accounts;

-- FULL VACUUM: returns space to OS (acquires table lock — use with caution in production)
VACUUM FULL accounts;

AutoVacuum runs this cleanup automatically in the background. It is enabled by default, but the default settings are conservative — high-traffic services must tune them.

# Key AutoVacuum settings in postgresql.conf
autovacuum = on                           # Enable AutoVacuum
autovacuum_vacuum_threshold = 50          # Minimum Dead Tuple count to trigger
autovacuum_vacuum_scale_factor = 0.2      # Trigger when 20% of the table is Dead Tuples
autovacuum_vacuum_cost_delay = 2ms        # Delay between work cycles (lower = faster)

Tables with very high write rates (logs, event tables) need table-level overrides to lower the threshold. Left untuned, Dead Tuples accumulate rapidly and query performance degrades sharply.

5-3. Transaction ID Wraparound

PostgreSQL's Transaction ID (XID) is a 32-bit integer — meaning around 4.2 billion transactions can be represented before the counter wraps around. When XID wraps, all existing rows appear to belong to "future" transactions, making the entire database invisible. This is the Transaction ID Wraparound problem.

-- Check current XID Wraparound risk
SELECT
    schemaname,
    tablename,
    age(relfrozenxid) AS xid_age,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_stat_user_tables
ORDER BY xid_age DESC
LIMIT 10;

-- age exceeding 200 million: warning; approaching 1 billion: emergency

VACUUM's FREEZE operation converts aged XIDs into a special value (FrozenXID = 2), effectively removing them from the age calculation and preventing wraparound.

6. Transaction Patterns You'll Use in Production

Pattern 1: SAVEPOINT — Partial Rollback

BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50000);

  SAVEPOINT before_payment;  -- intermediate checkpoint

  INSERT INTO payments (order_id, method) VALUES (100, 'card');

  -- Payment fails: roll back payment only, keep the order
  ROLLBACK TO SAVEPOINT before_payment;

  INSERT INTO payments (order_id, method) VALUES (100, 'bank_transfer');

COMMIT;

Pattern 2: FOR UPDATE SKIP LOCKED — Queue Processing

-- Multiple workers processing a job queue concurrently
-- Rows already locked by another worker are skipped
BEGIN;
SELECT *
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- After processing the job
UPDATE job_queue SET status = 'done' WHERE id = :job_id;
COMMIT;

Pattern 3: Repeatable Read for Consistent Reports

-- Report generation: consistent results even if data changes during the long query
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT SUM(amount) FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- Both queries execute against the same snapshot

COMMIT;

7. Part 2 Summary

This part examined PostgreSQL transaction internals in depth.

Key takeaways:

• MVCC: The foundation of PostgreSQL concurrency — readers and writers don't block each other. Each transaction holds its own data snapshot.
• xmin/xmax: Row-level transaction IDs that implement version management.
• Isolation levels: Read Committed (default) → Repeatable Read → Serializable — each level increases consistency and introduces performance trade-offs.
• Deadlock: Prevent with consistent access order, short transactions, and application-level retry logic.
• VACUUM: PostgreSQL's own mechanism for cleaning up Dead Tuples, the structural side effect of MVCC. AutoVacuum tuning is non-optional.

Part 3 covers the history and evolution of MongoDB transactions — why "MongoDB doesn't support transactions" is outdated, how multi-document transactions work, and how they compare to PostgreSQL's MVCC model.

References

• PostgreSQL Documentation: MVCC Introduction
• PostgreSQL Documentation: Concurrency Control
• Medium: The Core of PostgreSQL — MVCC
• Leapcell: Diving Deep into MVCC in PostgreSQL
• Netdata: 10 Real-World PostgreSQL Deadlock Examples
• Woowa Brothers Tech Blog: Almost Everything About PostgreSQL Vacuum
• CYBERTEC: Debugging Deadlocks in PostgreSQL