Skip to content

Instantly share code, notes, and snippets.

@revilon1991
Last active March 23, 2025 14:05
Show Gist options
  • Save revilon1991/faa3a30b7a5a4a8e6377e53c610cdae7 to your computer and use it in GitHub Desktop.
Save revilon1991/faa3a30b7a5a4a8e6377e53c610cdae7 to your computer and use it in GitHub Desktop.
MySQL InnoDB synthetic lock

Совместимость типов блокировок на уровне таблицы представлена в следующей матрице

X IX S IS
X Конфликт Конфликт Конфликт Конфликт
IX Конфликт Совместимо Конфликт Совместимо
S Конфликт Конфликт Совместимо Совместимо
IS Конфликт Совместимо Совместимо Совместимо
  • S-блокировка позволяет транзакции, которая удерживает блокировку, читать строку.
  • X-блокировка позволяет транзакции, которая удерживает блокировку, обновлять или удалять строку.
  • IS-блокировка (индикативная блокировка для чтения) указывает, что транзакция намерена установить S-блокировку на отдельные строки в таблице.
  • IX-блокировка (индикативная блокировка для записи) указывает, что транзакция намерена установить X-блокировку на отдельные строки в таблице.

№1 Блокировка при обновлении строк в разных таблицах

T1 T2 Пояснение
BEGIN; Начало первой транзакции
BEGIN; Начало второй транзакции
UPDATE Account a SET a.active = 1 WHERE a.id = 2; Получение X-блокировки на строку первой таблицы
UPDATE AccountBonus ab SET ab.amount = 100 WHERE ab.id = 1; Получение X-блокировки на строку второй таблицы
UPDATE Account a SET a.active = 0 WHERE a.id = 2; Попытка получить X-блокировку на строку первой таблицы, но возникает блокировка ожидания
UPDATE AccountBonus ab SET ab.amount = 100 WHERE ab.id = 1; Попытка получить X-блокировку на строку второй таблицы, но возникает deadlock либо в первой, либо во второй транзакции
COMMIT; Коммит изменений, если deadlock произошел во второй транзакции
COMMIT; Коммит изменений, если deadlock произошел в первой транзакции

Объяснение deadlock

  1. Транзакция 1 получает X-блокировку на строку в первой таблице и пытается обновить строку во второй таблице, но сталкивается с deadlock.
  2. Транзакция 2 получает X-блокировку на строку во второй таблице и пытается обновить строку в первой таблице, но также сталкивается с блокировкой ожидания.
  3. В результате возникает deadlock между транзакциями, и они не могут завершиться до разрешения блокировки.

№2 Блокировка при обновлении строки с использованием shared блокировки

T1 T2 Пояснение
BEGIN; Начало первой транзакции
BEGIN; Начало второй транзакции
SELECT * FROM Account a WHERE a.id = 2 FOR SHARE; Получение S-блокировки на строку таблицы
SELECT * FROM Account a WHERE a.id = 2 FOR SHARE; Получение S-блокировки на ту же строку таблицы
UPDATE Account a SET a.active = 1 WHERE id = 2; Попытка получить X-блокировку на ту же строку, но захвачена блокировка ожидания
UPDATE Account a SET a.active = 1 WHERE a.id = 2; Попытка получить X-блокировку на ту же строку, но возникает deadlock
COMMIT; Коммит не будет выполнен, так как произошла самоблокировка
COMMIT; Коммит, так как вторая транзакция освободила S-блокировку, и обновление было выполнено на пятом шаге.

Объяснение deadlock

  1. Транзакция 1 получает S-блокировку на строку с id = 2, затем пытается получить X-блокировку, но захватывает блокировку ожидания, так как другая транзакция удерживает блокировку.
  2. Транзакция 2 также получает S-блокировку на строку с id = 2.
  3. Ожидая X-блокировку, Транзакция 2 сталкивается с deadlock, так как Транзакция 1 ждет освобождения блокировки S, которая удерживается Транзакцией 2.
  4. Из-за deadlock транзакция 2 не может завершиться.

№3 Deadlock при вставке строки в один и тот же индексный разрыв и откате транзакции

Insert Intention Gap Lock (Блокировка намерения вставки в разрыв)

  • INSERT устанавливает эксклюзивную блокировку на вставляемую строку. Это блокировка записи в индексе (index-record lock), а не next-key блокировка (то есть без gap-блокировки) и не мешает другим сессиям вставлять данные в этот разрыв перед текущей записью.
  • Перед вставкой строки устанавливается gap-блокировка намерения вставки (insert intention gap lock), которая сигнализирует о намерении вставки.
  • Если два процесса вставляют значения в один и тот же индексный разрыв, они не блокируют друг друга, если вставка происходит в разные позиции внутри разрыва. Например, если есть индексы 4 и 7, то вставка 5 и 6 не конфликтует.
  • Если происходит ошибка дубликата ключа, на дублирующуюся запись устанавливается разделяемая блокировка (shared lock).
  • Это может привести к deadlock, если несколько сессий пытаются вставить одинаковую строку, но одна из них уже держит эксклюзивную блокировку.

Схема таблицы

CREATE TABLE Account (
    id BIGINT PRIMARY KEY,
    userId BIGINT,
    currency CHAR(3),
    UNIQUE INDEX uniqUserIdCurrency(userId, currency)
) ENGINE = InnoDB;
T1 T2 Т3 Пояснение
BEGIN; Начало первой транзакции
INSERT INTO Account VALUES(uuid_short(), '123', 'USD'); Получение "insert intention gap lock" на разрыв индекса, затем сразу X-блокировка на вставляемую запись
BEGIN; Начало второй транзакции
INSERT INTO Account VALUES(uuid_short(), '123', 'USD'); Получение "insert intention gap lock" на разрыв индекса, затем ошибка дубликата ключа.
Причина: "insert intention gap lock" уже сигнализирует о вставке в индексный разрыв, даже если коммита не было.
Ошибка дубликата приводит к захвату IS-блокировке и попытке захватить S-блокировку (shared lock) на вставляемую запись.
BEGIN; Начало третьей транзакции
INSERT INTO Account VALUES(uuid_short(), '123', 'USD'); Получение "insert intention gap lock" на разрыв индекса, затем ошибка дубликата ключа.
Причина: "insert intention gap lock" уже сигнализирует о вставке в индексный разрыв, даже если коммита не было.
Ошибка дубликата приводит к захвату IS-блокировке и попытке захватить S-блокировку (shared lock) на вставляемую запись.
ROLLBACK; Транзакция откатывается, X-блокировка снимается
S-блокировка установлена, так как X-блокировка была снята, затем попытка получить IX-блокировку (intention exclusive lock).
Происходит deadlock, так как третья транзакция уже удерживает IS-блокировку (intention shared lock).

Объяснение deadlock

  1. Транзакция 1 вставляет строку (userId = '123', currency = 'USD'), получает insert intention gap lock и эксклюзивную X-блокировку на запись.
  2. Транзакция 1 откатывается, снимая X-блокировку.
  3. Транзакция 2 пытается вставить ту же запись, получает insert intention gap lock, но из-за ошибки дубликата пытается получить S-блокировку.
  4. Транзакция 3 делает то же самое, но теперь обе транзакции удерживают конфликтующие блокировки (S и IX).
  5. Deadlock между транзакциями 2 и 3.

№4 Deadlock между INSERT ... ON DUPLICATE KEY UPDATE

Next-key lock — это комбинация блокировки записи (record lock) на запись в индексе и блокировки разрыва (gap lock) на промежуток перед этой записью в индексе.

  • При обновлении по первичному или уникальному ключу и уникальных данных: Устанавливается эксклюзивная блокировка записи (X index-record lock).
  • При обновлении по уникальному ключу, но при неуникальных данных: Устанавливается эксклюзивная next-key блокировка (X next-key lock).

Схема таблицы

CREATE TABLE _infos (
    id BIGINT PRIMARY KEY,
    mid INT,
    username INT,
    email INT,
    address INT,
    UNIQUE KEY mid_username_email_address_UK (mid, username, email, address)
);
T1 T2 Пояснение
BEGIN; Начало первой транзакции
BEGIN; Начало второй транзакции
INSERT INTO _infos (id, mid, username, email, address)
VALUES (100, 1, 99, 203455, 183)
ON DUPLICATE KEY UPDATE email=VALUES(email)
Получение insert intention gap lock
INSERT INTO _infos (id, mid, username, email, address)
VALUES (300, 1, 99, 203455, 183)
ON DUPLICATE KEY UPDATE email=email;
[lock 1] Ожидание эксклюзивной next-key блокировки
[lock 2] Ожидание record lock
INSERT INTO _infos (id, mid, username, email, address)
VALUES (200, 1, 12, 20, 9998)
ON DUPLICATE KEY UPDATE email=VALUES(email);
Ожидание X next-key lock
Возникает deadlock

Транзакция 1

  1. Начинает транзакцию.
  2. Выполняет первую команду INSERT ... ON DUPLICATE KEY UPDATE, вставляя (100, 1, 99, 203455, 183).
    • Перед вставкой эта операция приводит к выдаче insert intention gap lock.
  3. Пытается выполнить еще одну команду INSERT ... ON DUPLICATE KEY UPDATE с другими значениями, которые попадают в тот же самый диапазон индекса (200, 1, 12, 20, 9998) после того, как первая вставка была выполнена в Транзакции 2.
    • Перед вставкой эта операция приводит к выдаче insert intention gap lock.
    • Вставляемые данные в зазоре уникальные, но возможно конфликтуют по unique key, поэтому транзакция запрашивает X next-key lock на диапазон.
    • В этот момент возникает deadlock, так как выполнение этого запроса требует установки X next-key lock в том же диапазоне, где уже находятся lock 1 и lock 2.

Транзакция 2

  1. Запускает свою собственную транзакцию.
  2. Пытается вставить ту же самую запись (300, 1, 99, 203455, 183), что и в Транзакции 1, используя INSERT ... ON DUPLICATE KEY UPDATE.
    • Вставляемые данные в зазоре от первой транзакции уникальные и не конфликтуют по unique key это переводит insert intention gap lock от первой вставки в X index-record lock.
  3. Теперь Транзакция 2 нуждается в X next-key lock (lock 1) и ожидает ее,
    • Одновременно пытаясь получить X index-record lock (lock 2), которая уже удерживается Транзакцией 1.

References

  1. https://habr.com/ru/articles/238513/
  2. https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
  3. https://medium.com/@carolyn_chen/unlocking-mysql-real-case-studies-on-deadlocks-and-their-causes-2faab5bc0920
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment