Передняя часть

Эта статья возникла из-за проблем с пакетной обработкой проекта в компании. Сначала кратко опишем предысторию.

Основанное на Spring Batch приложение для пакетной обработки работает уже более девяти месяцев, но однажды задача пакета неожиданно завершилась неудачно. Проверка журнала показала, что причиной была ошибка MySQL: Lock wait timeout exceeded. Хотя ошибка MySQL «Lock wait timeout exceeded» встречается нечасто, её легко найти в интернете. Причиной этой ошибки является превышение времени ожидания блокировки транзакции MySQL по умолчанию, которое составляет 50 секунд. Однако наша бизнес-логика пакетной обработки не должна приводить к ситуации ожидания исключительной блокировки транзакции, и мы не можем понять причину.

О чём эта статья?

После прочтения этой статьи вы узнаете, как MySQL InnoDB решает проблемы параллелизма с помощью блокировок и MVCC (многоверсионного управления параллелизмом), а также когда возникает ошибка Lock wait timeout exceeded, какие блокировки применяются и как устранять проблемы взаимоблокировки базы данных. В статье представлены некоторые идеи решения этих проблем.

Обратите внимание, что эта статья применима только к версиям MySQL 5.7 и ниже.

Уровни изоляции транзакций MySQL

Сначала рассмотрим уровни изоляции транзакций в MySQL, которые часто обсуждаются и часто задаются на собеседованиях, хотя немногие могут объяснить их своими словами.

• Чтение незафиксированных данных (read-uncommitted): существует проблема грязного чтения, неповторяемости и фантомного чтения.
• Чтение зафиксированных данных (read-committed): существуют проблемы неповторяемого чтения и фантомного чтения.
• Повторяемое чтение (repeatable-read): существует проблема фантомного чтения.
• Сериализуемость (serializable): нет проблем.

Грязное чтение: транзакция A считывает данные, которые ещё не были зафиксированы транзакцией B, а затем транзакция B откатывается. Тогда данные, считанные транзакцией A, являются грязными данными. Неповторяющееся чтение: в одной транзакции транзакция A дважды считывает одну и ту же запись, но поскольку транзакция B изменяет данные между этими двумя операциями чтения, результаты двух операций чтения различаются. Фантомное чтение: транзакция A проверяет, существует ли запись, и если да, то вставляет эту запись. Перед выполнением вставки транзакция A обнаруживает, что записи не существует. Между тем, во время интервала транзакции A транзакция B вставила эту запись, поэтому при выполнении транзакции A вставка завершится неудачно, подобно иллюзии.

Что касается фантомного чтения, в интернете есть много интерпретаций этого понятия. На самом деле, это неправильное понимание. Фантомное чтение фактически относится к ситуации, когда в соответствии с определённым условием проверяется наличие записи, а затем выполняется соответствующая операция. Например, транзакция A запрашивает значение записи и копирует эту запись, а затем сохраняет её. Ожидается, что после завершения транзакции A скопированная запись будет иметь то же значение, что и исходная запись. Однако, если происходит фантомное чтение, между завершением транзакции A и изменением исходной записи транзакцией B значение скопированной записи становится отличным от значения исходной записи. Многие в интернете говорят, что фантомное чтение связано с несоответствием количества строк до и после операции вставки, которая относится к фантомному чтению на уровне ROW. Фактически, это неверно, и эту ситуацию можно избежать с помощью временных блокировок (Gap Locks) и MVCC на уровне RR. Проще говоря, данные, считываемые внутри транзакции, могут быть изменены до завершения текущей транзакции транзакцией B. При повторном чтении данные не соответствуют ожидаемым.

InnoDB транзакции, блокировки и связанные таблицы

В MySQL есть системная база данных, содержащая множество таблиц, включая три таблицы, которые записывают информацию о транзакциях, текущих блокировках и текущих ожидающих транзакциях. Чтобы проанализировать и проверить проблему, нам нужно объединить информацию в этих таблицах. Поэтому сначала рассмотрим, какую информацию записывают эти таблицы.

INNODB_TRX: таблица транзакций InnoDB, в которой записывается информация о каждой открытой транзакции, включая выполняемый SQL, уровень изоляции транзакции, связанную таблицу и другую информацию.

CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '', #внутренний уникальный идентификатор транзакции
  `trx_state` varchar(13) NOT NULL DEFAULT '', #состояние выполнения транзакции. Допустимые значения: RUNNING, LOCK WAIT, ROLLING BACK и COMMITTING
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', #время начала транзакции.
  `trx_requested_lock_id` varchar(81) DEFAULT NULL, #идентификатор блокировки, ожидаемой текущей транзакцией. Если TRX_STATE не равно LOCK WAIT; тогда NULL.
  `trx_wait_started` datetime DEFAULT NULL, #время ожидания блокировки. Если TRX_STATE не LOCK WAIT; тогда NULL.
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0', #вес транзакции, отражающий (но не обязательно точное количество) изменённых строк и заблокированных строк транзакции.
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0', #ID потока MySQL.
  `trx_query` varchar(1024) DEFAULT NULL, #SQL, выполняемый текущей транзакцией.
  `trx_operation_state` varchar(64) DEFAULT NULL, #текущая операция транзакции; если есть; иначе NULL.
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0', #количество таблиц, используемых текущей транзакцией InnoDB при обработке текущего SQL.
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0', #Количество таблиц, заблокированных текущей транзакцией SQL.
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0', #число блокировок, удерживаемых транзакцией.
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0', #общий размер памяти, занимаемый структурами блокировок транзакции.
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0', #приблизительное или фактическое количество строк, заблокированных транзакцией. Это значение может включать строки с удаленными метками, которые фактически не видны транзакции.
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0', #общее количество измененных и вставленных строк в транзакции.
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0', #указывает, сколько работы может выполнить текущая транзакция перед вытеснением, определяется системной переменной innodb_concurrency_tickets.
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '', #уровень изоляции текущей транзакции.
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0', #включать или выключать уникальные проверки для текущей транзакции. Например, они могут быть отключены во время массовой загрузки данных.
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0', #включить или отключить проверку внешних ключей для текущей транзакции. Например, они могут быть отключены во время массовой загрузки данных.
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL, #подробное сообщение об ошибке последнего внешнего ключа (если есть); иначе NULL.
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0', #заблокирован ли адаптивный хэш-индекс текущей транзакцией. Когда адаптивный хеш-индекс поиска системы разделён, отдельная транзакция не блокирует весь адаптивный хеш-индекс. Адаптивный хеш-индекс разделяется на части, по умолчанию 8.
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0', #немедленно отказаться от поиска блокировок адаптивного хеш-индекса или сохранить их между вызовами MySQL. Когда нет конкуренции за адаптивный хеш-индекс, это значение сохраняется равным нулю, и блокировка сохраняется до тех пор, пока она не будет завершена. Во время конкуренции это значение отсчитывается до нуля, и каждый раз, когда строка найдена, блокировка немедленно освобождается. Когда система поиска адаптивного хеширования разделена (контролируется innodb_adaptive_hash_index_parts), это значение остаётся равным 0.
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0', #значение 1 указывает, что транзакция доступна только для чтения.
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0' #значение 1 означает, что транзакция представляет собой SELECT без использования FOR UPDATE или LOCK IN SHARED MODE, и в настоящее время выполняется, поэтому автофиксация транзакции будет содержать только этот оператор. Когда эта колонка и TRX_IS_READ_ONLY оба равны 1, InnoDB оптимизирует транзакцию, чтобы уменьшить накладные расходы, связанные с изменениями данных таблицы.
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

INNODB_LOCKS: таблица блокировок InnoDB, которая записывает текущую информацию о блокировке транзакции. В приведённом тексте описывается тестирование различных уровней изоляции транзакций в MySQL.

Повторяемое чтение (repeatable-read):

• Выполнение: INSERT INTO USER (id, name, card, age) VALUES (31, 'kl', '00', 66), после блокировки транзакции.
• Блокируются операции вставки, обновления и удаления, связанные с первичным ключом 31 и уникальным индексом '00'.
• Операции чтения этой записи (где условия — id или уникальный индекс записи) не блокируются.

Сериализуемый уровень (transaction-serializable):

• Выполнение: INSERT INTO USER (id, name, card, age) VALUES (31, 'kl', '00', 66) после блокировки транзакции.
• Блокируются операции вставки, обновления и удаления, связанные с первичным ключом 31 и уникальным индексом '00'.
• Операции чтения этой записи (где условия — id или уникальный индекс записи) также блокируются.

Если время ожидания блокировки слишком велико, возникает исключение Lock wait timeout exceeded.

Автор приходит к выводу, что MySQL является важным инструментом для разработчиков, работающих с бэкендом. Он подчёркивает важность понимания транзакционных блокировок и предлагает обратиться к официальной документации MySQL для более глубокого изучения темы. Также автор рекомендует блог эксперта по базам данных из команды Alibaba Database, где можно найти дополнительную информацию по теме.