Transaction management: ACID, isolation, and distributed systems

Transaction management is the set of database mechanisms that make a group of operations succeed or fail together, as one unit, instead of leaving a system where some of them happened and others didn't. A transfer of $500 between two bank accounts is the standard case: the database debits one account and credits the other as a single operation, because stopping after only the debit has just made $500 disappear. That guarantee is what lets an application treat a multi-step change, a checkout, an inventory update, a ledger entry, as safe to attempt without custom recovery code for each possible failure.

The ACID guarantees

ACID names the four guarantees a database gives every transaction it runs.

• Atomicity. The transaction happens completely or not at all. Any failure partway through rolls back every change already made.
• Consistency. A transaction only moves the database from one valid state to another. Whatever the schema forbids stays forbidden.
• Isolation. Concurrent transactions don't see each other's uncommitted work, to a degree set by the active isolation level.
• Durability. Once a transaction commits, the change survives a crash, written to disk rather than held in memory.

Isolation levels and what they prevent

Isolation is the one ACID guarantee that comes in degrees. SQL defines four standard levels, and each closes off a specific anomaly the level below it allows.

• Read uncommitted lets a transaction see another transaction's uncommitted changes. If that other transaction rolls back, the data it exposed was never real, a dirty read. This level prevents nothing and is rarely anyone's default.
• Read committed shows only data already committed, ruling out dirty reads. The same query, run twice in one transaction, can still return different rows if another transaction commits in between, a non-repeatable read.
• Repeatable read keeps a value stable for the rest of the transaction once read, closing that gap. A second query can still turn up new rows inserted by someone else in the meantime, a phantom read.
• Serializable removes all three anomalies by making transactions behave as if they ran one after another. Enforcing that costs concurrency: the database blocks or retries more to keep the guarantee true.

Why distributed systems break the model

A database transaction is a promise the database can keep because it controls every piece of data involved. That stops holding once the operations span more than one database, the normal shape of a system integration problem: an order service, a payment service, and an inventory service each own their own data, and no single commit can cover all three.

Two-phase commit is the classical answer. A coordinator asks every participating database to prepare the change, and only once all confirm does it tell them to commit for real. It reproduces ACID's all-or-nothing guarantee across separate databases, but every participant holds its locks and waits during that prepare phase. If the coordinator dies after some participants have prepared but before sending the final instruction, those participants stay locked with no safe way to decide alone. That blocking risk is why two-phase commit is rare in systems built for high availability.

The saga pattern

A saga replaces one distributed transaction with a sequence of small, local transactions, each committed on its own and paired in advance with a compensating action. If step four of six fails, a saga doesn't unwind steps one through three inside the database; it runs their compensating actions instead, usually in reverse order, each one its own transaction.

That distinction changes what a product can promise a customer. Crediting money back is a new transaction, separate from the original charge, not a deletion of it. A statement can show both entries, the charge and the reversal, instead of a charge that never happened. Treating a compensating action as a clean undo tends to produce a refund flow that doesn't match the ledger, and a gap to explain later that was never really a bug.

Common transaction design mistakes

Two customers can check out the same last unit in stock and both come away thinking they got it, if nothing locks the row between the read and the write. The usual causes are more mundane than a race condition.

• Long-running transactions that hold locks while waiting on something slow, an external call, a slow report, a user's next click. Every other transaction touching the same rows queues up behind it; what looks like a database slowdown is really one transaction left open too long.
• Treating a call to another service as if it were inside the transaction. A payment confirmation sent from within a transaction that later rolls back doesn't un-send itself; the processor already has it, because the boundary only ever covered the local database.
• Retrying a failed request without making it idempotent. A payment retried after a timeout, with nothing checking whether the first attempt went through, can charge someone twice for one purchase.

When transaction design needs care

Most application code never touches an isolation level directly, and it doesn't need to. Framework and ORM defaults handle ordinary reads and writes safely enough that nobody designs for it on purpose.

That changes once money moves, inventory has to match a physical warehouse, or a record exists that a regulator might eventually ask to see. Payment flows, ledgers, and anything with an audit trail need someone to decide, deliberately, which isolation level applies where, how a distributed step compensates when it fails, and how the system behaves if one request lands twice. Getting it wrong rarely throws an error at the time. The gap surfaces months later as a number that doesn't reconcile, well after the responsible code has shipped.

That's worth an outside review before it ships rather than after a gap turns up, the sort of work behind custom software development and software development consulting engagements on fintech systems that move real money.