DEV Community

Mohamed Idris
Mohamed Idris

Posted on

Part 12: Transactions

#sql #database #learning

Part of the "SQL: Zero to Ninja" series, written for junior web devs.

Picture this. A user pays for an order. Your code creates the order row. Then,
right before it saves the items... the server crashes. Now you have a paid order
with nothing in it. The customer is angry, your data is broken, and you are
debugging at 2am. A transaction is the seatbelt that stops this. Let's learn
it.

The idea in one line

A transaction groups several statements into one all-or-nothing unit: either
they all succeed, or none of them happen.

The metaphor: carrying a couch with a friend

You and a friend are carrying a heavy couch up the stairs. The rule is simple:
you both lift, or you both stop. You never leave the couch half-lifted,
balanced on the stairs, while one of you wanders off. That is dangerous and silly.

A transaction is the same deal between your statements. They go up the stairs
together, or they do not go at all.

Half-done is NOT allowed:

  [ subtract money from A ]  done
  [ add money to B ]         crash!  <-- money just vanished

A transaction says: undo the first step too. Back to safe.
Enter fullscreen mode Exit fullscreen mode

The shape of a transaction

Three words do the work:

BEGIN;                                  -- start the unit, nothing saved yet
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                                 -- save everything, all at once
Enter fullscreen mode Exit fullscreen mode
  • BEGIN (some databases use START TRANSACTION) opens the unit.
  • Your statements run, but they are not saved to the world yet.
  • COMMIT makes it all real, together, in one snap.
  • If something goes wrong, ROLLBACK throws it all away as if it never happened: 
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  -- oops, something is wrong
ROLLBACK;                               -- undo it all, balance is untouched
Enter fullscreen mode Exit fullscreen mode

The classic example: a bank transfer

Move $100 from account A to account B. Two steps:

  1. Subtract $100 from A.
  2. Add $100 to B.

If the power dies between step 1 and step 2, the $100 just disappeared into
thin air. The money left A but never reached B.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Wrap it in a transaction and a crash before COMMIT rolls everything back. A
gets its $100 back. No money is ever lost. Both steps, or neither.

ACID, in plain English

People throw around the word "ACID" for transactions. It is four promises:

  • Atomic: all or nothing. Like the couch, no half-done.
  • Consistent: your rules (constraints, foreign keys) still hold true before and after. The data never lands in an illegal state.
  • Isolated: transactions do not step on each other mid-way. While yours is running, others do not see your half-finished mess.
  • Durable: once you COMMIT, it stays saved, even if the power dies one second later.

You do not need to memorize the letters. Just remember: a transaction keeps your
data honest even when things go wrong.

A real case: placing an order

This is where it matters for web devs. Placing one order really means three
writes that belong together:

BEGIN;
  -- 1. create the order
  INSERT INTO orders (user_id, total, status)
  VALUES (42, 59.98, 'paid');

  -- 2. create its items
  INSERT INTO order_items (order_id, product_id, quantity)
  VALUES (1001, 7, 2);

  -- 3. lower the stock
  UPDATE products SET stock = stock - 2 WHERE id = 7;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

If step 2 or step 3 fails, you do not want step 1 hanging around. A
transaction guarantees you never get a paid order with no items, or a sale that
forgot to lower stock. All three, or none.

A quick word on race conditions

Imagine two people click "buy" on the last item at the same instant. Both
requests read "stock = 1", both think they can buy, and now you have sold an item
you do not have.

Request A reads stock = 1   ok, buy!
Request B reads stock = 1   ok, buy!   <-- uh oh, sold twice
Enter fullscreen mode Exit fullscreen mode

This is a race condition. Isolation and database locks help here (for
example, locking the row while you check and update it). You do not need the deep
details yet. Just know transactions are the tool that makes "check then update"
safe.

Gotchas juniors hit

  1. Forgetting to COMMIT. If you BEGIN and never commit, your changes are never saved, and worse, the transaction may hold locks that block other queries. Always close what you open.
  2. Keeping a transaction open too long. A long transaction holds locks and blocks other people from working. Open it late, commit it fast.
  3. Slow stuff inside a transaction. Do not call a payment API or send an email while the transaction is open. Network calls are slow and can hang, leaving locks held. Do the slow work outside, keep the transaction tight around the database writes.

Recap

  • A transaction is an all-or-nothing group of statements.
  • BEGIN to start, COMMIT to save it all, ROLLBACK to undo it all.
  • ACID = Atomic, Consistent, Isolated, Durable. Plain version: your data stays honest even when something fails.
  • Use it for any set of writes that must happen together: bank transfers, placing an order (order + items + stock).
  • Keep transactions short, always commit, and keep slow network calls out.

Your turn

You are building a "transfer points" feature: take 50 points from user A and give
them to user B. Write the transaction. Then ask yourself: if the server crashes
right after you subtract from A but before you add to B, what should happen, and
which keyword makes sure of it? Explain it to a friend and you have got it.

That wraps the core of the "SQL: Zero to Ninja" journey on data safety. Next up
is Part 13: SQL Injection, where we keep attackers from sneaking their own
SQL into your queries.

Top comments (1)

Collapse
 
edriso profile image
Mohamed Idris •

Part 12 Practice: Transactions

Give these a real try before checking the solutions. They mix "reason about it"
questions with writing actual transactions using our shared schema (users,
orders, products, order_items).

1. Write a transfer transaction

You have an accounts table with id and balance. Move $200 from account 1 to
account 2 as one all-or-nothing unit.

Solution

BEGIN;
  UPDATE accounts SET balance = balance - 200 WHERE id = 1;
  UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;
Enter fullscreen mode Exit fullscreen mode

Why: both updates must happen together. If only the first ran, money would
vanish. BEGIN opens the unit and COMMIT saves both at once.

2. Reason about a crash

In the transaction above, the server crashes after the first UPDATE runs
but before COMMIT. What is the balance of account 1 when the database
comes back?

Solution

Unchanged. Because there was no COMMIT, the database rolls the transaction back
automatically. The - 200 is undone, so account 1 keeps its original balance. No
money is lost. That is the "Atomic" promise in action.

3. Place the keywords

Here is the order-placing logic with the transaction keywords removed. Put
BEGIN, COMMIT (and where a failure should land, ROLLBACK) in the right
spots.

INSERT INTO orders (user_id, total, status) VALUES (42, 99.00, 'paid');
INSERT INTO order_items (order_id, product_id, quantity) VALUES (500, 7, 1);
UPDATE products SET stock = stock - 1 WHERE id = 7;
Enter fullscreen mode Exit fullscreen mode

Solution

BEGIN;
  INSERT INTO orders (user_id, total, status) VALUES (42, 99.00, 'paid');
  INSERT INTO order_items (order_id, product_id, quantity) VALUES (500, 7, 1);
  UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;   -- or ROLLBACK; if any step above failed
Enter fullscreen mode Exit fullscreen mode

Why: all three writes belong to one order. Wrapping them means you never get a
paid order with no items or untouched stock.

4. Spot the mistake

A junior wrote this and wonders why their changes "never save":

BEGIN;
UPDATE products SET price = price * 1.1 WHERE category = 'books';
Enter fullscreen mode Exit fullscreen mode

What is missing, and what side effect can it cause?

Solution

There is no COMMIT. The price change is never saved, and the open transaction
may hold locks on those rows, blocking other queries that touch them. Always
close a transaction with COMMIT (to save) or ROLLBACK (to undo).

5. ACID in your own words

A friend asks what the "A" and the "D" in ACID mean. Give a one-line plain answer
for each.

Solution

  • A (Atomic): all the statements happen, or none of them do. No half-done.
  • D (Durable): once you COMMIT, the change stays saved, even if the power dies a second later.

Why: these two are the ones juniors lean on most. Atomic protects you from
half-finished writes, Durable means a committed change is truly safe.

6. The race condition

Two requests try to buy the last unit of product 7 at the same time. Both read
stock = 1 and both proceed, so you sell two of one item. What is this called,
and what tool helps prevent it?

Solution

It is a race condition. Transactions with proper isolation (and row locks,
for example locking product 7 while you check and update its stock) make the
"check then update" safe, so the second request waits and then sees stock = 0.

Great job. If you can wrap writes that must succeed together in BEGIN ...
COMMIT, and explain why a crash should roll back, you have got transactions
down. On to Part 13.