transactology.dev

Atomicity

BEGIN TRANSACTION;

All or nothing. A transaction is an indivisible unit of work — either every operation within it completes successfully, or none of them take effect. There is no partial execution, no half-committed state floating in the void between intention and completion.

all three pass, or the slate is wiped clean

The atomicity guarantee is implemented through write-ahead logging (WAL). Before any modification touches the actual data pages, the intended changes are first recorded in a sequential log. If the system crashes mid-transaction, the recovery process reads the log and rolls back any incomplete work.

ROLLBACK; -- undo everything

like lifting the brush before the stroke is finished — no mark remains

Consistency

CHECK (balance >= 0)

A transaction carries the database from one valid state to another. Constraints, triggers, and invariants form a contract that the data must honor — before, during, and after every operation. The system never rests in an invalid configuration.

from one truth to another — no lies in between

Consistency is partly the database's responsibility (enforcing declared constraints) and partly the application's (writing correct transaction logic). The database guarantees that no constraint will be violated at commit time. The application guarantees that the logic itself makes semantic sense.

CONSTRAINT fk_account REFERENCES accounts(id)

the contract is written in schema, not in hope

Isolation

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Concurrent transactions execute as if they were serial — each one unaware of the others' intermediate states. The illusion of solitude in a crowded system. No dirty reads, no phantom rows, no lost updates contaminating the result.

each transaction paints on its own sheet of glass

Isolation levels form a spectrum: READ UNCOMMITTED (chaos), READ COMMITTED (no dirty reads), REPEATABLE READ (stable snapshots), and SERIALIZABLE (perfect isolation). Each level trades performance for correctness. Most systems default to READ COMMITTED — a pragmatic compromise.

SELECT ... FOR UPDATE; -- lock the rows

solitude has a cost — measured in lock contention

Durability

COMMIT; -- written to disk

Once committed, the transaction's effects survive any subsequent failure — power loss, crash, disk corruption. The data persists. The promise of COMMIT is absolute: what is written shall not be unwritten by mere hardware misfortune.

ink that cannot be washed away

Durability is achieved through write-ahead logging combined with forced disk flushes (fsync). The WAL ensures that committed transactions can be replayed after a crash. Replication across multiple nodes extends durability from surviving disk failure to surviving entire machine loss.

wal_level = replica -- PostgreSQL

permanence is not a feature — it is a promise

The Transaction Lifecycle

Every transaction follows a lifecycle: birth (BEGIN), work (read/write operations), and resolution (COMMIT or ROLLBACK). Between birth and resolution lies a liminal space where changes exist in potential — visible to the transaction itself but invisible to others, held in suspension like pigment floating in wet wash before it settles into the paper fiber.

begin, work, resolve — the rhythm of every reliable system

Savepoints add nuance to this lifecycle. Within a transaction, you can create named checkpoints and roll back to them without aborting the entire transaction. It is like adding layers to a watercolor painting — you can lift the top layer without destroying what lies beneath.

SAVEPOINT before_risky_step; -- attempt something ROLLBACK TO before_risky_step;

Concurrency Control

MVCC — Multi-Version Concurrency Control

Rather than locking rows and forcing transactions to wait, MVCC keeps multiple versions of each row alive simultaneously. Each transaction sees a consistent snapshot — its own private copy of the world, frozen at the moment it began reading.

many painters, one canvas — each seeing their own version of the scene

PostgreSQL implements MVCC by storing transaction IDs (xmin/xmax) with each tuple. A row is visible to a transaction if its xmin is committed and precedes the current snapshot, and its xmax is either absent or belongs to an aborted transaction.

SELECT xmin, xmax, * FROM accounts;

every row carries the fingerprint of its creator

Write-Ahead Logging

WAL: intention before action

Before any change touches the data files, the intention is recorded in a sequential log. This simple rule — write the plan before executing it — is the foundation of crash recovery. If the system fails, the log tells the story of what was promised and what was delivered.

first the sketch, then the painting — never the reverse

The WAL is append-only and sequential, making it extremely fast to write. During recovery, the system replays committed entries (redo) and reverses uncommitted ones (undo). This ARIES-style recovery ensures that no committed work is lost and no incomplete work persists.

LSN: Log Sequence Number — the heartbeat of recovery

the log remembers what the crash forgot

Distributed Transactions

When a transaction spans multiple nodes, the challenge multiplies. The Two-Phase Commit protocol (2PC) coordinates the decision: first, all participants vote (prepare phase); then, a coordinator announces the verdict (commit or abort). The protocol guarantees agreement but cannot prevent blocking if the coordinator fails mid-decision.

consensus is expensive — but cheaper than inconsistency

Modern alternatives like Saga patterns decompose distributed transactions into a sequence of local transactions, each with a compensating action. If step 3 fails, compensating actions for steps 2 and 1 execute in reverse. This approach trades strict atomicity for availability — a pragmatic concession in distributed systems.

Saga: T1 → T2 → T3 (fail) → C2 → C1

sometimes undoing is the bravest form of doing

Isolation Levels

Four levels, four trade-offs. READ UNCOMMITTED lets you see uncommitted changes (dirty reads). READ COMMITTED hides uncommitted data. REPEATABLE READ freezes your view of read rows. SERIALIZABLE provides the illusion of sequential execution — at the cost of potential aborts.

more isolation, less throughput — choose wisely

Anomalies decrease as isolation increases: dirty reads vanish at READ COMMITTED, non-repeatable reads at REPEATABLE READ, and phantom reads at SERIALIZABLE. But stronger isolation means more locks, more aborts, and more retries. Most applications find their sweet spot at READ COMMITTED.

The CAP Theorem

In a distributed system, you can have at most two of three: Consistency, Availability, and Partition tolerance. Since network partitions are inevitable, the real choice is between consistency and availability during failure. CP systems refuse to answer rather than risk stale data. AP systems answer but might be wrong.

you cannot paint with all the colors at once

The PACELC extension adds nuance: even when the system is running normally (no partition), there is a trade-off between latency and consistency. Systems like DynamoDB choose availability and low latency. Systems like Spanner choose consistency, paying the price in latency via TrueTime.