MVCC Transaction Processing - Non-Blocking Reads

intermediate

PostgreSQL uses MVCC (multi-version concurrency control) to allow readers and writers to work simultaneously without blocking. When a transaction updates a row, PostgreSQL keeps the old version for concurrent readers. This enables high-concurrency workloads like Uber ride matching (50K writes/sec, zero blocking reads).

-- Setup: Create users table

CREATE TABLE users (

user_id INT PRIMARY KEY,

name VARCHAR(100),

balance DECIMAL(10,2),

updated_at TIMESTAMP

);

INSERT INTO users VALUES

(1, 'Alice', 1000.00, NOW()),

(2, 'Bob', 500.00, NOW());

-- Transaction 1: Long-running read (analytics)

BEGIN;

SELECT user_id, name, balance

FROM users

WHERE balance > 0;

-- Result: Alice: $1000, Bob: $500

-- Transaction sees snapshot at BEGIN time

-- Runs for 10 seconds (complex analytics)

-- Transaction 2: Update (concurrent, doesn't wait)

BEGIN;

UPDATE users

SET balance = balance - 100,

updated_at = NOW()

WHERE user_id = 1;

-- Creates new row version: Alice: $900

-- Old version kept: Alice: $1000

-- Commits immediately (doesn't block Transaction 1)

COMMIT;

-- Transaction 1: Read again (still sees old data)

SELECT user_id, name, balance

FROM users

WHERE balance > 0;

-- Result: Alice: $1000, Bob: $500 (unchanged)

-- Still sees snapshot from BEGIN time

-- Not blocked by Transaction 2's update

COMMIT;

-- New transaction: Sees latest data