DEV Community

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.

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

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

The classic example: a bank transfer

Move 100ドル from account A to account B. Two steps:

Subtract 100ドル from A.
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;

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;

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

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

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.
Keeping a transaction open too long. A long transaction holds locks and blocks other people from working. Open it late, commit it fast.
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)

edriso profile image

Mohamed Idris

Hello, world!

Email

edriso.dev@gmail.com
Education

BCS @ Alexandria University
Work

Web Developer
Joined

Dec 17, 2020

• May 29

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;

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;

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

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';

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.