Как работают блокировки строк и MVCC в InnoDB и как избежать взаимоблокировок?

Question

IT Interview Practice · Accepted Answer

InnoDB сочетает два механизма. **MVCC** позволяет обычным `SELECT` читать согласованный снимок без блокировки, поэтому читатели никогда не блокируют пишущих. **Блокировки** защищают записи: `UPDATE`/`DELETE`/`SELECT ... FOR UPDATE` берут блокировки строк, и InnoDB также использует **next-key locks** (строка + промежуток перед ней), чтобы предотвращать фантомы при REPEATABLE READ.

## Взаимоблокировки

Взаимоблокировка — это цикл: T1 удерживает блокировку A и хочет B, тогда как T2 удерживает B и хочет A. Ни одна не может продолжить. InnoDB **обнаруживает цикл и откатывает одну транзакцию** (более дешёвую жертву) с ошибкой 1213, так что другая завершается.

```sql
-- T1                              -- T2
BEGIN;                             BEGIN;
UPDATE acct SET bal=bal-1          UPDATE acct SET bal=bal-1
  WHERE id=1;  -- блокирует строку 1   WHERE id=2;  -- блокирует строку 2
UPDATE acct SET bal=bal+1          UPDATE acct SET bal=bal+1
  WHERE id=2;  -- ждёт T2              WHERE id=1;  -- ждёт T1 => ВЗАИМОБЛОКИРОВКА
-- ERROR 1213: Deadlock found; transaction rolled back
```

## Как их избегать

- **Согласованный порядок блокировок** — всегда обращайтесь к строкам в одном и том же порядке (например, по возрастанию id). Пример приводит к взаимоблокировке только потому, что две транзакции блокируют 1→2 и 2→1.
- **Держите транзакции короткими** — берите блокировки поздно, фиксируйте быстро, не удерживайте блокировки на время ожидания сети/пользователя.
- **Блокируйте минимум** — индексируйте свой `WHERE`, чтобы InnoDB блокировал конкретные строки, а не диапазоны или (без индекса) фактически каждую просканированную строку.
- **Повторяйте при 1213** — взаимоблокировки нормальны при конкуренции; оберните транзакцию в повтор на уровне приложения.

```sql
SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK объясняет, кто чего ждал
```

## Почему это важно

При реальной конкурентности конкуренция за блокировки и взаимоблокировки — это разница между базой, которая масштабируется, и той, что застревает. Знание того, что читатели используют MVCC (поэтому не блокируются), что взаимоблокировки ожидаемы и повторяемы, а согласованный порядок блокировок предотвращает большинство из них, — именно то, чего требует дежурная работа уровня senior.

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

Как их избегать

Почему это важно

Похожие вопросы