Definition
- MVCC (Multi-Version Concurrency Control) – a database concurrency technique where each row can have multiple versions, each tagged with the transaction ID that created it.
- Snapshot isolation – each transaction sees the row versions that were committed before the transaction started; uncommitted and later-committed changes are invisible.
- Non-blocking reads – readers never acquire locks that block writers, and writers never block readers; both work on different versions of the same row.
- Dead tuples – old row versions that no active transaction needs; they accumulate and must be cleaned up.
- Vacuum (PostgreSQL) / Purge (InnoDB) – background processes that reclaim space from dead tuples to prevent table bloat.
- Write-write conflict – when two transactions update the same row under REPEATABLE_READ, the second transaction gets a serialization failure and must retry.
At the National Bank of India, when a teller updates Rajesh's balance from Rs.1,00,000 to Rs.95,000, the vault does not erase the old entry. Instead it writes a new line: "Rs.95,000 (by TX-42)." Other tellers reading the balance see the old line until TX-42 commits. Nobody waits for the vault: everyone reads their own snapshot. The janitor (autovacuum) comes later to erase the old lines nobody needs anymore.
Understanding the Concepts
What is MVCC?
MVCC stands for Multi-Version Concurrency Control. It is a technique where the database keeps more than one version of each row so that readers and writers do not block each other. When a transaction updates a row, the database does not overwrite the existing data. Instead, it creates a new version of the row tagged with the writing transaction's ID and keeps the old version for any reader whose transaction started before the update.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class ConcurrentAccessDemo {
private final AccountRepository accountRepo;
public ConcurrentAccessDemo(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
// TX A: reads balance, runs for 30 seconds (a long report)
@Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
public BigDecimal longRunningReport(String accountId) {
// Sees the snapshot from when TX A started
BigDecimal balance = accountRepo.findById(accountId).getBalance();
// ... 30 seconds of processing ...
// Balance is still the same snapshot value
return balance;
}
// TX B: modifies balance while TX A is running
@Transactional
public void deposit(String accountId, BigDecimal amount) {
Account account = accountRepo.findByIdForUpdate(accountId);
account.credit(amount);
accountRepo.save(account);
// Creates a new row version; TX A still sees the old version
}
}
TX A sees Rajesh's balance as Rs.1,00,000 (the snapshot), while TX B deposits Rs.5,000 and creates a new row version showing Rs.1,05,000. TX A never sees the Rs.1,05,000 version because it was not committed when TX A started.
Why does MVCC exist?
Before MVCC, databases used lock-based concurrency. A reader would acquire a shared lock on a row, and a writer would need an exclusive lock. If a reader held a shared lock, the writer had to wait. If a writer held an exclusive lock, all readers had to wait. In a banking system processing thousands of concurrent operations, this created bottlenecks.
MVCC eliminates this contention. Readers never block writers because they read from older row versions. Writers never block readers because they create new versions instead of modifying the ones readers are using. Both run concurrently on different versions of the same data.
-- Lock-based: reader blocks writer
-- TX A: SELECT balance FROM account WHERE id = 'SAV2001'; -- shared lock
-- TX B: UPDATE account SET balance = 95000 WHERE id = 'SAV2001'; -- WAITS
-- MVCC: reader does NOT block writer
-- TX A: SELECT balance FROM account WHERE id = 'SAV2001'; -- reads old version
-- TX B: UPDATE account SET balance = 95000 WHERE id = 'SAV2001'; -- creates new version immediately
How does MVCC implement snapshot isolation?
Each transaction gets a snapshot when it begins (or at first read, depending on the isolation level). The snapshot includes the list of transaction IDs that were committed at that point. When the transaction reads a row, the database looks at all versions of that row and returns the one whose creating transaction is in the committed set. Versions created by uncommitted or later transactions are invisible.
PostgreSQL implements this with two hidden columns on every row: xmin (the transaction ID that inserted this version) and xmax (the transaction ID that deleted or updated this version). When you read, PostgreSQL checks these columns against your snapshot to determine visibility.
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class SnapshotDemo {
private final AccountRepository accountRepo;
public SnapshotDemo(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
// REPEATABLE_READ: snapshot taken at first query
@Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
public void readConsistentData(String accountId) {
BigDecimal first = accountRepo.findById(accountId).getBalance();
// Simulating time passing; another TX commits a deposit
try { Thread.sleep(5000); } catch (InterruptedException e) {}
BigDecimal second = accountRepo.findById(accountId).getBalance();
System.out.println("First: " + first + ", Second: " + second);
// Both return the same value due to snapshot isolation
}
}
First: 100000, Second: 100000
Even though another transaction deposited money during the 5-second sleep, the snapshot ensures both reads return the same value.
What are dead tuples and version bloat?
When a row is updated or deleted under MVCC, the old version becomes a dead tuple. Dead tuples occupy disk space but are invisible to all active transactions. If many updates happen and dead tuples accumulate, the table grows (bloats), and sequential scans slow down because the database reads pages containing dead data.
PostgreSQL uses autovacuum to periodically scan tables and remove dead tuples that no active transaction needs. InnoDB uses purge threads that run in the background to clean up old row versions from the undo log.
-- PostgreSQL: check dead tuples on the account table
SELECT relname, n_dead_tup, n_live_tup,
round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'account';
relname | n_dead_tup | n_live_tup | dead_pct
---------+------------+------------+---------
account | 12500 | 50000 | 25.00
A dead_pct above 20% indicates autovacuum is falling behind and the table needs attention.
package com.bank.config;
// PostgreSQL autovacuum tuning in application.yml or postgresql.conf
// Increase autovacuum_vacuum_scale_factor for large tables
// Default: vacuum when 20% of rows are dead
// For a 50,000-row account table, that means vacuum at 10,000 dead rows
How does MVCC differ from lock-based concurrency?
| Aspect | Lock-Based Concurrency | MVCC |
|--------|------------------------|------|
| Readers block writers | Yes (shared lock) | No |
| Writers block readers | Yes (exclusive lock) | No |
| Writers block writers | Yes | Yes (same row) |
| Row versions | Single version | Multiple versions |
| Space overhead | None (one version) | Dead tuples / undo log |
| Cleanup | Not needed | Vacuum / purge |
| Throughput | Lower (lock contention) | Higher (no read locks) |
| Serialization failures | Deadlocks | Write-write conflicts |
package com.bank.service;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.math.BigDecimal;
@Service
public class LockingDemo {
private final AccountRepository accountRepo;
public LockingDemo(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
// SELECT FOR UPDATE acquires a row-level exclusive lock
// This is the one case where writers block other writers, even with MVCC
@Transactional
public void transfer(String fromId, String toId, BigDecimal amount) {
Account sender = accountRepo.findByIdForUpdate(fromId);
Account receiver = accountRepo.findByIdForUpdate(toId);
sender.debit(amount);
receiver.credit(amount);
accountRepo.save(sender);
accountRepo.save(receiver);
}
}
Even under MVCC, SELECT FOR UPDATE acquires an exclusive lock on the row. This is intentional: for a bank transfer, you need to prevent two concurrent transfers from debiting the same account simultaneously.
What are write-write conflicts under MVCC?
Under REPEATABLE_READ with MVCC, if two transactions try to update the same row, the first one to commit wins. The second transaction gets a serialization failure (PostgreSQL error code 40001) and must retry.
package com.bank.service;
import org.springframework.retry.annotation.Retryable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.dao.CannotAcquireLockException;
import java.math.BigDecimal;
@Service
public class RetryableDepositService {
private final AccountRepository accountRepo;
public RetryableDepositService(AccountRepository accountRepo) {
this.accountRepo = accountRepo;
}
// Retry on serialization failure
@Retryable(retryFor = CannotAcquireLockException.class, maxAttempts = 3)
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void deposit(String accountId, BigDecimal amount) {
Account account = accountRepo.findByIdForUpdate(accountId);
account.credit(amount);
accountRepo.save(account);
}
}
How do PostgreSQL and InnoDB implement MVCC differently?
| Aspect | PostgreSQL | InnoDB (MySQL) |
|--------|------------|----------------|
| Row versions stored | In the main table (heap) | In the undo log |
| Hidden columns | xmin, xmax per row | DB_TRX_ID, DB_ROLL_PTR per row |
| Cleanup process | autovacuum (vacuum worker) | Purge threads |
| Bloat location | Main table file | Undo tablespace |
| Default isolation | READ_COMMITTED | REPEATABLE_READ |
| Phantom prevention | MVCC (snapshot) under RR | Gap locks under RR |
-- PostgreSQL: see hidden MVCC columns
SELECT xmin, xmax, id, balance
FROM account
WHERE id = 'SAV2001';
xmin | xmax | id | balance
------+------+---------+--------
42100 | 0 | SAV2001 | 100000
xmin = 42100 means transaction 42100 created this version. xmax = 0 means no transaction has deleted or updated it yet.
What are common mistakes with MVCC?
Mistake 1: long-running transactions that prevent vacuum
// WRONG: 30-minute report holds a snapshot, preventing vacuum of old rows
@Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
public void slowReport() {
// Runs for 30 minutes, holding the oldest snapshot
// autovacuum cannot clean up any rows modified after this TX started
processAllAccounts();
}
// CORRECT: use short transactions with pagination
public void fastReport() {
int page = 0;
List<Account> batch;
do {
batch = accountRepo.findAll(PageRequest.of(page, 1000)).getContent();
processBatch(batch);
page++;
} while (!batch.isEmpty());
// Each page read is a short transaction; vacuum can clean between pages
}
Mistake 2: not retrying serialization failures
// WRONG: serialization failure crashes the request
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void deposit(String accountId, BigDecimal amount) {
Account account = accountRepo.findById(accountId).orElseThrow();
account.credit(amount);
// If another TX updated this row, commit throws serialization failure
}
// CORRECT: add retry logic
@Retryable(retryFor = CannotAcquireLockException.class, maxAttempts = 3)
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void deposit(String accountId, BigDecimal amount) {
Account account = accountRepo.findByIdForUpdate(accountId);
account.credit(amount);
}
What should a senior engineer know?
- MVCC is why a 30-second end-of-day report at National Bank of India does not freeze live banking traffic. In a lock-based system, the report's shared locks would block every writer for 30 seconds.
- The cost of MVCC is version bloat and the vacuum overhead. Monitor
pg_stat_user_tables.n_dead_tupand autovacuum activity in PostgreSQL. - In InnoDB, the undo log stores old versions. A long-running transaction prevents the purge of undo entries, causing the undo tablespace to grow.
- PostgreSQL's
REPEATABLE_READis technically Snapshot Isolation (SI), which is between REPEATABLE_READ and SERIALIZABLE in the SQL standard. It prevents phantom reads via snapshots, not gap locks. - Use
SELECT FOR UPDATEwith MVCC when you need mutual exclusion on a specific row (e.g., debiting an account). This combines MVCC's non-blocking reads with explicit write locks. - For Spring applications, combine
@Retryablewith@Transactional(isolation = REPEATABLE_READ)to handle serialization failures gracefully.
package com.bank.config;
import org.springframework.context.annotation.Configuration;
import org.springframework.retry.annotation.EnableRetry;
@Configuration
@EnableRetry
public class RetryConfig {
// Enables @Retryable for serialization failure handling
}
Visual Overview
MVCC: How Readers and Writers Coexist
───────────────────────────────────────
Row: account SAV2001
Version 1 (xmin=100, xmax=200) ← old, for readers with snapshot < 200
│ balance = 100,000
│
Version 2 (xmin=200, xmax=300) ← for readers with snapshot between 200-300
│ balance = 95,000
│
Version 3 (xmin=300, xmax=0) ← current, for readers with snapshot >= 300
balance = 105,000
TX A (snapshot at xid 250) → sees Version 2 (balance = 95,000)
TX B (snapshot at xid 350) → sees Version 3 (balance = 105,000)
TX C (write at xid 400) → creates Version 4 without blocking TX A or TX B
Autovacuum:
│ If no TX has snapshot < 200 → Version 1 is a dead tuple → remove it
Key Takeaway
MVCC is how PostgreSQL and InnoDB achieve high concurrency without readers blocking writers. Instead of using shared locks that make readers wait for writers, the database keeps multiple versions of each row. When a row is updated, a new version is created and tagged with the writing transaction's ID. Readers see a snapshot: the row versions that were committed before their transaction started. This means a 30-second end-of-day report at National Bank of India does not freeze live deposits and withdrawals.
The cost of MVCC is version bloat. Old row versions accumulate as dead tuples that must be cleaned up. PostgreSQL uses autovacuum to reclaim this space; InnoDB uses purge threads. If a long-running transaction holds an old snapshot, autovacuum cannot clean up any rows modified after that transaction started, and the table grows. This is why long-running transactions in production are dangerous: they hold back vacuum and cause bloat.
The other cost is write-write conflicts. Under REPEATABLE_READ, if two transactions update the same row, the second one gets a serialization failure and must retry. This is the trade-off: MVCC eliminates read-lock contention but introduces serialization failures that the application must handle, typically with Spring Retry's @Retryable annotation.
Remember one sentence: MVCC keeps multiple row versions so readers see a snapshot without blocking writers, but the trade-offs are dead-tuple bloat requiring vacuum and write-write conflicts requiring application-level retry.
---