MySQL + SQL · Lesson 82

MySQL Transaction Example with COMMIT and ROLLBACK

A Complete Transaction

A worked example: transferring money safely between two accounts.

The Code

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- if both succeeded:
COMMIT;
-- if something failed, instead run:
-- ROLLBACK;

Either both balances change (COMMIT) or neither does (ROLLBACK) — money is never lost.

Summary

Wrap related updates in START TRANSACTION ... COMMIT.
ROLLBACK undoes everything if any step fails.