На складе остался последний ноутбук. В одну и ту же секунду два покупателя нажимают «Купить», два серверных процесса читают остаток, оба видят «1» и оба подтверждают продажу. В итоге один покупатель платит за товар, которого нет. Это не ошибка отдельной программы, а естественная проблема параллельной работы. В прошлых уроках мы познакомились с транзакцией как «всё или ничего». Теперь вопрос сложнее: когда одновременно работают сотни транзакций, как СУБД изолирует их друг от друга и чего это стоит?
Как обеспечивается ACID
- Атомарность и долговечность: СУБД сначала пишет изменение в последовательный журнал (WAL — write-ahead log) и принудительно сбрасывает его на диск, и лишь затем
COMMITсчитается успешным. После сбоя подтверждённая работа восстанавливается по журналу, а незавершённая откатывается. - Согласованность: ограничения (
CHECK,FOREIGN KEY,UNIQUE) проверяются после каждой команды или в конце транзакции. - Изолированность: блокировки или многоверсионное хранение данных (MVCC) — главная тема этого урока.
Чтобы читать дальше, зарегистрируйся бесплатно
Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.
Аномалии параллельной работы
| Аномалия | Что происходит |
|---|---|
| Грязное чтение | T1 видит ещё не подтверждённое изменение T2; если T2 откатится, T1 работала с данными, которых никогда не было. |
| Неповторяемое чтение | T1 дважды читает одну строку, а между чтениями T2 изменяет и подтверждает её — получаются два разных значения. |
| Фантомное чтение | T1 дважды считает строки по условию, а между подсчётами T2 вставляет новую подходящую строку — появляется «фантом». |
| Потерянное обновление | T1 и T2 читают одно и то же значение, и каждая записывает свой расчёт — одно из изменений бесследно исчезает. |
Где
- s₀
- исходный остаток, который прочитали обе транзакции
- q₁, q₂
- количество, проданное T1 и T2
- s
- результат, сохранённый последней записавшей транзакцией (T2)
Потерянное обновление: в схеме «прочитать → посчитать в приложении → записать» продажа T1 выпадает из итога.
Уровни изоляции
Полная изоляция стоит дорого, поэтому стандарт SQL определяет четыре уровня: каждый следующий запрещает больше аномалий, но снижает параллельность. Serializable — самый строгий: результат должен совпадать с последовательным выполнением транзакций в каком-либо порядке.
| Уровень | Грязное чтение | Неповторяемое чтение | Фантом |
|---|---|---|---|
| READ UNCOMMITTED | возможно | возможно | возможно |
| READ COMMITTED | нет | возможно | возможно |
| REPEATABLE READ | нет | нет | возможно |
| SERIALIZABLE | нет | нет | нет |
| СУБД | Уровень по умолчанию | Механизм |
|---|---|---|
| PostgreSQL | READ COMMITTED | MVCC |
| MySQL (InnoDB) | REPEATABLE READ | MVCC + блокировки строк |
| SQL Server | READ COMMITTED | блокировки (режим snapshot — по выбору) |
| Oracle | READ COMMITTED | MVCC |
| SQLite | SERIALIZABLE | один пишущий на весь файл базы |
Блокировки и MVCC
Классический подход — блокировки: читающий берёт разделяемую (S) блокировку, пишущий — исключительную (X), а X несовместима ни с какой другой. При двухфазном блокировании (2PL) транзакция сначала только захватывает блокировки, а потом только отпускает — это гарантирует сериализуемый результат, но читатели ждут писателей. MVCC (многоверсионное управление) хранит несколько версий строки: каждая транзакция видит свой снимок (snapshot), поэтому читатели не блокируют писателей, а писатели — читателей. Друг друга ждут лишь два писателя, меняющие одну и ту же строку.
Пример 1: как не потерять обновление
Задача
Приложение сначала выполняет SELECT stock FROM products WHERE id = 1, вычисляет stock - 1 в коде и пишет UPDATE products SET stock = 7 WHERE id = 1. Когда два покупателя покупают одновременно, остаток падает с 8 не до 6, а до 7. Как это исправить?
Показать решениеСкрыть решение
Решение
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock >= 1. Второй писатель ждёт X-блокировку первого и затем видит свежее значение.2) Проверь число обновлённых строк: если 0, товар закончился и продажа отклоняется.
3) Если нужна сложная логика — пессимистичная блокировка:
SELECT stock FROM products WHERE id = 1 FOR UPDATE блокирует строку до конца транзакции.4) Оптимистичная альтернатива: столбец
version и UPDATE ... WHERE id = 1 AND version = 7; если обновлено 0 строк, кто-то успел раньше — перечитай и повтори.CREATE TEMP TABLE sales_log (product TEXT, sold INTEGER);
UPDATE products SET stock = stock - 1 WHERE name = 'Laptop' AND stock >= 1;
INSERT INTO sales_log VALUES ('Laptop', changes());
UPDATE products SET stock = stock - 1 WHERE name = 'Monitor' AND stock >= 1;
INSERT INTO sales_log VALUES ('Monitor', changes());
SELECT l.product, l.sold, p.stock AS stock_now
FROM sales_log AS l
JOIN products AS p ON p.name = l.product
ORDER BY l.product;Ожидаемый результат
product | sold | stock_now Laptop | 1 | 7 Monitor | 0 | 0
changes() возвращает число строк, изменённых последней командой: для отсутствующего Monitor это 0, и остаток не уходит в минус.Взаимоблокировки (deadlock)
T1 заблокировала строку A и ждёт B, а T2 заблокировала B и ждёт A — ни одна не может продолжить. СУБД ищет цикл в графе ожиданий и, найдя его, выбирает одну транзакцию «жертвой» и откатывает её, а другая продолжает работу. Ниже — ответ PostgreSQL в двух параллельных сеансах.
-- session 1 -- session 2
BEGIN; BEGIN;
UPDATE accounts SET balance = balance - 30
WHERE id = 1; UPDATE accounts SET balance = balance - 10
WHERE id = 2;
UPDATE accounts SET balance = balance + 30
WHERE id = 2; -- waits for session 2
UPDATE accounts SET balance = balance + 10
WHERE id = 1; -- cycle!Ожидаемый результат
ERROR: deadlock detected DETAIL: Process 4211 waits for ShareLock on transaction 918; blocked by process 4187. Process 4187 waits for ShareLock on transaction 917; blocked by process 4211. HINT: See server log for query details.
Пример 2: убираем взаимоблокировки из переводов
Задача
В банковском приложении transfer(from, to, amount) сначала обновляет счёт from, затем to. Когда одновременно приходят переводы 1→2 и 2→1, иногда возникает взаимоблокировка. Как изменить код?
Показать решениеСкрыть решение
Решение
2) Решение: всегда захватывай блокировки в одном порядке — сначала счёт с меньшим
id, затем с большим: first = min(from, to), second = max(from, to).3) Теперь оба перевода начинают со счёта 1: второй просто ждёт окончания первого, и цикл невозможен.
4) Дополнительно: держи транзакцию короткой и при ошибке взаимоблокировки (в PostgreSQL SQLSTATE 40P01) повторяй попытку несколько раз.
Если внутри длинной транзакции нужно отменить один шаг, используют SAVEPOINT: ROLLBACK TO отменяет только изменения после этой точки, а остальное подтверждается COMMIT. Ниже заказ сохраняется, а строка с «подарком» отменяется.
BEGIN;
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (5, 10, 1, '2025-08-01');
SAVEPOINT gift;
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (5, 8, 1, '2025-08-01');
ROLLBACK TO gift; -- undo only the gift
COMMIT;
SELECT id, product_id, quantity, order_date
FROM orders
WHERE customer_id = 5
ORDER BY id;Ожидаемый результат
id | product_id | quantity | order_date 6 | 7 | 2 | 2025-03-18 13 | 10 | 1 | 2025-08-01
Задание
С помощью атомарного условного обновления продай 3 штуки Chess set (только если хватает остатка), затем тем же способом попробуй продать 1 Monitor. В конце выведи название и остаток обоих товаров, отсортировав по названию. Остаток никогда не должен становиться отрицательным.
-- sell 3 chess sets
-- try to sell 1 monitor
SELECT name, stock
FROM products
WHERE name IN ('Chess set', 'Monitor')
ORDER BY name;Ожидаемый результат
name | stock Chess set | 15 Monitor | 0
Задание
Отмени заказ № 12 в одной транзакции: сначала верни его количество на остаток товара, затем удали заказ и выполни COMMIT. В конце выведи для товара № 4 название, остаток (stock) и число оставшихся заказов (orders).
BEGIN;
-- 1) give the quantity of order 12 back to its product
-- 2) delete order 12
COMMIT;
SELECT p.name, p.stock,
(SELECT COUNT(*) FROM orders AS o WHERE o.product_id = p.id) AS orders
FROM products AS p
WHERE p.id = 4;Ожидаемый результат
name | stock | orders Notebook | 510 | 1
Запомни
- Атомарность и долговечность обеспечивает журнал упреждающей записи (WAL), изолированность — блокировки или MVCC.
- Аномалии параллельной работы: грязное, неповторяемое и фантомное чтение, потерянное обновление.
- Чем выше уровень изоляции, тем меньше аномалий, но и параллельности; по умолчанию в PostgreSQL — READ COMMITTED, в MySQL InnoDB — REPEATABLE READ.
- От потерянного обновления защищают атомарный
UPDATE ... SET x = x - 1 WHERE x >= 1,FOR UPDATEили проверка версии. - СУБД обнаруживает взаимоблокировку и откатывает одну транзакцию; захватывай блокировки в одном порядке и предусматривай повторы.