Дедлоки в MySQL — від страху до розуміння

💡У цій статті всі наведені приклади та вся інформація стосуватимуться MySQL (InnoDB), в інших базах даних механізм дедлоків може відрізнятись!

Привіт, спільното! Мене звати Сергій Стець, я Lead Software Engineer у компанії GlobalLogic. Зараз спеціалізуюся на Java, Spring, AWS, SQL/NoSQL, маю досвід менторства, також проводжу інтерв’ю в рамках Trusted Interviewer Program.

Починав свій шлях в ІТ ще у 2013 році, коли це ще не було мейнстрімом, з позиції Trainee. Тому, звичайно, я знаю такі страшні слова, як web.xml, JSP, сервлети, скриплети тощо. Маю практичний досвід роботи з базами даних, і цим досвідом я б хотів поділитись з вами сьогодні у контексті дедлоків (deadlocks) в MySQL.

Ми поговоримо про:

• Що таке дедлоки. Коли й чому вони виникають.
• Типи дедлоків.
• Налаштування бази даних для ефективного моніторингу та альортингу дедлоків.
• Які типи блокувань (locks) існують, і як з ними працювати.
• Вирішення (troubleshooting) дедлоків.

Що таке дедлоки

Дедлок виникає тоді, коли два якихось різних потоки (thread-a) перехресно хочуть заволодіти двома різними об’єктами (ресурсами). При тому, перший потік володіє першим об’єктом і бажає отримати другий об’єкт, а другий потік володіє другим об’єктом і бажає володіти першим. І ніхто не хоче поступатися.

Більш зрозумілою аналогією з реального життя можуть бути наступні ситуації:

Малюнок ліворуч ви могли бачити, якщо готувались до іспиту з ПДР — це нерегульоване перехрестя 🙂 На ньому діє правило «перешкода праворуч». В даній ситуації — у кожного така перешкода і хтось має поступитись. Водії повинні домовитись про те, хто першим проїде перехрестя, потім буде діяти правило «перешкода праворуч» і вони проїдуть. Малюнок праворуч — вже більш реальний кейс мого звичайного ранку в Києві.

У базах даних є процес, який автоматично запускається, виявляє дедлоки та вирішує (resolve) їх. Яким чином? Він обирає одну з транзакцій і виконує відкат (rollback). Таку транзакцію ми називаємо жертвою (deadlock victim). Як саме InnoDB обирає собі жертву? Він обирає найменшу транзакцію. Найменша транзакція визначається кількістю рядків, які ми намагаємось модифікувати (тобто вставка, видалення або оновлення). Другий фактор — це розмір undo log рекорда. Undo log record вміщує у собі інформацію про те, що саме транзакція змінювала, поки виконувалась (такий собі журнал усіх операцій). Тобто чим менше інформації треба відкотити та чим менша транзакція — тим легше це зробити (звучить логічно).

Чому розробники побоюються дедлоків? 🤷 Причини, напевно, більш філософські. Розробник, як і будь-яка звичайна людина, боїться того, чого не розуміє. I стосовно дедлоків дійсно може бути не зрозуміло, чи дедлок — це помилка, чи щось очікуване. На інтуїтивному рівні здається, що дедлок — це не ок. Також розробники інколи не знають, як моніторити дедлоки, відтворювати їх і, особливо, як виправляти. Тому в цілому ідея цієї статті — щоб таких страхів стало менше й інженери почали замислюватись, що, можливо, дедлок це не так і страшно.

А що про це каже офіційна документація? Згідно з нею, дедлоки — це класична проблема систем, які мають справи з транзакціями.

Виходить, що вони не є небезпечними до тих пір, поки це не впливає на вашу систему. Завжди будьте готові повторити (retry) транзакцію.

Блокування (locks)

Перш ніж заглиблюватися в тему дедлоків, варто нагадати, що таке блокування (locks) і яку роль вони відіграють. Адже дедлоки не виникали б без блокувань. Блокування в InnoDB потрібні, щоб гарантувати цілісність (consistency) і забезпечувати ізоляцію (isolation) між багатьма одночасними (concurrent) транзакціями. Поняття цілісності та ізоляції, своєю чергою, є частиною принципів ACID — A-atomicity, C-consistency, I-isolation, D-durability. Цим принципам мають слідувати усі реляційні бази даних (RDBMS) і загалом будь-яка система, яка працює з транзакціями.

Які типи блокувань існують?

• Блокування на рівні рядків (record lock).
• Блокування на рівні таблиць (table lock).
• Геплоки (gap locks) — інколи нам потрібно заблокувати не якийсь конкретний рядок, а проміжок (gap) між ними. Для чого це використовується? Наприклад, для того, щоб не було вставки в діапазон. Існують різні рівні ізоляції транзакцій: наприклад, для repeatable read цей рівень ізоляції транзакції гарантує, що ви не вичитаєте дублікати, якщо сусідня транзакція зробила вставку паралельно з вашою транзакцією. І один із способів, щоб repeatable read рівень ізоляції працював коректно, це використовувати геплоки, які блокують вставку для однієї транзакції, якщо вона працює з деяким діапазоном. Тобто інша транзакція не може вставити щось між записами, які потрапили у вашу вибірку. Наприклад, ви робите select * from users where age >= 18 and age <= 30. Для того, щоб у рамках однієї транзакції декілька раз вичитати той самий набір користувачів (users) і бути впевненим, що інша транзакція не додасть інших записів, база даних може заблокувати цей діапазон.
• Next Key Lock — щось схоже на gap locks, але він є комбінацією record lock (який блокує цілий рядок) та gap lock (який блокує певний проміжок).
• Auto-Increment Lock — використовується для генерації автоінкременту.
• Intention Lock — блокування, що сигналізують про намір щось заблокувати. Наприклад, перед тим, як зробити вставку у таблицю, база даних спочатку сповіщає про намір це зробити.

Блокування бувають Shared та Exclusive. Якщо Shared Lock накладений на таблицю, то декілька транзакцій можуть читати, але якщо хтось буде намагатися записувати в цю таблицю — це буде неможливо, треба буде почекати. Exclusive Lock не дозволяє ні читати, ні записувати.

Як хендлити дедлоки

Все просто — не використовувати базу даних 😂 Жартую.

Найперше потрібно визнати, що у вас виникла проблема (майже як у психолога на прийомі). Для цього потрібно правильно сконфігурувати базу даних, щоб мати можливість отримувати детальну інформацію про дедлоки. Також потрібно налаштувати моніторинг (monitoring) та систему оповіщення (alerting).

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

Крім того, варто бути готовим виконати будь-яку транзакцію двічі або більше разів (retry). Намагайтесь використовувати менше блокувань. Один з прикладів, як можна використовувати менше блокувань: якщо вам не потрібен рівень ізоляції транзакції repeatable read, ви можете завжди змінити його на read committed (тобто послабити рівень ізоляції транзакцій). Варто також звертати увагу на індекси, якими ви користуєтесь, і взагалі на те, чи потрібні вам індекси.

Налаштування бази даних (для роботи з дедлоками)

• В MySQL є опція innodb_print_all_deadlocks, яка відповідає за те, щоб інформація про дедлок (коли такий трапляється) записувалась в журнал помилок (error log). За замовчуванням вона вимкнена. Варто увімкнути її для того, щоб ви мали змогу бачити не лише останній дедлок, але і всі інші дедлоки, що трапились у минулому. Документація обіцяє, що увімкнення цієї опції ніяк не впливає на роботу вашої бази даних.

Хотів зазначити, що існує наступна команда SHOW ENGINE INNODB STATUS, що показує безліч корисної інформації, зокрема й інформацію про останній дедлок, який трапився. Але, коли виникне інший дедлок, вся попередня інформація буде перезатерта. Тому опція innodb_print_all_deadlocks повинна бути завжди увімкнута.

• Інша опція, на яку потрібно звернути увагу, це innodb_deadlock_detect. За замовчуванням вона включена. Ця опція вмикає/вимикає механізм пошуку дедлоків. Документація зазначає, що інколи бувають випадки, особливо у високонавантажених системах, де варто вимкнути цю опцію, адже вона може спричинити уповільнення роботи вашої системи, коли багато потоків чекають на один і той самий лок.

Моніторинг та оповіщення

На нашому проєкті ми використовуємо AWS CloudWatch для моніторингу (monitoring) та оповіщення (alerting).

Вище я навів приклад AWS CloudWatch дашборду, в якому ми можемо створювати аларми. Аларми створюються на основі метрик. Коли значення цієї метрики перевищує вказаний поріг, спрацьовує аларм. Аларм може перебувати у будь-якому із наступних станів: OK, In Alarm, Insufficient Data. Ми можемо налаштувати сповіщення, яке буде спрацьовувати кожен раз, коли аларм змінює свій стан (наприклад с OK на In Alarm).

У нашому випадку аларм надсилає повідомлення у AWS SNS (Simple Notification Service) топік. Це сервіс, який може приймати повідомлення (наприклад, у форматі JSON) і розсилати (broadcast) його далі на будь-які канали, що підписані (subscribed) на нього. Це може бути AWS SQS черга, будь-який інший сервіс (який працює по HTTP), AWS Lambda тощо. В нашому випадку хотілось би отримати листа, тому наш SNS-топік сконфігурований так, щоб кожен раз, коли в нього потрапляє повідомлення (event), він надсилав листа на заздалегідь сконфігуровану email-адресу. В результаті ми отримуємо листа з посиланням на сам аларм у випадках, коли трапився дедлок — і це дуже зручно.

Troubleshooting

Уявімо, що ви все налаштували, і ось трапився дедлок. Що робити далі?

Як було сказано, коли трапляється дедлок, інформація про нього фіксується в базі даних. Щоб отримати детальну інформацію про дедлок, я використовую вже згадану команду SHOW ENGINE INNODB STATUS (виконувати її треба в SQL-консолі). В нашому випадку цікава лише секція «Latest Deadlock». Якщо дедлок трапився давно, то шукаю інформацію в логах з помилками бази даних (MySQL error logs).

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

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

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

SELECT ENGINE_TRANSACTION_ID,
      THREAD_ID,
      LOCK_TYPE,
      LOCK_MODE,
      LOCK_STATUS,
      LOCK_DATA
FROM PERFORMANCE_SCHEMA.DATA_LOCKS;

Ця інформація допомагає зрозуміти, які рядки зараз знаходяться в локу, у якому стані знаходиться лок (granted / waiting), з якого потоку (thread) ініційована ця транзакція.

Загальні поради (tips & tricks)

У нас на проєкті використовується MyBatis SQL. Це не зовсім ОРМ, а швидше mapping-фреймворк, де ти пишеш усі SQL-запити вручну, а потім мапиш їх на сутності (entities). В MyBatis SQL є можливість заінлайнити коментар:

Пропоную наступну структуру коментаря: він починається з імені аплікації, потім доменна область (user, order тощо) і потім сама операція (update, іnsert, delete). Таким чином ми створюємо унікальні ідентифікатори. Навіщо? Щоб потім, коли ми будемо аналізувати deadlock dump, нам було легко знайти конкретний SQL-запит. Якщо ж, наприклад, у вас на проєкті JPA, то використовуйте анотацію @Meta для того, щоб додати коментар, та не забудьте увімкнути опцію hibernate.use_sql_comments (встановити значення true).

Інша моя порада буде стосуватись того, як репродʼюсити дедлок. Спойлер: це інколи важко. Якщо ви, як і я, користуєтесь IntelliJ IDEA, то там є вбудований термінал для роботи з базою даних з безліччю корисних функцій. Щоб спробувати зарепродʼюсити дедлок, я використовую мануальний (manual) режим контролю транзакцій.

Мануальний режим потрібен для того, щоб контролювати, коли транзакція починається, а коли закінчується. Пам’ятайте, за замовчуванням кожна SQL-операція виконується в окремій транзакції, тому симулювати дедлоки не вийде, доки не увімкнете ручне управління транзакціями. Якщо ви не використовуєте IntelliJ IDEA, або просто працюєте з терміналом, то використовуйте ключові слова START TRANSACTION (щоб розпочати транзакцію) та COMMIT чи ROLLBACK (щоб успішно зафіксувати всі операції в транзакції чи відкотити усе відповідно).

Важливо розуміти, з яким рівнем ізоляції транзакції ви працюєте. Інколи, щоб зарепродʼюсити дедлок, потрібно переходити на інший рівень ізоляції транзакцій. Наприклад, нещодавно я не міг зарепродʼюсити дедлок з рівнем ізоляції за замовчуванням, і мені довелось змінити його на Serializable. Щоб змінити рівень ізоляції в межах сесії, використовуйте команду: SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED (дана команда змінить рівень ізоляції транзакції на READ COMMITED для поточної сесії). Щоб перевірити рівень ізоляції для поточної сесії, використовуйте: SELECT @@transaction_ISOLATION.

Демо

Як наголошує документація з MySQL, нам потрібно бути готовими повторити (retry) будь-яку нашу транзакцію, якщо з якихось причин вона була завершена з відкатом (rollbacked).

Розглянемо невеликий Java Spring Boot проєкт.

Проєкт має досить просту структуру (складається з controller, service, repository, dto, entity). Є невеликий UI (HTML + JS). Ідея застосунку у тому, щоб через WEB-інтерфейс завантажувати фото для обраного стилю одягу.

Ми спробуємо завантажувати зображення (натискаючи кнопку ‘Choose files’ та обираючи декілька зображень і натискаючи кнопку ‘Upload’). На першому етапі це не буде виходити, бо виникатимуть дедлоки. Після незначних маніпуляцій все має запрацювати 😀

Перегляньмо структуру бази даних. У нас є табличка image, що складається з:

• id — унікальний ідентифікатор зображення.
• name — назва файлу (зображення).
• style_id — це ідентифікатор стилю одягу (наприклад, «кросівки чоловічі» матимуть значення 1, «кросівки жіночі» — 2 і тому подібне).
• status — статус зображення (можливі значення: ‘Submitted’, ‘Approved’, ‘Rejected’, за замовчуванням — ‘Submitted’).
• upload_date — дата завантаження файлу (зображення).

create table image
(
   id          int auto_increment primary key,
   name        varchar(500)                           null,
   style_id    int                                    null,
   status      varchar(100) default 'Submitted'       not null,
   upload_date timestamp    default CURRENT_TIMESTAMP not null
);

Також є інша таблиця — image_statistics, що зберігає агреговані статистичні дані:

create table image_statistics
(
   style_id         int           not null,
   num_of_submitted int default 0 not null,
   max_upload_date  timestamp     null,
   constraint style_id unique (style_id)
);

Для кожного стилю одягу (тобто для кожного style_id) ми зберігаємо кількість завантажених зображень (num_of_submitted) та останню (максимальну) дату завантаження (max_upload_date).

В який момент і хто саме буде робити такі обчислення? Все просто — тригер.

DELIMITER //
CREATE TRIGGER demo.image_created
   AFTER INSERT
   ON demo.image
   FOR EACH ROW
BEGIN
   CALL demo.update_image_statistics_by_style_id(NEW.style_id);
END//
DELIMITER ;

Кожен раз після того, як в таблицю image потрапляє нове зображення, спрацює тригер та викличе процедуру update_image_statistics_by_style_id.

Так, у нас є ще й процедура 🙂 Що вона робить? Процедура виконує групування (GROUP BY) по вказаному стилю одягу (style_id_param), рахуючи кількість завантажених (where state = ‘Submitted’) зображень, а також визначає останню (максимальну) дату завантаження. Результат записує в таблицю image_statistics.

create definer = root@`%` procedure update_image_statistics_by_style_id(IN style_id_param int)
BEGIN
   INSERT INTO demo.image_statistics (style_id, max_upload_date, num_of_submitted)
   SELECT img.style_id,
          (SELECT MAX(image.upload_date) as max_upload_date
           from demo.image image
           WHERE image.style_id)                  AS max_upload_date,
          SUM(IF(img.status = 'Submitted', 1, 0)) AS num_of_submitted
   FROM demo.image AS img
   WHERE img.style_id = style_id_param
   GROUP BY img.style_id
   ON DUPLICATE KEY UPDATE demo.image_statistics.max_upload_date  = VALUES(max_upload_date),
                           demo.image_statistics.num_of_submitted = VALUES(num_of_submitted);
END;

Спроба #1. Запускаємо як є

Запускаємо застосунок (як це зробити, описно в README.md). Обираємо декілька файлів з файлової системі (будь-яких). Уявімо, що ми завантажуємо три зображення кросівок одного стилю, але з різних ракурсів. Тиснемо кнопку ‘Upload’... Тут варто додати, що кожен файл (зображення) завантажується окремо (тобто окремий HTTP-запит, окрема транзакція). І як результат бачимо, що не всі зображення успішно завантажились.

Аналізуючи логи, звертаємо увагу на текст помилки:

Deadlock found when trying to get Lock — try restarting transaction.

Тобто ми виконували insert, і процедура, яка були викликана тригером, призвела до дедлоку. До речі, зверніть увагу, що ніяких згадувань про процедуру або тригери немає — і це ще один підводний камінь у використання процедур і тригерів. Здається, що просто виконався простенький insert, але чомусь трапився дедлок. Спробуймо розв’язати цю проблему.

Спроба #2. Правимо код і пробуємо ще

Один зі способів, який я продемонструю, це те, що радить нам текст помилки: try restarting transaction (тобто спробуйте ще). Для цього ми скористаймося анотацією @Retryable.

Почнемо з потрібних додаткових залежностей у pom.xml. Потрібно додати spring-retry і також не забути додати spring-aop (для правильної роботи анотації @Retryable)

<!-- Retry -->
<dependency>
   <groupId>org.springframework.retry</groupId>
   <artifactId>spring-retry</artifactId>
</dependency>

<!-- is required for a spring-retry framework to work properly -->
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

Потім слід додати анотацію @EnableRetry для того, щоб глобально у застосунку увімкнути механізм re-try-їв.

І звісно додати анотацію @Retryable над методом сервісного класу:

Анотація @Retryable дуже гнучка в плані налаштувань. У моєму випадку я вказав кількість спроб (maxAttempts) п’ять. Стратегію розрахунку наступної спроби (backoff) я обрав random з додатковими параметрами: delay (затримка між спробами) та maxDelay (максимальна затримка між спробами). В результаті: метод save(), якщо в ньому виникне PessimisticLockingFailureException (а саме він виникне, коли трапиться deadlock), буде повторно запущений максимально 5 разів з затримкою між запусками від 100 мілісекунд до 300 мілісекунд (обрана випадково).

Завантажуємо наші зображення знову і бачимо, що все працює.

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

Тестування

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

@Test
void save_whenDeadlock_shouldSuccessfullyRetry() {
  // given
  doThrow(PessimisticLockingFailureException.class, 3) // emulate deadlock for each insert for 3 times
      .doNothing() // then allow the insert
      .when(imageRepository).insert(any());
  
 // when we save the image
  imageService.save(image());
  
 // then we expect 3 unlucky and a single lucky calls (4 calls in total) to a repository
  verify(imageRepository, times(4)).insert(imageEntity());
}

Для тестування використовую Mockito. В даному тестовому сценарії я симулюю дедлок. А саме — вказую, що метод репозиторію insert() має викинути PessimisticLockingFailureException тричі, а на четвертий раз виконатись успішно — doNothing(). Я очікую, що коли я викликаю метод сервісу save() один раз, то метод insert() репозиторію буде викликаний 4 рази (3 рази — це невдалі спроби ретрай-механізму, а остання четверта спроба — успішна).

Висновки

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

Ми також дізналися, як налаштувати моніторинг і сповіщення, щоб своєчасно виявляти та аналізувати дедлоки. Розглянули практичний підхід до обробки дедлоків за допомогою анотації @Retryable та переконалися, як це можна налаштувати в Spring Boot.

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

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

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті