Транзакции и параллельная работа

Узнай, как обеспечивается ACID, какие аномалии порождают параллельные транзакции, как работают уровни изоляции, блокировки, MVCC и взаимоблокировки.

  • Университет
  • 30 мин
  • 10 вопросов
  • Урок 24 из 28

В этом уроке ты узнаешь

  • Распознавать грязное чтение, неповторяемое чтение, фантомное чтение и потерянное обновление
  • Выбирать уровень изоляции под задачу и сравнивать подходы с блокировками и MVCC
  • Писать безопасный код с атомарным UPDATE, SELECT ... FOR UPDATE, SAVEPOINT и повторными попытками и избегать взаимоблокировок

На складе остался последний ноутбук. В одну и ту же секунду два покупателя нажимают «Купить», два серверных процесса читают остаток, оба видят «1» и оба подтверждают продажу. В итоге один покупатель платит за товар, которого нет. Это не ошибка отдельной программы, а естественная проблема параллельной работы. В прошлых уроках мы познакомились с транзакцией как «всё или ничего». Теперь вопрос сложнее: когда одновременно работают сотни транзакций, как СУБД изолирует их друг от друга и чего это стоит?

Как обеспечивается ACID

  • Атомарность и долговечность: СУБД сначала пишет изменение в последовательный журнал (WAL — write-ahead log) и принудительно сбрасывает его на диск, и лишь затем COMMIT считается успешным. После сбоя подтверждённая работа восстанавливается по журналу, а незавершённая откатывается.
  • Согласованность: ограничения (CHECK, FOREIGN KEY, UNIQUE) проверяются после каждой команды или в конце транзакции.
  • Изолированность: блокировки или многоверсионное хранение данных (MVCC) — главная тема этого урока.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Аномалии параллельной работы

АномалияЧто происходит
Грязное чтениеT1 видит ещё не подтверждённое изменение T2; если T2 откатится, T1 работала с данными, которых никогда не было.
Неповторяемое чтениеT1 дважды читает одну строку, а между чтениями T2 изменяет и подтверждает её — получаются два разных значения.
Фантомное чтениеT1 дважды считает строки по условию, а между подсчётами T2 вставляет новую подходящую строку — появляется «фантом».
Потерянное обновлениеT1 и T2 читают одно и то же значение, и каждая записывает свой расчёт — одно из изменений бесследно исчезает.
s = s₀ − q₂≠ s₀ − q₁ − q₂

Где

s₀
исходный остаток, который прочитали обе транзакции
q₁, q₂
количество, проданное T1 и T2
s
результат, сохранённый последней записавшей транзакцией (T2)

Потерянное обновление: в схеме «прочитать → посчитать в приложении → записать» продажа T1 выпадает из итога.

Уровни изоляции

Полная изоляция стоит дорого, поэтому стандарт SQL определяет четыре уровня: каждый следующий запрещает больше аномалий, но снижает параллельность. Serializable — самый строгий: результат должен совпадать с последовательным выполнением транзакций в каком-либо порядке.

УровеньГрязное чтениеНеповторяемое чтениеФантом
READ UNCOMMITTEDвозможновозможновозможно
READ COMMITTEDнетвозможновозможно
REPEATABLE READнетнетвозможно
SERIALIZABLEнетнетнет
Минимальные требования стандарта. Реальные системы часто строже: в PostgreSQL REPEATABLE READ не допускает и фантомов, а READ UNCOMMITTED работает как READ COMMITTED.
СУБДУровень по умолчаниюМеханизм
PostgreSQLREAD COMMITTEDMVCC
MySQL (InnoDB)REPEATABLE READMVCC + блокировки строк
SQL ServerREAD COMMITTEDблокировки (режим snapshot — по выбору)
OracleREAD COMMITTEDMVCC
SQLiteSERIALIZABLEодин пишущий на весь файл базы

Блокировки и 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. Как это исправить?

Показать решениеСкрыть решение

Решение

1) Поручи арифметику СУБД — атомарное обновление: 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 строк, кто-то успел раньше — перечитай и повтори.
SQL
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
В SQLite changes() возвращает число строк, изменённых последней командой: для отсутствующего Monitor это 0, и остаток не уходит в минус.

Взаимоблокировки (deadlock)

T1 заблокировала строку A и ждёт B, а T2 заблокировала B и ждёт A — ни одна не может продолжить. СУБД ищет цикл в графе ожиданий и, найдя его, выбирает одну транзакцию «жертвой» и откатывает её, а другая продолжает работу. Ниже — ответ PostgreSQL в двух параллельных сеансах.

SQL
-- 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, иногда возникает взаимоблокировка. Как изменить код?

Показать решениеСкрыть решение

Решение

1) Причина: две транзакции блокируют одни и те же ресурсы в противоположном порядке — в графе ожиданий возникает цикл.
2) Решение: всегда захватывай блокировки в одном порядке — сначала счёт с меньшим id, затем с большим: first = min(from, to), second = max(from, to).
3) Теперь оба перевода начинают со счёта 1: второй просто ждёт окончания первого, и цикл невозможен.
4) Дополнительно: держи транзакцию короткой и при ошибке взаимоблокировки (в PostgreSQL SQLSTATE 40P01) повторяй попытку несколько раз.

Если внутри длинной транзакции нужно отменить один шаг, используют SAVEPOINT: ROLLBACK TO отменяет только изменения после этой точки, а остальное подтверждается COMMIT. Ниже заказ сохраняется, а строка с «подарком» отменяется.

SQL
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. В конце выведи название и остаток обоих товаров, отсортировав по названию. Остаток никогда не должен становиться отрицательным.

Задание · SQL
-- 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).

Задание · SQL
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 или проверка версии.
  • СУБД обнаруживает взаимоблокировку и откатывает одну транзакцию; захватывай блокировки в одном порядке и предусматривай повторы.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

Вопрос 1 / 10

Вопрос 1 / 10. Что такое грязное чтение?