Deadlock MySQL

Блокування та рівні ізоляції транзакцій InnoDB у MySQL

Базові поняття

Усі, гадаю, вже знають, що InnoDB використовує блокування на рівні рядків. Залежно від рівня ізоляції транзакції можуть блокуватися як рядки, що потрапили в результуючу вибірку, так і всі рядки, що були переглянуті під час пошуку. Наприклад, у REPEATABLE READ блокувальний запит без використання індексу потребуватиме перебору всієї таблиці, а отже і блокування всіх записів. Тому пам'ятай, %username%, правильний вибір індексів безпосередньо впливає на швидкість роботи блокувань.

Є два базових типи блокувань:

• shared lock - спільне блокування, дає змогу іншим транзакціям читати рядок і ставити на нього таке саме спільне блокування, але не дає змоги змінювати рядок або ставити виняткове блокування.
• exclusive lock - виняткове блокування, забороняє іншим транзакціям блокувати рядок, а також може блокувати рядок як на запис, так і на читання залежно від поточного рівня ізоляції (про які нижче).

Якщо копнути глибше, то з'ясується, що є ще 2 типи блокувань, назвемо їх блокуваннями «про наміри». Не можна просто так узяти і заблокувати запис в InnoDB. Блокування intention shared і intention exclusive є блокуваннями на рівні таблиці й блокують тільки створення інших блокувань і операції на всій таблиці типу LOCK TABLE. Накладення такого блокування транзакцією лише повідомляє про намір цієї транзакції отримати відповідне спільне або виключне блокування рядка.

Якщо накладене на рядок блокування не дає змоги виконати операцію, то транзакція просто чекає зняття блокування, що заважає. У разі перехресних блокувань чекати можна довго, це deadlock. У документації MySQL є кілька порад про те, як уникнути взаємних блокувань.

InnoDB накладає блокування не на самі рядки з даними, а на записи індексів. Те чи інше блокування може накладатися на:

• record lock - блокування запису індексу
• gap lock - блокування проміжку між, до або після індексного запису
• ext-key lock - блокування запису індексу та проміжку перед ним

Блокування проміжків потрібне для того, щоб уникнути появи фантомних записів, коли, наприклад, між двома однаковими читаннями діапазону сусідня транзакція встигає вставити запис у цей діапазон.

Усе описане вище визначається InnoDB неявно, вам потрібно лише уявляти, що відбувається «під капотом». Про те, які саме запити накладають блокування, можна подивитися знову ж таки в документації.

Ну і перед тим як перейти до рівнів ізоляції, розглянемо поняття узгодженого читання (consistent read). У момент першого запиту в транзакції створюється снепшот даних БД (т.зв. read view), на який не впливають зміни в паралельних транзакціях, але впливають зміни в поточній. Читання з такого снепшота називають неблокуючим узгодженим читанням. Неблокувальним, тому що для створення такого снепшота не потрібне навішування блокувань, узгодженим, тому що жодні катаклізми в зовнішньому світі (крім DROP TABLE й ALTER TABLE) не вплинуть на затишний світ снепшота. InnoDB можна попросити зробити снепшот і до першого запиту в транзакції, для цього потрібно згадати про це під час старту транзакції - START TRANSACTION WITH CONSISTENT SNAPSHOT.

Рівні ізоляції транзакцій InnoDB

Рівень ізоляції можна змінити запитом SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.

REPEATABLE READ (значення за замовчуванням)

• Узгоджене читання (SELECT) нічого не блокує, читає рядки зі снепшота, який створюється під час першого читання в транзакції. Однакові запити завжди повернуть однаковий результат.
• Для блокуючого читання (SELECT... FOR UPDATE/LOCK IN SHARE MODE), UPDATE і DELETE блокування буде залежати від типу умови. Якщо умова унікальна (WHERE id=42), то блокується тільки знайдений індексний запис (record lock). Якщо умова з діапазоном (WHERE id > 42), то блокуються весь діапазон (gap lock або next-key lock).

READ COMMITED

• Узгоджене читання нічого не блокує, але щоразу відбувається зі свіжого снепшота.
• Блокуюче читання (SELECT... FOR UPDATE/LOCK IN SHARE MODE), UPDATE і DELETE блокує тільки шукані індексні записи (record lock). Таким чином можлива вставка паралельним потоком записів у проміжки між індексами. Проміжки блокуються (gap lock) тільки під час перевірок зовнішніх ключів і ключів, що дублюються. Також блокування просканованих рядків (record lock), що не задовольняють WHERE, знімають одразу ж після обробки WHERE.

READ UNCOMMITED (найслабший рівень)

• Усі запити SELECT читають у не блокувальній манері. Зміни незавершеної транзакції можуть бути прочитані в інших транзакціях, а зміни ці можуть бути ще й згодом відкочені. Це так зване «брудне читання» (неузгоджене).
• В іншому все так само як і при READ COMMITED.

SERIALIZABLE (найсуворіший рівень)

• Аналогічний REPEATABLE READ, за винятком одного моменту. Якщо autocommit вимкнений (а під час явного старту транзакції він вимкнений), то всі прості запити SELECT неявно перетворюються на SELECT... LOCK IN SHARE MODE, якщо ввімкнено - кожен SELECT йде в окремій транзакції. Використовується, як правило, для того, щоб перетворити всі запити читання на SELECT... LOCK IN SHARE MODE, якщо цього не можна зробити в коді програми.

І наостанок пара згаданих у тексті штук, про які слід знати.

SELECT... LOCK IN SHARE MODE - блокує зчитувані рядки на запис. Інші сесії можуть читати, але чекають закінчення транзакції для зміни порушених рядків. Якщо ж у момент такого SELECT'а рядок уже змінено іншою транзакцією, але ще не зафіксовано, то запит чекає закінчення транзакції й потім читає свіжі дані. Ця конструкція потрібна, як правило, для того, щоб отримати найсвіжіші дані (незалежно від часу життя транзакції) і заразом переконатися в тому, що їх ніхто не змінить.

SELECT... FOR UPDATE - блокує зчитувані рядки на читання. Точно таке саме блокування ставить звичайний UPDATE, коли зчитує дані для оновлення.

Зверніть увагу, що такі запити читають уже не зі снепшота, як простий SELECT, тобто вони побачать зміни, зафіксовані іншою транзакцією після початку поточної. Так відбувається тому, що InnoDB може заблокувати тільки останню версію рядка, а в снепшоті вона не обов'язково буде останньою.

Наприклад, у сценарії зчитати → змінити → записати назад між зчитати й записати паралельна транзакція може змінити дані, але цю зміну буде тут же затерто поточною транзакцією під час запису назад. LOCK IN SHARE MODE у цьому прикладі не дасть вклинитися сусідній транзакції, їй доведеться почекати. Зауважте, що в цьому разі блокування ставитиметься двічі, спочатку спільне блокування під час читання, потім виняткове під час запису. Оскільки блокувань два, то є теоретичний шанс проскочити третім між ними й викликати deadlock.

Відмінність FOR UPDATE у тому, що він одразу ставить виняткове блокування, таке саме, як і звичайний UPDATE. Таким чином для сценарію зчитати → змінити → записати назад блокування ставитиметься тільки один раз у момент зчитування. Такий варіант знизить імовірність виникнення взаємних блокувань.