Transactions

Pradeep P
3 days ago
3 min read

Series: Modern System Design · Layer 4 — Data

Layer 4 · Post 2 of 11

← Previous: ACID → Next: Isolation Levels

Layer 4 — Data · Post 41 of 88

A transaction groups several reads and writes into one all-or-nothing unit of work, so partial updates do not leak into the real world.

What you'll learn

What BEGIN / COMMIT / ROLLBACK actually do, including after a crash
Why long transactions are a locking and bloat problem, not a "safer" habit
When one transaction is enough — and the moment you have already left that world

The idea in one minute

A transaction is the unit ACID applies to. You open one, do several statements, then either commit (make the whole thing visible and durable) or roll back (pretend none of it happened).

BEGIN INSERT into orders ... UPDATE inventory SET qty = qty - 1 ... INSERT into outbox ... COMMIT

If the process dies between the inventory update and the outbox insert, a well-implemented engine leaves neither change visible. That is atomicity, applied to a named unit of work.

Autocommit is a transaction too: every statement is its own tiny one. You just did not get to group them.

Why it matters

Almost every correctness bug that "should have been impossible" is a missing transaction: order created, inventory not decremented; payment captured, invoice never written.

In interviews, "put it in a transaction" is the right first answer inside one database. The senior follow-up is: what if the second write is Kafka, Stripe, or another Postgres? That is no longer this post. That is distributed transactions, later in this layer.

How it works

The client (or the ORM) starts a transaction. The database assigns it a snapshot and/or locks, depending on isolation.
Writes go to the buffer and the WAL. Other transactions may or may not see them yet.
Commit: the engine makes the changes durable and visible according to isolation rules. Rollback: it discards them.
Crash recovery replays the WAL: committed work is redone, uncommitted work is undone.

ORMs hide this (@Transactional, db.transaction(async trx => ...)). That is convenient until a HTTP call sits inside the transaction, holding locks while Stripe thinks.

Keep the transaction short: read what you need, write, commit. Do I/O to other systems outside it. If you must emit an event atomically with the row change, write an outbox row in the same transaction and publish later. That pattern shows up again in CDC.

A simple example

A checkout in Postgres: insert orders, decrement inventory, insert payments as pending. One transaction. Either the customer has an order and inventory moved, or the page can retry cleanly.

Then you call Stripe. Stripe is not in that transaction. You store the PaymentIntent id in a later update, or you drive the flow from Stripe webhooks. The database transaction bought you a consistent local starting point. It did not buy you a distributed commit.

Common mistakes

One transaction around the whole request. You hold row locks while a user stares at 3D Secure. Other checkouts wait. Vacuum cannot clean dead tuples. Postgres idle_in_transaction is a production celebrity.

Implicit autocommit across two statements. Read balance, then write balance, each in its own transaction. Two requests interleave. Isolation and locking are how you fix that — next posts.

Catching an error and committing anyway. A mid-transaction exception in some ORMs still commits remaining work unless you roll back. Know your client.

How this shows up in real systems

Postgres / MySQL: BEGIN/COMMIT; savepoints for nested rollback without aborting the whole unit.
DynamoDB TransactWriteItems: up to 100 items, all-or-nothing, across partitions — still one product, not "Postgres plus Kafka."
SQLite: one writer at a time; a long transaction is a site-wide pause.
ORMs (Hibernate, Active Record, SQLAlchemy): the transaction boundary is often the real design, not the SQL you imagined.

Recap

A transaction is the all-or-nothing unit ACID talks about. Keep it short and inside one engine.
Group the statements that must not leak partially; do not group network calls.
Isolation decides what concurrent transactions see. That is next.