Transactions and ACID
Group several statements into one all-or-nothing unit of work
Big Idea: A transaction is a group of SQL statements that the database treats as a single, indivisible unit: either all of them take effect, or none of them do. You open a transaction with
START TRANSACTION, make your changes, and finish with COMMIT to save everything or ROLLBACK to throw it all away. This is how a bank can move money between two accounts without ever losing or inventing a single cent.
By the end you will be able to:
- open, save and undo work with
START TRANSACTION,COMMITandROLLBACK, - explain the four ACID guarantees using a bank transfer,
- describe how
autocommitwraps single statements automatically, - predict exactly what survives when a statement fails or a connection drops,
- use
SAVEPOINTto roll back only part of a transaction.
1. Our example schema: a tiny bank
Every example in this topic uses one small accounts table. Each row is a customer account with a name and a current balance. We use the InnoDB storage engine because in MySQL only InnoDB supports transactions (the older MyISAM engine ignores them). Create and fill the table once:
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
owner VARCHAR(40) NOT NULL,
balance DECIMAL(10,2) NOT NULL
) ENGINE = InnoDB;
INSERT INTO accounts (account_id, owner, balance) VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00),
(3, 'Carol', 1000.00);The starting data looks like this:
| account_id | owner | balance |
|---|---|---|
| 1 | Alice | 500.00 |
| 2 | Bob | 300.00 |
| 3 | Carol | 1000.00 |
We use DECIMAL(10,2) for money on purpose: it stores exact values, unlike FLOAT, which would introduce rounding errors you never want near a bank balance.