A bank transfer that debits Account A and credits Account B must never leave money in limbo — the debit happened but the credit did not. A flight booking must never sell the same seat twice to two passengers. An e-commerce checkout must decrement inventory exactly once per order even under 10,000 concurrent buyers. ACID makes all of this reliable by default.

Stripe-Style Payment Processing Pipeline

A payment platform processes millions of card charges per day. Each charge involves multiple writes across three tables that must all succeed or all fail.

Atomicity ensures a charge is never "pending" without a ledger entry. Consistency ensures wallet balances never go negative. Durability ensures every completed charge survives a server restart. The optimistic lock (version column) prevents a double-charge race condition.

Atomic charge creation with full audit trail

BEGIN;

-- Create the charge record

INSERT INTO charges(charge_id, customer_id, amount, currency, status)

VALUES ('ch_abc123', 'cus_456', 2999, 'usd', 'pending');

-- Deduct from customer balance (if pre-funded)

UPDATE wallets

SET balance = balance - 2999,

version = version + 1

WHERE customer_id = 'cus_456'

AND balance >= 2999 -- consistency guard

AND version = 14; -- optimistic lock

-- Record ledger entry (double-entry bookkeeping)

INSERT INTO ledger_entries(charge_id, entry_type, amount)

VALUES ('ch_abc123', 'DEBIT', 2999);

-- Mark charge succeeded

UPDATE charges SET status = 'succeeded' WHERE charge_id = 'ch_abc123';

COMMIT;

Refund — atomic reversal of a charge

BEGIN;

INSERT INTO refunds(refund_id, charge_id, amount, status)

VALUES ('re_xyz789', 'ch_abc123', 2999, 'pending');

UPDATE wallets SET balance = balance + 2999

WHERE customer_id = 'cus_456';

INSERT INTO ledger_entries(charge_id, entry_type, amount)

VALUES ('ch_abc123', 'CREDIT', 2999);

UPDATE refunds SET status = 'succeeded' WHERE refund_id = 're_xyz789';

COMMIT;

All

Concert Ticket Inventory (High-Contention Scenario)

Taylor Swift concert: 50,000 tickets, 500,000 concurrent buyers. The system must never oversell and must perform under extreme load.

The FOR UPDATE serialises concurrent booking at the database row level. The CHECK (quantity >= 0) is the final backstop. The 15-minute reservation expiry with atomic cleanup ensures inventory is never stranded by abandoned sessions.

Correct inventory reservation pattern

-- Approach 1: Pessimistic locking (FOR UPDATE)

BEGIN;

SELECT quantity