Database Transactions & Consistency

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.

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 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.

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.

1. 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. 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. 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. 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 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.

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:

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:

-- 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.

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

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.