Database Transactions & Consistency

⚡ TL;DR: Transactions guarantee all-or-nothing, but the lever that actually prevents anomalies is your isolation level. Learn to pick it deliberately, choose optimistic vs pessimistic locking by contention, and stop treating eventual consistency as a bug.

Contents

• Two transfers, one disappearing $100
• What a transaction promises
• The shape of a transaction
• Isolation levels and the anomalies they prevent
• Doing it in SQL: commit, rollback, and locking
• Optimistic vs pessimistic, and a word on eventual consistency
• Common mistakes that cost hours
• Takeaways
• Where to go next

Two transfers, one disappearing $100

Alice has $100. At 12:00:00.000 two transfer requests hit your server at the same time: one moves $100 to Bob, the other moves $100 to Carol. Each request runs the same code: read Alice's balance ($100), check it's enough (it is), subtract $100, write the new balance ($0). Both reads happen before either write lands. Both checks pass. Both writes succeed. Bob gets $100, Carol gets $100, and Alice's balance is $0, not -$100. You just created $100 out of thin air.

Nobody wrote a bug. The logic is correct for one request at a time. It falls apart because two requests interleaved, and nothing forced them to take turns. This is the entire reason database transactions exist: to make a sequence of steps behave as if it were a single, indivisible, isolated operation, even when thousands of them run concurrently.

📌 Who this is for: Backend engineers who use BEGIN/COMMIT but have never been bitten hard enough to learn why they matter. You'll leave understanding ACID concretely, knowing which isolation level prevents which anomaly, when to reach for optimistic vs pessimistic locking, and what "eventual consistency" actually buys and costs. No CS degree assumed.

What a transaction promises

A transaction is a group of operations that the database treats as a single unit: all of them happen, or none of them do, and to everyone else it looks instantaneous.

The one-sentence definition of ACID

The classic example is a bank transfer. Moving $100 from Alice to Bob is really two writes: subtract $100 from Alice, add $100 to Bob. If the server crashes between them, you must never end up in a world where Alice was debited but Bob was never credited. The money has to fully move or not move at all. A transaction is the contract that guarantees exactly that.

ACID, mapped to a single bank transfer.

Those four letters are ACID. Atomicity and durability are mostly the database's job, it writes to a durable log before it acknowledges your COMMIT. Consistency is shared: the database enforces the constraints you declare (foreign keys, CHECK, UNIQUE), but it can't know your business rules unless you encode them. Isolation is the subtle one, and where most real bugs live, it's where the $100-from-thin-air problem hides.

The shape of a transaction

Every transaction has the same skeleton: you BEGIN, run one or more operations, then either COMMIT (make it permanent and visible) or ROLLBACK (throw it all away as if it never happened). Below, two transactions run against the same rows at once, and isolation is what decides what each one is allowed to see of the other.

One transaction's lifecycle (top row) and a second concurrent transaction (bottom row) contending for the same rows; isolation governs what each sees of the other's uncommitted work.

1. BEGIN: You open a transaction. The database takes a consistent snapshot point and starts tracking every change you make so it can undo them if needed.
2. Run operations: Your reads and writes happen, but to other sessions they're invisible (or visible-but-blocked, depending on isolation level). Nothing is permanent yet.
3. Decide: COMMIT or ROLLBACK: If every step succeeded, COMMIT durably writes the changes and makes them visible to everyone. If anything failed, ROLLBACK discards every change, the database returns to exactly its pre-BEGIN state.
4. Concurrency is resolved here: When Txn A and Txn B touch the same rows, the isolation level decides whether B sees A's uncommitted data, blocks until A finishes, or fails with a conflict it must retry.

Isolation levels and the anomalies they prevent

Perfect isolation is expensive, making every transaction take strict turns kills throughput. So SQL defines a ladder of isolation levels that trade safety for concurrency. Each rung permits certain anomalies: weird results caused by interleaving. Know the three you'll meet most:

• Dirty read, you read a row another transaction has changed but not yet committed. If it rolls back, you acted on data that never officially existed.
• Non-repeatable read, you read a row twice in one transaction and get different values, because someone else committed an UPDATE in between.
• Phantom read, you run the same WHERE query twice and get a different set of rows, because someone else committed an INSERT or DELETE matching your filter.

The four standard SQL isolation levels and which anomalies each still allows. Lower in the table = safer but more contention.

💡 Defaults differ, check yours: PostgreSQL defaults to Read committed; its Repeatable read is snapshot-based and actually prevents phantoms too (the * above), and its Serializable detects conflicts and aborts one transaction to retry. MySQL/InnoDB defaults to Repeatable read. Never assume, SHOW transaction_isolation; and read your engine's docs, because the same level name can mean subtly different things.

The practical rule: Read committed is a sane default for most CRUD apps. Bump to Repeatable read or Serializable only for transactions where correctness depends on a stable view, like a report that sums rows it also reads back, or a balance check that must hold for the whole transaction. Higher levels mean more blocking and more retryable conflicts, so pay for them only where you need them.

Doing it in SQL: commit, rollback, and locking

Here's the bank transfer done correctly, wrapped in a transaction so it's all-or-nothing, with an explicit ROLLBACK path. The CHECK keeps the books consistent even if logic slips.

transfer.sql

BEGIN;

-- Debit Alice; the CHECK constraint refuses to let her go negative
UPDATE accounts
   SET balance = balance - 100
 WHERE id = 'alice' AND balance >= 100;

-- If no row matched (insufficient funds), bail out and undo everything
-- (application checks the affected row count; on 0 it issues:)
--   ROLLBACK;

-- Credit Bob
UPDATE accounts
   SET balance = balance + 100
 WHERE id = 'bob';

COMMIT;  -- both writes land together, or neither does

But two concurrent transfers can still race, the lost-update problem from the hook. There are two ways to stop it. Pessimistic locking grabs the row up front so nobody else can touch it until you're done:

pessimistic.sql

BEGIN;

-- Lock Alice's row: any other txn that also does SELECT ... FOR UPDATE
-- (or tries to UPDATE it) blocks here until this transaction ends.
SELECT balance FROM accounts WHERE id = 'alice' FOR UPDATE;

-- Now we hold the lock; the read-check-write is safe.
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
UPDATE accounts SET balance = balance + 100 WHERE id = 'bob';

COMMIT;  -- lock releases here

Optimistic locking assumes conflicts are rare. You don't lock, you add a version column, read it, and only write if it hasn't changed since you read it. If someone else got there first, your UPDATE affects zero rows and you retry:

optimistic.sql

-- Step 1: read the row AND its current version (no lock held)
SELECT balance, version FROM accounts WHERE id = 'alice';
--   -> balance = 100, version = 7

-- Step 2: write only if the version is still 7. Note version bumps.
UPDATE accounts
   SET balance = balance - 100,
       version = version + 1
 WHERE id = 'alice' AND version = 7;

-- Step 3: check the affected row count.
--   1 row  -> success, you won the race.
--   0 rows -> someone else updated first (version is now 8);
--             re-read and retry the whole operation.

Optimistic vs pessimistic, and a word on eventual consistency

Neither lock is "better", they're bets on how often conflicts happen. Pessimistic locking prevents conflicts by blocking; it's right when contention is high or the work between read and write is short, but it costs throughput and risks deadlocks if you lock rows in inconsistent orders. Optimistic locking lets everyone proceed and only pays a cost when a conflict actually occurs; it shines under low contention and for long think-time (e.g. a user editing a form for two minutes), but you must handle the retry path or you'll silently drop the loser's write.

Choosing a concurrency strategy.

Everything so far assumes one database that can give you a single, instantly-consistent answer. Spread data across many machines and you hit the CAP trade-off: when the network between nodes breaks (a Partition), you can only keep one of Consistency (every read sees the latest write) or Availability (every request still gets an answer). You can't have both during a partition, and partitions are not optional, so the real choice is C-vs-A under failure.

Many distributed stores pick availability and offer eventual consistency: a write may not be visible everywhere immediately, but if writes stop, all replicas converge to the same value given enough time. That's why you sometimes post a comment and don't see it on a refresh, your read hit a replica that hadn't caught up. It's a perfectly good trade for likes, feeds, and caches; it's a terrible trade for account balances, which is exactly where you keep a strongly-consistent, ACID-transactional database. Pick per-use-case, not per-religion. (More on the storage side in SQL vs NoSQL.)

Common mistakes that cost hours

1. No transaction around multi-step writes. Any operation that's two or more writes (transfer, order + line items, create-user + create-profile) must be one transaction. Without it, a crash or error leaves you half-written and corrupt.
2. Wrong isolation level. Running a balance check or a sum-and-reconcile at Read committed lets values shift mid-transaction. Match the level to the invariant you need to hold; default Read committed is not enough for everything.
3. Long-running transactions. Holding a transaction open across a network call, an external API, or user think-time keeps locks and snapshots alive, bloating the database and blocking others. Keep transactions short; do slow work outside them.
4. Ignoring the lost update. Read-modify-write without FOR UPDATE or a version check is the $100-from-thin-air bug. Pick pessimistic or optimistic locking, "neither" is a silent data-loss bug waiting to happen.
5. Catching errors but not rolling back. If you swallow an exception mid-transaction and keep going, you may COMMIT partial work. Always ROLLBACK on any failure inside the transaction.

Takeaways

The whole article in seven lines

• A transaction makes many operations behave as one: all commit, or all roll back.
• ACID = Atomicity, Consistency, Isolation, Durability, isolation is where real bugs hide.
• Anomalies climb a ladder: dirty read → non-repeatable read → phantom read.
• Isolation levels trade safety for concurrency; Read committed is a sane default, raise it only where an invariant demands a stable view.
• Stop lost updates with pessimistic locking (FOR UPDATE) or optimistic locking (a version column + retry).
• Across machines, CAP forces a Consistency-vs-Availability choice during partitions.
• Eventual consistency converges over time, great for feeds, wrong for balances.

Where to go next

You now have the vocabulary to reason about correctness under concurrency, the foundation under every backend that touches money, inventory, or counts. Build on it:

• Decide where your transactional data lives, read SQL vs NoSQL to weigh strong consistency against scale.
• Move slow work out of transactions and off the request path with Async Processing: Queues & Workers.
• See how these fit a full role on the Backend Engineer path.

Originally published on TheSimplifiedTech, where this guide is interactive, with in-browser terminal labs and diagrams. Learn cloud and DevOps by doing, no videos.