MySQL проти MariaDB: які взаємні блокування простіше відлагодити, коли сайт горить

Чіткий вердикт: хто простіший для налагодження

Якщо ви використовуєте сучасний MySQL (8.0+), MySQL зазвичай простіший для налагодження взаємних блокувань і очікувань блокування в моменті,
тому що Performance Schema — більш послідовне й багате джерело істини: хто тримає який лок, хто чекає, який SQL-текст,
які індекси, які транзакції. Це не ідеально, але це операційно цілісно. Ви можете набити м’язову пам’ять і надійні рукбуки.

MariaDB теж може бути добре відлагоджуваною — особливо якщо дисципліновано вмикати потрібну інструментацію та логи — але на практиці легше
опинитися з частковою видимістю: достатньо, щоб знати, що ви застряли, але недостатньо, щоб зрозуміти чому.
Також: екосистема MariaDB має більше варіацій (різні форки/рушії/шари реплікації як Galera трапляються частіше),
тож ваша «стандартна» історія про взаємні блокування менш стандартна.

Ось практичне правило, яким я користуюся:

• Якщо у вас вже є дашборди та рукбуки на базі Performance Schema, MySQL 8.0 зазвичай виграє за швидкістю до кореня проблеми.
• Якщо ви на MariaDB з Galera, ви дебагуєте не лише InnoDB-deadlock-и — часто йдеться про сертифікаційні конфлікти, які для додатку виглядають як deadlock і відчуваються гірше.
• Якщо ви покладаєтесь лише на «SHOW ENGINE INNODB STATUS», рано чи пізно обидва вас підведуть. Це артефакт останньої інцидентної стадії, а не стратегія моніторингу.

Суб’єктивна порада: для команд, що не мають зрілої спостережуваності бази даних, обирайте платформу, яка дає відповіді з найменшою кількістю хаків.
Це зазвичай MySQL 8.0 з Performance Schema та адекватним логуванням.

Одна цитата, яка має бути в кожному оперсі:
«Надія — це не стратегія.» — генерал Гордон Р. Салліван

Жарт #1: Взаємне блокування — це просто дві транзакції, що провели зустріч і погодились ніколи не йти на компроміс.

Цікаві факти та історичний контекст (те, що важливо о 03:00)

• MariaDB з’явилася у 2009 році після того, як Oracle придбав Sun (а отже — MySQL). Рішення про форк досі відгукується в інструментах і налаштуваннях за замовчуванням.
• InnoDB став рушієм за замовчуванням у MySQL 5.5. Старі системи, що «виросли» до 5.5, часто зберігають звички епохи MyISAM, які призводять до сучасних проблем з блокуваннями.
• MySQL 8.0 зробив Performance Schema центром інструментації. Саме тому сучасні MySQL-рукбуки починаються звідти.
• MariaDB історично замінила InnoDB на XtraDB (а згодом знову наблизилась). У залежності від версії ви побачите різні назви змінних і відмінності в поведінці інструментації.
• Взаємні блокування — це знак правильності, а не збою: InnoDB виявляє цикли і відкачує «жертву», щоб система рухалась далі. Помилка там, де ваш додаток не вміє коректно повторювати операцію.
• Gap locks і next-key locks часто є «сюрпризною» причиною блокувань під REPEATABLE READ. Багато команд дізнаються про них лише після інциденту.
• Реплікація у стилі Galera (поширена у розгортаннях MariaDB) може повертати помилки конфлікту, які на рівні застосунку нагадують deadlock, навіть без класичного InnoDB-циклу.
• Sys schema у MySQL (шари допоміжних view над Performance Schema) зробила «що зараз відбувається» запити простішими для людей; це змінило поведінку операторів під тиском.

Як виглядають взаємні блокування в реальному продакшні

Два режими відмов, які оператори плутають (і не повинні)

В інцидентному чаті ви почуєте слово «deadlock» у двох різних значеннях:

1. Взаємне блокування (цикл): InnoDB виявляє цикл і швидко відміняє одну транзакцію. Ви бачите помилку 1213: Deadlock found when trying to get lock.
2. Таймаут очікування блоку: циклу немає, просто довге очікування. Зрештою очікувач зупиняється з помилкою 1205: Lock wait timeout exceeded.

Операційно це потребує різних реакцій. Справжній deadlock часто є шаблоном (два шляхи коду беруть блоки в різному порядку).
Таймаут очікування зазвичай означає гарячий рядок / гарячий індекс / довгу транзакцію, інколи разом з відсутнім індексом.
Обидва можуть відбуватися одночасно під час простою, і тоді ви починаєте лаяти не ту діаграму.

Чому «та це ж просто два UPDATE» — пастка

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

Що робить взаємні блокування болючими під навантаженням

• Шторми повторних спроб: додаток агресивно повторює транзакції після deadlock, погіршуючи контенцію.
• Довгі транзакції: одна повільна транзакція тримає блоки довше, збільшуючи ймовірність циклів.
• Колапс черг: потоки накопичуються в очікуванні блокувань; латентність зростає нелінійно; виснаження пула слідує.
• Затримка реплікації: на асинхронних репліках довгі транзакції і очікування блоку ускладнюють застосування; на напівавтоматичних вони можуть уповільнювати підтвердження.

Жарт #2: Якщо хочете, щоб дві команди погодили порядок захоплення блоків, скажіть їм, що це обов’язково, і призначте нараду — deadlock гарантовано.

Телеметрія та інструменти: MySQL проти MariaDB під тиском

Перевага MySQL: одна розповідь інструментації для всіх

Performance Schema в MySQL 8.0 — це місце, куди йти, щоб відповісти «хто блокує кого» без гадань. Ключова операційна перевага — послідовність: ті самі таблиці, ті самі шляхи джоінів, однакова ментальна модель на хості.
Коли сайт горить, послідовність — це швидкість.

Зазвичай ви можете отримати:

• поточні очікувачі і блокувальники (performance_schema.data_lock_waits)
• інвентар блокувань (performance_schema.data_locks)
• метадані транзакцій (information_schema.innodb_trx)
• SQL-текст і дайджести запитів (events_statements_current, підсумки дайджестів)

MariaDB: може бути добре, але дивіться версію й шлях рушія

У MariaDB також є Performance Schema, але оперативно частіше трапляється, що воно вимкнене, частково ввімкнене або на нього менше покладаються.
У MariaDB часто використовують Information Schema views, як-от INFORMATION_SCHEMA.INNODB_LOCK_WAITS (де доступно),
і, звісно, старий боєць: SHOW ENGINE INNODB STATUS.

Якщо у вашому ландшафті MariaDB є Galera, потрібна додаткова ментальна модель: не кожне «транзакція відхилена» — це InnoDB-deadlock.
Часто це конфлікти на рівні реплікації (write set). Здатність відлагодити залежить від того, чи збираєте ви ці статистики і зіставляєте їх із поведінкою застосунку.

Різниці в логуванні, що вирішують, чи знайдете винуватця за хвилини чи години

Звіти про взаємні блокування корисні лише за правильної конфігурації. І в MySQL, і в MariaDB бажано:

• увімкнене логування deadlock-ів (InnoDB друкує деталі в error log)
• мітки часу + ідентифікатори потоків, узгоджені з логами додатку
• slow log і/або дані statement digest, щоб прив’язати deadlock до форми запиту, а не лише до одноразового SQL-фрагмента

Простими словами: звіт про deadlock без ідентифікації шляху коду — це дорогий печиво з передбаченням.

Швидкий план діагностики

Це послідовність «перестаньте гадати». Вона навмисно коротка. Під навантаженням першочергове — визначити:
(1) чи це хвиля deadlock-ів чи черга lock-wait, (2) які таблиці/індекси гарячі, (3) яка транзакція — булі.

Перший крок: підтвердьте, який у вас біль

1. Перевірте рівні помилок у додатку: бачите 1213 (deadlock) чи 1205 (timeout)?
2. Перевірте лічильники InnoDB: чи швидко ростуть lock waits? Чи зростає history list length (довгі транзакції)?
3. Підтвердьте стан пула потоків/з’єднань: потоки застрягли в «Waiting for table metadata lock» чи в очікуванні рядкового локу?

Другий крок: знайдіть блокувальника(ів) і гарячі об’єкти

1. Перелічіть очікування блокувань і ідентифікуйте ID блокуючих транзакцій.
2. Зіставте блокувальників з SQL-текстом та клієнтом/користувачем/хостом.
3. Визначте таблицю та індекс. Якщо це діапазонний скан по другорядному індексу — припускайте, що шлях доступу винен.

Третій крок: виберіть дію для стримування

1. Убийте одну найгіршу блокуючу транзакцію, якщо це безпечно (зазвичай довга пакетна робота або застряглий запит обслуговування).
2. Тимчасово зменшіть конкурентність проблемного endpoint/типу воркерів.
3. Підкоригуйте поведінку повторних спроб: експоненціальне зворотне відстеження з джиттером і обмеження кількості повторів. Не створюйте DDOS через повтори.

Тільки після стримування робіть «гарну» роботу: індексування, переписування запитів, перегляд порядку блокувань, рівня ізоляції.

Практичні завдання: команди, виводи, рішення (12+)

Сенс цих завдань не в збиранні тривіальної інформації. Мета — перетворити містерію в рішення: «вбити це», «тротлити те», «переписати цей запит»,
«додати цей індекс» або «змінити межі транзакції». Кожне завдання включає: команду, що означає її вивід, і що робити далі.

Завдання 1: підтвердьте рушій/версію і чи ви взагалі в одній грі

cr0x@server:~$ mysql -uroot -p -e "SELECT VERSION() AS version, @@version_comment AS comment, @@innodb_version AS innodb_version\G"
*************************** 1. row ***************************
version: 8.0.36
comment: MySQL Community Server - GPL
innodb_version: 8.0.36

Значення: Версія повідомляє, які таблиці інструментації існують і які налаштування за замовчуванням застосовуються.

Рішення: Якщо ви на MySQL 5.7 / старішій MariaDB, готуйтеся до менших можливостей для живої інспекції lock-ів; більше покладайтесь на логи і SHOW ENGINE INNODB STATUS.

Завдання 2: перевірте, чи маєте справу з deadlock-ами або таймаутами на рівні сервера

cr0x@server:~$ mysql -uroot -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_deadlocks'; SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_timeouts';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Innodb_deadlocks| 482   |
+-----------------+-------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_row_lock_timeouts  | 91    |
+---------------------------+-------+

Значення: Швидке зростання deadlocks вказує на конфлікт у порядку блокувань; таймаути — на довгі очікування і гарячі точки.

Рішення: Якщо Innodb_deadlocks підіймається стрімко, пріоритет — ідентифікувати два типи SQL-запитів. Якщо таймаути зросли, шукайте найдовшу транзакцію та відсутні індекси.

Завдання 3: зафіксуйте останній сюжет deadlock-а (швидко, але не вичерпно)

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,+80p'
LATEST DETECTED DEADLOCK
------------------------
2025-12-29 10:41:12 0x7f1c4c1fe700
*** (1) TRANSACTION:
TRANSACTION 123456789, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 9012, OS thread handle 139759, query id 882199 appdb 10.2.3.44 appuser updating
UPDATE orders SET status='paid' WHERE id=778812
*** (2) TRANSACTION:
TRANSACTION 123456790, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 9013, OS thread handle 139760, query id 882200 appdb 10.2.3.45 appuser updating
UPDATE orders SET status='shipped' WHERE id=778812
*** WE ROLL BACK TRANSACTION (1)

Значення: Ви отримуєте SQL жертви і конфліктну пару, а також деталі індексу/локів нижче.

Рішення: Якщо два вирази походять із одного шляху коду, перевірте недетермінований порядок або приховані читання (перевірки FK, триґери). Якщо з різних шляхів, вирівняйте порядок захоплення локів або розділіть модель володіння рядками.

Завдання 4 (MySQL 8.0): перелікуйте поточних блокувальників і очікувачів через Performance Schema

cr0x@server:~$ mysql -uroot -p -e "
SELECT
  w.REQUESTING_ENGINE_TRANSACTION_ID AS waiting_trx,
  w.BLOCKING_ENGINE_TRANSACTION_ID  AS blocking_trx,
  dlw.OBJECT_SCHEMA, dlw.OBJECT_NAME, dlw.INDEX_NAME,
  dlw.LOCK_TYPE, dlw.LOCK_MODE, dlw.LOCK_STATUS
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks dlw
  ON w.REQUESTING_ENGINE_LOCK_ID = dlw.ENGINE_LOCK_ID
ORDER BY dlw.OBJECT_SCHEMA, dlw.OBJECT_NAME\G"
*************************** 1. row ***************************
waiting_trx: 123456799
blocking_trx: 123456650
OBJECT_SCHEMA: appdb
OBJECT_NAME: order_items
INDEX_NAME: idx_order_id
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: WAITING

Значення: Це вказує на гарячу таблицю/індекс і ID блокуючої транзакції.

Рішення: Негайно отримайте SQL обох транзакцій і їх тривалість. Якщо блокер «очевидно неправильний» (довга пакетна робота), вбийте його. Якщо це нормальний трафік — маєте структурну проблему з контенцією.

Завдання 5 (MySQL 8.0): зіставте ID транзакцій із сесіями та SQL-текстом

cr0x@server:~$ mysql -uroot -p -e "
SELECT
  t.trx_id, t.trx_started, TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS trx_age_s,
  p.ID AS processlist_id, p.USER, p.HOST, p.DB, p.COMMAND, p.TIME, p.STATE,
  LEFT(p.INFO, 200) AS sql_sample
FROM information_schema.innodb_trx t
JOIN information_schema.PROCESSLIST p
  ON p.ID = t.trx_mysql_thread_id
ORDER BY trx_age_s DESC
LIMIT 10\G"
*************************** 1. row ***************************
trx_id: 123456650
trx_started: 2025-12-29 10:38:01
trx_age_s: 191
processlist_id: 8441
USER: batch
HOST: 10.2.8.19:55312
DB: appdb
COMMAND: Query
TIME: 187
STATE: Updating
sql_sample: UPDATE order_items SET price=price*0.98 WHERE order_id IN (...)

Значення: Ви знайшли «булі»: довгу транзакцію, що тримає блоки.

Рішення: Якщо це некритично — вбийте її. Якщо критично — тротлите конкурентних записувачів і зменшуйте площу блокувань (індекси, дроблення, вужчі предикати) після стримування.

Завдання 6: перевірте налаштування innodb_lock_wait_timeout (і припиніть робити це механічно)

cr0x@server:~$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';"
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+

Значення: Це тривалість очікування перед тим, як транзакція здасться. Воно не запобігає deadlock-ам.

Рішення: Не «виправляйте» deadlock-и, ставлячи це в 1 або 500. Налаштуйте його згідно з бюджетами латентності для користувачів і терпимістю фонів, а потім виправляйте патерн контенції.

Завдання 7: перевірте рівень ізоляції (deadlock-и чутливі до нього)

cr0x@server:~$ mysql -uroot -p -e "SELECT @@transaction_isolation AS isolation;"
+--------------+
| isolation    |
+--------------+
| REPEATABLE-READ |
+--------------+

Значення: REPEATABLE READ може вводити next-key/gap блокування для діапазонних сканів і пошуків по індексу.

Рішення: Якщо deadlock-и пов’язані з діапазонними предикатами (наприклад, WHERE created_at BETWEEN...), розгляньте READ COMMITTED для цього навантаження, але тільки після явної перевірки коректності.

Завдання 8: виявіть накопичення metadata lock (не deadlock, але інцидент схожий)

cr0x@server:~$ mysql -uroot -p -e "SHOW PROCESSLIST;" | sed -n '1,20p'
Id	User	Host	db	Command	Time	State	Info
8123	appuser	10.2.3.12:51221	appdb	Query	45	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN x INT
8124	appuser	10.2.3.13:51222	appdb	Query	44	Waiting for table metadata lock	SELECT * FROM orders WHERE id=... 
8125	appuser	10.2.3.14:51223	appdb	Query	44	Waiting for table metadata lock	UPDATE orders SET ...

Значення: DDL блокує читання/записи через MDL. Це не InnoDB-deadlock; це інцидент зі зміною схеми.

Рішення: Пауза/убивство DDL, якщо безпечно, або перенесіть його на стратегію онлайн-зміни схеми. Не витрачайте час на «налагодження deadlock-ів» тут.

Завдання 9: перевірте довжину history list (тиск undo, довгі транзакції)

cr0x@server:~$ mysql -uroot -p -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/History list length/,+3p'
History list length 987654
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 123456650, ACTIVE 191 sec
... 

Значення: Величезна довжина history list часто означає довгі транзакції, що перешкоджають очищенню purge. Це корелює з інтенсивним churn блокувань і колапсом продуктивності.

Рішення: Знайдіть і виправте довгі транзакції: пакетні завдання, шаблони «read in transaction», або код, що тримає транзакцію відкритою під час виклику зовнішніх сервісів (так, таке трапляється).

Завдання 10: підтвердьте індекси, що використовуються у deadlocking-запитах (шлях доступу часто винен)

cr0x@server:~$ mysql -uroot -p -e "EXPLAIN UPDATE order_items SET price=price*0.98 WHERE order_id IN (101,102,103)\G"
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_items
partitions: NULL
type: range
possible_keys: idx_order_id
key: idx_order_id
key_len: 8
ref: NULL
rows: 45000
filtered: 100.00
Extra: Using where

Значення: Діапазонний скан, що блокує багато записів, — класичний чинник deadlock-ів.

Рішення: Змініть форму запиту (розбити по primary key, приєднатися до тимчасової таблиці з ID, або забезпечити доступ через рівність). Якщо потрібно торкнутися багатьох рядків — серіалізуйте цей тип навантаження.

Завдання 11 (MySQL 8.0): отримайте statement digest-и, щоб знайти головних порушників блокувань

cr0x@server:~$ mysql -uroot -p -e "
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_LOCK_TIME/1000000000000 AS sum_lock_time_s,
  SUM_TIMER_WAIT/1000000000000 AS sum_total_time_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_LOCK_TIME DESC
LIMIT 5\G"
*************************** 1. row ***************************
DIGEST_TEXT: UPDATE `order_items` SET `price` = `price` * ? WHERE `order_id` IN ( ... )
COUNT_STAR: 11922
sum_lock_time_s: 843.1200
sum_total_time_s: 910.5523

Значення: Дає вам повторювану сімейство запитів-правопорушників, а не один випадковий приклад.

Рішення: Виправляйте запит з найбільшою сумарною lock-часом першочергово. Там ви купуєте зниження частоти інцидентів.

Завдання 12: перевірте конфігурацію логування deadlock-ів

cr0x@server:~$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';"
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_print_all_deadlocks| OFF   |
+---------------------------+-------+

Значення: Якщо OFF, ви отримуєте лише останній deadlock у InnoDB status; під час хвиль ви можете втратити історію.

Рішення: Увімкніть його в середовищах, де форензика deadlock-ів важлива і обсяг логів прийнятний. Якщо не можете дозволити шум у error log, покладайтесь на Performance Schema, де доступно.

Завдання 13: ідентифікуйте й убийте блокуючу сесію (хірургічно, не панічно)

cr0x@server:~$ mysql -uroot -p -e "KILL 8441;"
Query OK, 0 rows affected (0.00 sec)

Значення: Сесія 8441 завершена; її транзакція відкатується, звільняючи блоки.

Рішення: Робіть це лише після підтвердження, що це блокер, і вартість відкату прийнятна. Якщо транзакція змінила мільйони рядків, її вбивство може тимчасово погіршити навантаження на I/O та undo.

Завдання 14: перевірте затримку реплікації та стан apply thread (deadlock-и можуть бути симптомом upstream)

cr0x@server:~$ mysql -uroot -p -e "SHOW REPLICA STATUS\G" | sed -n '1,35p'
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 420
Last_SQL_Error: 
Replica_SQL_Running_State: Waiting for dependent transaction to commit

Значення: Лаг вказує, що транзакції повільно застосовуються, часто через очікування блоків або великі транзакції.

Рішення: Якщо лаґ зростає під час хвилі deadlock-ів, очікуйте застарілих читань на репліках і тимчасово спрямовуйте менше запитів на них або зупиніть непотрібні write-heavy роботи.

Завдання 15: перевірте насичення на рівні ОС (бо «deadlock» може маскуватися під повільний диск)

cr0x@server:~$ iostat -x 1 3
Linux 6.1.0 (db01) 	12/29/2025 	_x86_64_	(16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          12.31    0.00    6.88   34.22    0.00   46.59

Device            r/s     w/s   rkB/s   wkB/s  await  svctm  %util
nvme0n1         120.0   980.0  4096.0 32768.0  48.10   0.85  92.30

Значення: Високий iowait і великий await вказують на затримку зберігання. Повільний I/O подовжує час транзакцій, підвищуючи ймовірність deadlock-ів.

Рішення: Якщо вузьке місце — зберігання, ваша «проблема deadlock» частково інфраструктурна: зменшіть write amplification (батчинг, перегляд індексів) і вирішіть проблему I/O контенції.

Три міні-історії з корпоративного життя

Інцидент №1: просто припущення, яке спричинило outage

Середня торгова платформа мігрувала зі старого MySQL на кластер MariaDB, бо «це майже те саме і нам подобається open source governance».
Міграція пройшла гладко. Тести продуктивності виглядали добре. Всі святкували відповідально, призначивши cutover на будній ранок.

Неправильне припущення було тонким: команда вважала, що їхня логіка повторів — написана роками раніше — «надійно» обробляє deadlock-и, бо вона повторює при
загальних помилках бази даних. Вона дійсно повторювала. Миттєво. Без backoff. І повторювала транзакції, які фактично не були ідемпотентними, коли часткова робота вже
відбулася на рівні додатку.

В день cutover вони натрапили на шаблон конфліктів записів, якого раніше не бачили. Кілька гарячих рядків (лічильники запасів і стани замовлень) стали магнітами контенції.
База зробила те, що має робити: виявила deadlock-и і відмнила жертв. Додаток зробив те, що роблять недоглянуті додатки: миттєво повторив усе, паралельно, без роздумів про побічні ефекти для користувача.

Сайт не впав одразу. Він уповільнився. Потім воркер-пули наситились. Потім пул з’єднань заповнився. Потім перевірки здоров’я почали падати.
Ззовні це виглядало як «база даних безперервно deadlock-иться». З середини це було «додаток підсилює нормальний механізм безпеки в DoS».

Виправлення не було «повернутися на MySQL». Виправлення — нудна інженерія: робити повтори умовними, використовувати експоненціальний backoff з джиттером, капати повтори,
і зробити транзакцію дійсно ідемпотентною (або спроектувати компенсаційну дію). Вони також ввели бюджет deadlock-ів на endpoint: якщо бюджет перевищено,
endpoint починає тротлити, перш ніж база буде змушена.

Інцидент №2: оптимізація, що зіграла злий жарт

SaaS-аналітична компанія мала повільну роботу генерації звітів. Вона оновлювала таблицю «report_status» для мільйонів користувачів.
Хтось помітив класичну неефективність: «Занадто багато комітів. Давайте загорнемо всю роботу в одну транзакцію для швидкості».
На тихій стейджинг-базі це виглядало блискуче. Одна транзакція, один коміт, менше накладних витрат. Графіки кивнули ввічливо.

В продакшні ця «оптимізація» перетворилася на монстра, що тримає блоки. Транзакція виконувалася хвилини, інколи довше.
Поки вона працювала, вона тримала блоки на записах другорядних індексів і спричиняла черги для інших робочих навантажень — особливо для запитів, орієнтованих на користувача.
Deadlock-и зросли, бо кожна інша транзакція мала більше часу, щоб зіткнутися з довгою транзакцією.

Патерн інциденту був підступний: не одна драматична помилка, а ескалуюча спіраль латентності. Запити клієнтів сповільнились.
Фонові завдання повторювалися. Лаг реплікації зростав. Оператори вбили задачу; відкат тривав; система стала виглядати гіршою перед тим, як покращитись.
Команда спробувала найгірше «виправлення»: підняла innodb_lock_wait_timeout. Тепер транзакції чекали довше, щоб впасти, тож конкурентність залишалася високою, а прогрес знижувався.

Справжнє виправлення було нудним, але правильним: розбити роботу на дрібні детерміновані батчі, комітити кожні N рядків,
і впорядкувати оновлення за первинним ключем, щоб уніфікувати порядок захоплення локів. Вони прийняли трохи більше витрат на коміт, але отримали передбачувані часи тримання локів.
Задача стала трохи повільнішою сама по собі і значно швидшою для бізнесу, бо сайт залишився чутливим.

Урок: будь-яка оптимізація, що збільшує тривалість транзакції, — це оптимізація проти вашої власної доступності.

Інцидент №3: нудна практика, що врятувала день

Фінтех-команда використовувала MySQL 8.0 зі суворою операційною гігієною. Нічого екзотичного: Performance Schema увімкнено, error logs централізовано і пошуково,
дашборди для lock waits і стандартний «інцидентний пакет запитів» у спільному репозиторії. Кожен на відповіді відпрацьовував пакет хоча б раз на game day.

Одного дня звичайний випуск фічі спричинив раптове зростання deadlock-ів на таблиці, що тримала стан верифікації користувача.
Клієнти не могли завершити onboarding. Тікети підтримки накопичувалися. На чергуванні зайшли.

Вони не почали з дебатів про рівні ізоляції чи звинувачування ORM. Вони запустили запит lock-wait до Performance Schema, ідентифікували блокуючий дайджест,
і одразу побачили новий формат запиту: UPDATE з діапазонним предикатом по timestamp-у, без індексу, що виконувався в транзакції, яка також читала з іншої таблиці.
Той запит не просто був повільним — він блокував діапазони.

Контейнмент був простим: відкотити фічу через feature flag і тротлити фоновий воркер, який гамселив ту ж таблицю.
Зменшивши тиск, додали відсутній композитний індекс і переписали транзакцію, щоб захоплювати локи в послідовному порядку.
Інцидент завершився швидко, а постмортем був досить коротким.

Практика, що їх врятувала, була не героїчною. Це була рутинна інструментація, рутинні рукбуки і дисципліна тримати обсяг транзакцій малим.
Нудне — добре. Нудне масштабується.

Запобігання взаємним блокуванням без самообману

1) Зробіть взаємні блокування опрацьованою ситуацією, а не несподіваним винятком

Взаємні блокування будуть у будь-якій достатньо конкурентній системі. Ваше завдання — зробити їх:
(a) рідкісними, (b) дешевими і (c) нешкідливими. Тобто повтори мають бути безпечними.
Якщо не можете повторювати безпечно, ваша модель даних і межі транзакцій невірні для конкуренції.

2) Тримайте транзакції короткими, детермінованими й локальними

Найчастіший прискорювач deadlock-ів — час. Кожен мілісекунд, протягом якого ви тримаєте локи, — ще один квиток у лотерею колізій.
Зменшуйте область транзакції. Не тримайте транзакцію відкритою, поки ви:

• звертаєтесь до зовнішніх сервісів
• рендерите шаблони
• чекаєте у черзі
• виконуєте невідіндексовані читання, які «плануєте оновити пізніше»

3) Використовуйте послідовний порядок захоплення локів у всіх шляхах коду

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

• Завжди оновлювати батька перед дитиною (або навпаки), але виберіть один порядок і дотримуйтесь його.
• При оновленні кількох рядків сортуйте ID і оновлюйте в тому порядку.
• Переважайте оновлення одиночного рядка по первинному ключу, де можливо.

4) Проєктуйте так, щоб уникати гарячих точок

Деякі таблиці народжуються «гарячими»: лічильники, рядки «поточний стан», таблиці лідербордів, сховища сесій.
Гарячі точки спричиняють і deadlock-и, і таймаути, бо концентрують запис. Варіанти:

• розшарування за ключем (навіть у межах однієї БД через партиціонування або application-level bucketing)
• використовуйте append-only події і обчислюйте агрегати асинхронно
• уникайте «глобальних» лічильників; використовуйте лічильники на сутність і агрегуйте

5) Не «виправляйте» deadlock-и зниженням рівня ізоляції як рефлекс

Перехід на READ COMMITTED може зменшити деякі gap-lock пов’язані deadlock-и, так. Але він також змінює семантику додатка.
Якщо логіка бізнесу розраховує на узгоджені читання в межах транзакції, ви тільки що придбали новий клас багів.
Робіть це свідомо, з перевіркою коректності і тестами.

Поширені помилки: симптом → корінна причина → виправлення

1) Симптом: spike deadlock-ів одразу після деплою

Корінна причина: новий формат запиту торкається рядків в іншому порядку або додає діапазонний скан через відсутній індекс.

Виправлення: порівняйте statement digest/топ lock time до і після; відкотіть або вмикайте фічу через feature flag; додайте/підправте індекси; забезпечте порядок блокувань у коді.

2) Симптом: багато сесій «Waiting for table metadata lock»

Корінна причина: DDL блокує через MDL, а не InnoDB-deadlock.

Виправлення: зупиніть/вбийте DDL, якщо безпечно; використовуйте методи онлайн-зміни схеми; плануйте DDL з guardrails і моніторингом MDL.

3) Симптом: таймаути очікування блокувань, але мало помилок deadlock

Корінна причина: довга транзакція або важкий пакет, гарячі рядки, затримки зберігання, що подовжують транзакції.

Виправлення: знайдіть найдовшу транзакцію, розбийте задачу на частини, додайте відсутні індекси, зменшіть I/O контенцію, і переконайтеся, що додаток не тримає транзакції відкритими без потреби.

4) Симптом: deadlock-и відбуваються «випадково» під навантаженням

Корінна причина: недетермінований порядок захоплення локів (несортовані IN списки, паралельні воркери, що оновлюють перекриваючі набори), або cascade по FK, що блокує додаткові рядки.

Виправлення: сортуйте ключі перед оновленням; серіалізуйте воркерів за partition key; перегляньте FK cascade і триґери; розгляньте явні SELECT … FOR UPDATE з визначеним порядком.

5) Симптом: підвищення innodb_lock_wait_timeout робить ситуацію гіршою

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

Виправлення: знизьте його до вашого бюджету латентності; усуньте блокер; зменшіть контенцію; реалізуйте backpressure на рівні додатка.

6) Симптом: deadlock-и зникають після «додавання індексу», але продуктивність погіршилась

Корінна причина: індекс зменшив контенцію, але збільшив write amplification; ви створили новий гарячий індекс або зробили оновлення важчими.

Виправлення: перевірте вартість запису; видаліть невикористовувані індекси; розгляньте композитні індекси, що відповідають шляхам доступу; зберігайте мінімальний набір індексів для критичних операцій.

7) Симптом: у Galera — «deadlock-подібні» відхилення без явних InnoDB-трас

Корінна причина: сертифікаційні конфлікти реплікації (два вузли комітять конфліктні записи).

Виправлення: зменшіть одночасні мульти-мастер записи; спрямовуйте записи для конкретної сутності на один вузол; перегляньте логіку повторів і моніторинг рівня конфліктів.

Чеклісти / покроковий план

Під час інциденту (перші 15 хвилин)

1. Класифікуйте помилку: 1213 deadlock проти 1205 timeout проти MDL wait. Не змішуйте їх.
2. Швидко зберіть докази: витягніть уривок InnoDB status, топ lock-wait запит і найгірші сесії-порушники.
3. Стримайте: вбийте найбільшого блокера, якщо це безпечно; тротлите проблемних воркерів/endpoint; вимкніть нову фічу, якщо є кореляція.
4. Стабілізуйте повтори: обмежте повтори, додайте backoff і припиніть повтори неідемпотентних робочих потоків.
5. Перевірте інфраструктурні обмеження: затримки зберігання, насичення CPU, тиск на buffer pool. На повільній системі deadlock-и виникають легше.

Після стримування (того ж дня)

1. Знайдіть топ offender digest-и (не одиничні SQL). Виправте найбільшого вкладника.
2. Зменшіть область транзакцій і приберіть шаблони «транзакція під час виконання роботи».
3. Забезпечте порядок блокувань у коді та тестах (так, тести).
4. Огляд індексів: переконайтеся, що предикати відповідають індексам; уникайте непередбачених діапазонних сканів.
5. Оцініть вартість відкату: якщо часто вбиваєте блокери, зрозумійте undo churn і поведінку I/O під час відкатів.

Укріплення (як припинити повторні пейджі)

1. Увімкніть довготривалу спостережуваність deadlock-ів: відправка логів, налаштований Performance Schema, дашборди для lock waits і віку транзакцій.
2. Створіть бюджет deadlock-ів на сервіс: якщо перевищено поріг, авто-тротл або shedding навантаження.
3. Game day: практикуйтеся використовувати ті самі запити і команди вище в синтетичному сценарії контенції.
4. Перегляньте схему: гарячі точки, лічильники, state machines. За потреби переробіть дизайн.

Питання та відповіді

1) Чи є взаємне блокування багом БД?

Зазвичай ні. Це база даних, що робить правильну річ, коли дві транзакції створюють цикл. Баг зазвичай у дизайні конкуренції або в логіці повторів.

2) Що простіше налагоджувати: MySQL чи MariaDB?

За сучасних налаштувань і зрілого використання Performance Schema, MySQL 8.0 зазвичай простіший для швидкого налагодження. MariaDB теж може бути зручним, але видимість більше залежить від розгортання.

3) Чи варто просто увімкнути innodb_print_all_deadlocks?

Якщо ви можете витримати обсяг логів і потрібна форензика — так. Але не вважайте це єдиним інструментом. Під сильним навантаженням логи можуть стати шумом і все одно пропустити ширший патерн.

4) Чому deadlock-и трапляються, якщо ми оновлюємо лише по первинному ключу?

Тому що другорядні індекси і перевірки зовнішніх ключів теж можуть вводити додаткові блокування. Також «по первинному ключу» інколи не є таким фактично, коли ORM втручається.

5) У чому різниця для додатка між deadlock і lock wait timeout?

Deadlock — це миттєвий відкат для розриву циклу; таймаут — система відмовляється після тривалого очікування. Обидва треба обробляти, але таймаути частіше вказують на довгого блокера чи відсутній індекс.

6) Чи вирішує READ COMMITTED deadlock-и?

Воно може зменшити частину gap-lock пов’язаних deadlock-ів, але не усуває категорію deadlock-ів повністю. Також змінює припущення транзакцій. Розглядайте як інженерне рішення, а не перемикач.

7) Чи можна запобігти deadlock-ам, збільшивши innodb_lock_wait_timeout?

Ні. Це впливає на таймаути, а не на виявлення deadlock-ів. Збільшення часто погіршує інциденти, бо потоки довше сидять в очікуванні і накопичують контенцію.

8) Яка найбезпечніша негайна міра під час хвилі?

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

9) Як зрозуміти, що це проблема зміни схеми?

Якщо сесії показують «Waiting for table metadata lock», це MDL. Інструменти для deadlock-ів тут не допоможуть; треба управляти виконанням DDL.

Висновок: наступні кроки, які справді зменшують кількість пейджів

Якщо ви хочете менше інцидентів з взаємними блокуваннями, перестаньте ставитись до них як до міфічної проблеми і почніть розглядати їх як
вимірювану, класифіковану вартість конкуренції. MySQL 8.0 робить це простіше з коробки завдяки Performance Schema.
MariaDB теж це підтримує, але вам доведеться суворіше вмикати і стандартизувати потрібну телеметрію — особливо в змішаних топологіях.

Практичні наступні кроки:

1. Побудуйте двошляховий рукбук: deadlock (1213) проти timeout (1205) проти MDL. Різні інструменти, різні виправлення.
2. Увімкніть і перевірте спостережуваність: логування deadlock-ів, Performance Schema таблиці, які ви фактично запитуєте, і пошукові логи.
3. Виправте топ offender digest за сумарним lock-часом, а не найстрашніший одиночний запит, який ви випадково помітили.
4. Зробіть повтори адекватними: ідемпотентні транзакції, backoff з джиттером і обмеження. Припиніть шторми повторів.
5. Скоротіть транзакції і забезпечте порядок захоплення локів. Це і є робота. І це також перемога.