Блокировки и взаимоблокировки

Блокировка — это способ СУБД сказать «эту строку сейчас меняю я, подождите», чтобы две транзакции не затоптали данные друг друга.

Блокировка (lock) — временный «замок» на ресурсе (строке, таблице), который СУБД выдаёт транзакции, чтобы упорядочить одновременный доступ. Взаимоблокировка (deadlock) — ситуация, когда две транзакции взаимно ждут замки друг друга и не могут продолжить.

Уровни изоляции из прошлого урока — это «что видно». Блокировки — это «кто кого ждёт». Когда T1 меняет строку, СУБД ставит на неё замок; T2, которая хочет изменить ту же строку, ждёт, пока T1 закоммитит. Обычно это незаметно, но иногда ожидание превращается в тупик, в котором обе транзакции стоят навсегда — пока СУБД не вмешается.

Зачем это на практике

Под нагрузкой блокировки определяют, насколько хорошо система «дышит». Слишком грубые блокировки (на всю таблицу) убивают параллелизм — пользователи стоят в очереди. Неправильный порядок захвата ресурсов порождает дедлоки: запросы случайно падают с ошибкой deadlock detected. Понимание блокировок — это умение писать код, который под сотней одновременных пользователей не встаёт колом и не падает.

Строчные и табличные блокировки

СУБД блокирует на разной «гранулярности». Строчная блокировка (row lock) запирает только конкретные строки — две транзакции, меняющие разные строки одной таблицы, друг другу не мешают. Табличная блокировка (table lock) запирает таблицу целиком — её ставят ALTER TABLE, иногда массовые операции; пока она держится, остальные ждут.

Кроме гранулярности у замков есть «режим». Самые важные два:

Режим	Кто берёт	Совместимость
Разделяемый (shared, на чтение)	несколько читателей одновременно	совместим с другими shared, но не с exclusive
Исключительный (exclusive, на запись)	один писатель	несовместим ни с чем — все остальные ждут

Отсюда правило: «много читателей — норма, один писатель — монополист». Обычный UPDATE/DELETE берёт исключительную блокировку на изменяемые строки до конца транзакции.

SELECT FOR UPDATE: блокируем заранее

Иногда нужно прочитать строку с намерением изменить и не дать никому вклиниться между чтением и записью. Обычный SELECT блокировку на запись не ставит. Для этого есть SELECT ... FOR UPDATE: он читает строки и сразу вешает на них исключительную блокировку до конца транзакции.

-- классический паттерн «прочитал остаток → проверил → списал»
BEGIN;
  SELECT quantity FROM stock WHERE product_id = 7 FOR UPDATE;  -- блокируем строку
  -- здесь никто другой не изменит этот товар, пока мы не закоммитим
  UPDATE stock SET quantity = quantity - 1 WHERE product_id = 7;
COMMIT;

Без FOR UPDATE две параллельные транзакции могли бы обе прочитать «на складе 1 штука», обе решить «можно продать» и обе списать — получив остаток −1. Это потерянное обновление (lost update), и FOR UPDATE — простой способ его избежать: вторая транзакция подождёт у заблокированной строки, прочитает уже актуальное значение и увидит, что товара нет.

Взаимоблокировка: как она возникает

Дедлок — это «после вас» — «нет, после вас», доведённое до абсурда. Две транзакции захватывают ресурсы в разном порядке и ждут друг друга по кругу.

T1: BEGIN; UPDATE accounts SET balance=balance-100 WHERE id=1;  -- захватил замок на строку 1
T2: BEGIN; UPDATE accounts SET balance=balance-50  WHERE id=2;  -- захватил замок на строку 2

T1: UPDATE accounts SET balance=balance+100 WHERE id=2;  -- ждёт замок на 2 (его держит T2)
T2: UPDATE accounts SET balance=balance+50  WHERE id=1;  -- ждёт замок на 1 (его держит T1)
-- круг замкнулся: оба ждут вечно

СУБД не даёт системе зависнуть навсегда: специальный детектор находит цикл ожидания и принудительно откатывает одну из транзакций («жертву») с ошибкой вроде deadlock detected. Вторая после этого продолжает работу.

ERROR:  deadlock detected
DETAIL: Process 123 waits for ShareLock on transaction 456; blocked by process 789.
        Process 789 waits for ShareLock on transaction 455; blocked by process 123.
HINT:   See server log for query details.

Как избежать дедлоков

Главный приём — всегда захватывать ресурсы в одном и том же порядке. Если бы обе транзакции в примере выше блокировали строки по возрастанию id (сначала 1, потом 2), цикл бы не возник: вторая просто подождала бы первую у строки 1.

-- ОБЕ транзакции трогают строки в порядке возрастания id → дедлок невозможен
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- сначала меньший id
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- потом больший
COMMIT;

Дополнительные правила:

Короткие транзакции. Чем меньше держатся замки, тем меньше окно для пересечения.
Единый порядок доступа к таблицам. Если несколько таблиц — всегда обращайтесь к ним в одной и той же последовательности во всём коде.
Готовность к повтору. Дедлок — не катастрофа, а ожидаемое событие: ловите ошибку и повторяйте транзакцию.

Как это работает под капотом

СУБД ведёт граф ожидания (wait-for graph): «транзакция A ждёт транзакцию B». Когда A блокируется на занятом замке, в граф добавляется ребро A → B. Если в графе появляется цикл — это и есть дедлок. Детектор периодически (или при постановке в ожидание) ищет циклы и, найдя, выбирает жертву (обычно ту, что сделала меньше работы или дешевле откатывается) и откатывает её. Поэтому дедлок всегда разрешается за конечное время — система не виснет, кто-то один просто получает ошибку.

Частые ошибки

Читать без FOR UPDATE там, где собираетесь писать. Паттерн «прочитал → проверил → обновил» без блокировки строки открывает гонку и потерянные обновления.
Захватывать ресурсы в разном порядке в разных частях кода. Это прямая дорога к дедлокам, которые «вылезают только на проде под нагрузкой».
Не обрабатывать ошибку deadlock. Без повтора транзакции-жертвы пользователь видит непонятную ошибку вместо успешной операции.
Лишние табличные блокировки. LOCK TABLE «на всякий случай» или тяжёлый ALTER в час пик останавливают всех читателей и писателей.

Итоги

Блокировки упорядочивают параллельный доступ: строчные запирают строки, табличные — таблицу целиком.
Shared-замки (чтение) совместимы между собой; exclusive-замки (запись) монопольны.
SELECT ... FOR UPDATE блокирует прочитанные строки и спасает от потерянных обновлений в паттерне «прочитал → проверил → обновил».
Дедлок возникает при захвате ресурсов в разном порядке; СУБД находит цикл ожидания и откатывает одну транзакцию.
Главная профилактика — единый порядок захвата ресурсов, короткие транзакции и готовность повторить транзакцию-жертву.