What is a transaction?
A transaction groups several statements into one unit of work that either happens completely or not at all — the engine-neutral theory, ACID and what each letter promises, lives in Concepts: what is a transaction?. This page is MySQL keeping the promise — with one caveat PostgreSQL doesn't have.
How to read the demos
Each lesson shows a transcript generated from an actual run of the scenario: plain SQL, color-coded per session (A, B, … are separate MySQL connections). How the transcripts are produced and verified is explained in What this is
The caveat: all of this applies to InnoDB, MySQL's default storage engine. Tables on other engines (e.g. MyISAM) are not transactional at all — their writes commit instantly, always.
Atomicity, demonstrated
Session A transfers 150 from alice (who has only 100) to bob. The credit to bob succeeds; the debit from alice violates a CHECK constraint. Watch what happens to bob's already-successful credit:
A transfers 150 from alice to bob. Crediting bob works fine…
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = balance + 150 WHERE owner = 'bob';
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE owner = 'bob'; -- B can't see A's uncommitted credit
balance
---------
50
(1 row)…but debiting alice violates the CHECK constraint — she only has 100.
A> UPDATE accounts SET balance = balance - 150 WHERE owner = 'alice'; -- ER_CHECK_CONSTRAINT_VIOLATED
ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.Roll the transaction back. Bob's credit — which had succeeded — evaporates with it.
A> ROLLBACK;
Query OK
B> SELECT owner, balance FROM accounts ORDER BY id;
owner | balance
-------+---------
alice | 100
bob | 50
(2 rows)Verified against MySQL 8.4.10 · Run it yourself · Scenario source
Two things to carry forward. Unlike PostgreSQL, a failed statement doesn't doom a MySQL transaction — you may roll back, but you're not forced to, as the next lesson proves. And other sessions never saw an intermediate state: B read bob's balance mid-transfer and got the old value, which is exactly what isolation levels govern.