SQL Help

MySQL transactions: all-or-nothing changes

Published August 2026 · completing the classic SQL Help series

A transaction groups several statements into one all-or-nothing unit: either every change in the group becomes permanent, or none of them do. It's the tool for the moments when "half-done" is worse than "not done".

The classic example

Move £50 between two accounts:

START TRANSACTION;

UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;

COMMIT;

If the connection dies, the server crashes, or your application errors between the two updates, the first update is rolled back automatically — no £50 vanishes into the void. Until COMMIT, other connections never see the half-finished state; if you change your mind, ROLLBACK; undoes everything since the transaction began.

The same shape protects any multi-table change — like the parent-and-child inserts in our LAST_INSERT_ID() article: wrap the article insert and its tag inserts together and you can never end up with orphaned halves.

You're already using transactions

MySQL runs with autocommit on by default: every single statement is silently its own tiny transaction, committed the instant it finishes. That's why beginners can use MySQL for years without meeting COMMIT. START TRANSACTION simply suspends autocommit until you decide the group's fate.

Two consequences worth knowing:

• There is no undo for a plain UPDATE you ran with autocommit on — it committed as it ran. (For risky manual surgery, type START TRANSACTION; first, run the update, check the row counts and a SELECT, then commit or roll back. This habit has saved more production data than any backup schedule.)
• DDL statements — CREATE TABLE, ALTER, DROP — commit implicitly and can't be rolled back in MySQL. Don't mix schema changes into a data transaction and expect an undo.

It's InnoDB doing the work

Transactions are a property of the storage engine. InnoDB — the default for every table you've created this couple of decades — supports them fully. The only time this bites is on ancient tables explicitly created as MyISAM (common in 2000s-era code this site remembers well): MyISAM ignores transactions silently — statements just commit one by one, no error, no safety. Check with SHOW CREATE TABLE yourtable; and migrate anything critical: ALTER TABLE yourtable ENGINE=InnoDB;

From application code

Every driver wraps the same three verbs — here's PHP PDO, with the pattern that matters (roll back in the error path, then re-throw):

try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE account_id = ?')
        ->execute([50, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE account_id = ?')
        ->execute([50, 2]);

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

Locking, briefly

While your transaction holds changed rows, other writers to those rows wait. Two rules keep this civilised:

• Keep transactions short. Do the reads, the thinking and the API calls before START TRANSACTION; hold the transaction only for the writes. A transaction left open across a user's lunch break is how whole applications grind to a halt.
• Read-then-write needs FOR UPDATE. If you read a value in order to change it (check a balance, claim a seat — see the double-booking section of the reservations article), lock it as you read: SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE; — otherwise two transactions can both read the old value and both act on it.

That's the working core: START TRANSACTION, COMMIT, ROLLBACK, keep them short, lock what you read-to-write, and let InnoDB handle the rest.