Що таке блокування проміжків (gap lock) і next-key lock і чому INSERT раптом чекає?

Question

Accepted Answer

InnoDB блокує не лише існуючі рядки, а й проміжки між записами індексу. Так на рівні REPEATABLE READ він захищається від фантомів - рядків, що «з'являються» між двома блокувальними читаннями однієї транзакції.
Види блокувань:

record lock - блокування одного запису індексу;
gap lock - блокування проміжку між записами (без самих записів). Забороняє лише вставку в цей проміжок;
next-key lock - запис плюс проміжок перед ним. Саме так InnoDB блокує при пошуку й скануванні індексу на REPEATABLE READ.

Приклад, що дивує:
-- у таблиці є рядки з age = 20 і age = 30, індекс на age
-- сесія A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 21 AND 29 FOR UPDATE;  -- 0 рядків

-- сесія B
INSERT INTO users (age) VALUES (25);    -- чекає!
INSERT INTO users (age) VALUES (35);    -- може теж чекати: next-key lock
                                        -- на запис 30 разом із проміжком

Сесія A не знайшла жодного рядка, але заблокувала проміжок, щоб повторний запит дав той самий результат.
Коли блокування ширше, ніж очікуєш:

немає індексу на колонці з умови - сканується вся таблиця, і блокуються всі записи з проміжками. UPDATE ... WHERE email = ? без індексу на email фактично блокує таблицю для вставок;
неунікальний індекс - блокуються проміжки з обох боків знайдених записів;
пошук за унікальним індексом з рівністю блокує лише сам запис, без проміжку.

Gap locks не конфліктують між собою - дві транзакції можуть тримати блокування одного проміжку. Звідси класичне взаємоблокування: обидві роблять SELECT ... FOR UPDATE за неіснуючим ключем, обидві отримують gap lock, обидві намагаються вставити - deadlock.
READ COMMITTED вимикає gap locks для пошуку й сканування (лишаються лише для перевірки зовнішніх ключів і дублікатів). Блокувань і взаємоблокувань менше, але фантоми можливі.
Як побачити блокування:
SELECT engine_transaction_id, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

lock_mode вигляду X,GAP, X,REC_NOT_GAP чи просто X (next-key) показує, що саме тримає транзакція.
Докладніше в документації: Блокування next-key і фантомні рядки

Що таке блокування проміжків (gap lock) і next-key lock і чому INSERT раптом чекає?

Схожі питання