Транзакции углублённо: уровни изоляции и блокировки
Что на самом деле происходит, когда две транзакции одновременно трогают одни и те же строки
Главная идея: Вы уже умеете оборачивать команды в
START TRANSACTION ... COMMIT. Самое сложное в продакшене - это конкурентность: множество транзакций выполняется одновременно. Уровень изоляции - это регулятор, который решает, насколько одной транзакции разрешено видеть незакоммиченную или ещё выполняющуюся работу другой, а СУБД обеспечивает это блокировками и версиями строк. Выбирайте уровень и стратегию блокировки осознанно, иначе в бою вас ждут грязные данные, потерянные обновления и взаимоблокировки.
К концу урока вы сможете:
- назвать три феномена чтения: грязное чтение, неповторяющееся чтение, фантомное чтение;
- выбирать между READ COMMITTED, REPEATABLE READ и SERIALIZABLE и знать значение по умолчанию в MySQL;
- объяснить разделяемые и монопольные блокировки строк и чем
SELECT ... FOR UPDATEотличается от обычного чтения; - распознать взаимоблокировку и применить стандартные приёмы, чтобы её избежать;
- рассуждать об оптимистичном и пессимистичном подходах к конкурентности и выбирать один.
0. Наша рабочая схема
Каждый пример ниже работает с двумя небольшими таблицами InnoDB: банковские accounts, между которыми мы будем переводить деньги, и магазинные products, у которых мы уменьшаем stock. Столбец version нужен для раздела про оптимистичную блокировку.
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 |
Почему InnoDB? Уровни изоляции и построчная блокировка - это возможности движка хранения. InnoDB (движок MySQL по умолчанию) поддерживает транзакции, построчные блокировки и MVCC; старый движок MyISAM - нет, поэтому к нему всё это неприменимо.