MySQL InnoDB synthetic lock

mysql_lock.md

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

	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