Transactions in Depth: Isolation Levels and Locking
What actually happens when two transactions touch the same rows at the same time
Big idea: You already know how to wrap statements in
START TRANSACTION ... COMMIT. The hard part in production is concurrency: many transactions run at once. The isolation level is the dial that decides how much one transaction is allowed to see of another's uncommitted or in-flight work, and the database enforces it with locks and row versions. Choose the level and the locking strategy deliberately, or you will meet dirty data, lost updates and deadlocks in the field.
By the end you will be able to:
- name the three read phenomena: dirty read, non-repeatable read, phantom read;
- choose between READ COMMITTED, REPEATABLE READ and SERIALIZABLE and know MySQL's default;
- explain shared vs exclusive row locks and how
SELECT ... FOR UPDATEdiffers from a plain read; - recognise a deadlock and apply the standard tactics to avoid it;
- reason about optimistic vs pessimistic concurrency and pick one.
0. Our working schema
Every example below runs against two small InnoDB tables: bank accounts we will transfer money between, and shop products whose stock we decrement. The version column is there for the optimistic-locking section.
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
owner VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
INSERT INTO accounts VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00),
(3, 'Carol', 0.00);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
price DECIMAL(8,2) NOT NULL,
stock INT NOT NULL,
version INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT INTO products (product_id, name, price, stock) VALUES
(1, 'Keyboard', 45.00, 10),
(2, 'Mouse', 25.00, 0),
(3, 'Monitor', 220.00, 4);| account_id | owner | balance |
|---|---|---|
| 1 | Alice | 500.00 |
| 2 | Bob | 300.00 |
| 3 | Carol | 0.00 |
| product_id | name | price | stock | version |
|---|---|---|---|---|
| 1 | Keyboard | 45.00 | 10 | 0 |
| 2 | Mouse | 25.00 | 0 | 0 |
| 3 | Monitor | 220.00 | 4 | 0 |
Why InnoDB? Isolation levels and row locking are storage-engine features. InnoDB (MySQL's default engine) supports transactions, row-level locks and MVCC; the old MyISAM engine does not, so none of this applies to it.