Первоначально Написал Peiran Song
В MySQL возникает тупик, когда две или более транзакций взаимно удерживаются и запрашивают блокировки, создавая цикл зависимостей. В транзакционной системе взаимоблокировки являются фактом жизни и их нельзя полностью избежать. InnoDB автоматически обнаруживает взаимоблокировки транзакций, немедленно откатывает транзакцию и возвращает ошибку. Он использует метрику для выбора самой простой транзакции для отката. Хотя случайный тупик не является чем-то, о чем нужно беспокоиться, частые случаи требуют внимания.
До MySQL 5.6 только последняя тупиковая ситуация может быть проверена с помощью команды SHOW ENGINE INNODB STATUS. Но с помощью pt-deadlock-logger Percona Toolkit вы можете получить информацию о взаимоблокировке, извлеченную из SHOW ENGINE INNODB STATUS с заданным интервалом и сохраненную в файл или таблицу для поздней диагностики. Для получения дополнительной информации об использовании pt-deadlock-logger см. Этот пост . В MySQL 5.6 вы можете включить новую переменную innodb_print_all_deadlocks, чтобы все взаимоблокировки в InnoDB записывались в журнал ошибок mysqld.
Перед и прежде всего диагностикой , всегда важно, чтобы приложения перехватывали тупиковую ошибку (ошибка MySQL № 1213) и обрабатывали ее, повторяя транзакцию.
Как диагностировать тупик MySQL
В случае взаимоблокировки MySQL может потребоваться более двух транзакций, но в разделе ПОСЛЕДНЯЯ ОБНАРУЖЕННАЯ ОТКЛЮЧЕНИЕ показаны только две последние транзакции. Кроме того, он показывает только последний оператор, выполненный в двух транзакциях, и блокирует две транзакции, которые создали цикл. То, что пропущено, — это более ранние утверждения, которые действительно могли получить блокировки. Я покажу несколько советов о том, как собрать пропущенные утверждения.
Давайте посмотрим на два примера, чтобы увидеть, какая информация предоставляется . Пример 1:
1 141013 6:06:22 2 *** (1) TRANSACTION: 3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock 4 mysql tables in use 1, locked 1 5 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4 6 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update 7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello') 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting 10 *** (2) TRANSACTION: 11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting 12 mysql tables in use 1, locked 1 13 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002 14 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update 15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more") 16 *** (2) HOLDS THE LOCK(S): 17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC 18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting 20 *** WE ROLL BACK TRANSACTION (1)
В строке 1 указано время возникновения тупика. Если код вашего приложения перехватывает и регистрирует ошибки взаимоблокировки, что и должно быть, вы можете сопоставить эту временную отметку с временными отметками ошибок взаимоблокировки в журнале приложения. Вы бы получили транзакцию, которая была откатана. Оттуда получите все операторы из этой транзакции.
В строках 3 и 11 запишите номер транзакции и АКТИВНОЕ время. Если вы периодически регистрируете выходные данные SHOW ENGINE INNODB STATUS (что является хорошей практикой), то вы можете искать предыдущие выходные данные с номером транзакции, чтобы надеяться увидеть больше операторов из той же транзакции. Секция ACTIVE дает подсказку о том, является ли транзакция одной или несколькими операциями.
В строках 4 и 12 таблицы используются и заблокированы только относительно текущего оператора. Таким образом, использование 1 таблицы не обязательно означает, что транзакция включает только 1 таблицу.
В строках 5 и 13 это заслуживает внимания, так как в нем указывается, сколько изменений было сделано транзакцией, а именно «отменить записи журнала» и сколько блокировок строк она удерживала, что является «блокировкой строки (строк)». Эта информация указывает на сложность транзакции.
В строках 6 и 14 обратите внимание на идентификатор потока, подключение хоста и подключение пользователя. Если вы используете разных пользователей MySQL для разных функций приложения, что является еще одной хорошей практикой, вы можете определить, из какой области приложения происходит транзакция, основываясь на подключающемся хосте и пользователе.
В строке 9 для первой транзакции показывается только ожидаемая блокировка, в этом случае блокировка AUTO-INC для таблицы t1. Другими возможными значениями являются S для общей блокировки и X для исключительной с блокировкой или без нее.
Строки 16 и 17 для второй транзакции показывают блокировку, которую он удерживал, в этом случае блокировку AUTO-INC, которая была тем, что ожидал TRANSACTION (1).
Строки 18 и 19 показывают, какую блокировку TRANSACTION (2) ожидал. В этом случае это была общая блокировка записи без пробелов в первичном ключе другой таблицы. В InnoDB есть только несколько источников для общей блокировки записи:
1) использование SELECT… LOCK IN SHARE MODE
2) для записи (записей) с внешним ключом
3) с INSERT INTO… SELECT, общие блокировки для исходной таблицы
Текущий оператор trx (2) — простая вставка в таблицу t1, поэтому 1 и 3 исключены. Проверяя SHOW CREATE TABLE t1, вы можете подтвердить, что блокировка S была вызвана ограничением внешнего ключа родительской таблицы t2.
Пример 2. В версии сообщества MySQL для каждой блокировки записи выводится содержимое записи:
1 2014-10-11 10:41:12 7f6f912d7700 2 *** (1) TRANSACTION: 3 TRANSACTION 2164000, ACTIVE 27 sec starting index read 4 mysql tables in use 1, locked 1 5 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 6 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating 7 update t1 set name = 'b' where id = 3 8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: 9 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164000 lock_mode X locks rec but not gap waiting 10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 11 0: len 4; hex 80000003; asc ;; 12 1: len 6; hex 000000210521; asc ! !;; 13 2: len 7; hex 180000122117cb; asc ! ;; 14 3: len 4; hex 80000008; asc ;; 15 4: len 1; hex 63; asc c;; 16 17 *** (2) TRANSACTION: 18 TRANSACTION 2164001, ACTIVE 18 sec starting index read 19 mysql tables in use 1, locked 1 20 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1 21 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating 22 update t1 set name = 'c' where id = 2 23 *** (2) HOLDS THE LOCK(S): 24 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap 25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 26 0: len 4; hex 80000003; asc ;; 27 1: len 6; hex 000000210521; asc ! !;; 28 2: len 7; hex 180000122117cb; asc ! ;; 29 3: len 4; hex 80000008; asc ;; 30 4: len 1; hex 63; asc c;; 31 32 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 33 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap waiting 34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0 35 0: len 4; hex 80000002; asc ;; 36 1: len 6; hex 000000210520; asc ! ;; 37 2: len 7; hex 17000001c510f5; asc ;; 38 3: len 4; hex 80000009; asc ;; 39 4: len 1; hex 62; asc b;;
Строки 9 и 10: «идентификатор пространства» — это идентификатор табличного пространства, «номер страницы» указывает, на какой странице находится блокировка записи внутри табличного пространства. «N бит» — это не смещение страницы, а количество бит в битовой карте блокировки. Смещение страницы — это «куча нет» в строке 10,
Строка 11 ~ 15: показывает данные записи в шестнадцатеричных числах. Поле 0 является индексом кластера (первичный ключ). Игнорировать старший бит, значение равно 3. Поле 1 является идентификатором транзакции, которая последней изменила эту запись, десятичное значение — 2164001, которое равно TRANSACTION (2). Поле 2 является указателем отката. Начиная с поля 3 — остальные данные строки. Поле 3 является целочисленным столбцом, значение 8. Поле 4 является строковым столбцом с символом ‘c’. Читая данные, мы точно знаем, какая строка заблокирована и какое текущее значение.
Что еще мы можем извлечь из анализа? Поскольку большинство взаимоблокировок MySQL происходит между двумя транзакциями, мы могли бы начать анализ на основе этого предположения. В примере 1trx (2) ожидал общей блокировки, поэтому trx (1) удерживал общую или монопольную блокировку для этой записи первичного ключа таблицы t2. Скажем, col2 — это столбец внешнего ключа, проверив текущий оператор trx (1), мы знаем, что он не требует такой же блокировки записи, поэтому это должен быть какой-то предыдущий оператор в trx (1), который требовал блокировки S или X ( s) на записи PK в t2. Trx (1) сделал только 4 изменения строки за 7 секунд. Затем вы узнали несколько характеристик trx (1): он выполняет много обработки, но вносит некоторые изменения; изменения касаются таблицы t1 и t2, вставки одной записи в t2. Эта информация в сочетании с другими данными может помочь разработчикам определить местонахождение транзакции.
Где еще мы можем найти предыдущие заявления о сделках? Помимо журнала приложений и предыдущих выходных данных SHOW ENGINE INNODB STATUS, вы также можете использовать binlog, slow log и / или общий журнал запросов. С binlog, если binlog_format = оператор, каждое событие binlog будет иметь thread_id. В binlog регистрируются только зафиксированные транзакции, поэтому мы можем искать только Trx (2) в binlog. В случае примера 1 мы знаем, когда произошла тупиковая ситуация, и мы знаем, что Trx (2) начался 9 секунд назад. Мы можем запустить mysqlbinlog в правом файле binlog и искать операторы с thread_id = 155097580. Всегда полезно затем перекрестно ссылаться на операторы с кодом приложения для подтверждения.
$ mysqlbinlog -vvv --start-datetime=“2014-10-13 6:06:12” --stop-datatime=“2014-10-13 6:06:22” mysql-bin.000010 > binlog_1013_0606.out
В Percona Server 5.5 и выше вы можете установить log_slow_verbosity для включения идентификатора транзакции InnoDB в медленный журнал. Тогда, если у вас long_query_time = 0, вы сможете перехватывать все операторы, включая те, которые откатываются в медленный файл журнала. В общем журнале запросов идентификатор потока включен и может использоваться для поиска связанных операторов.
Как избежать тупика MySQL
Есть вещи, которые мы могли бы сделать, чтобы устранить тупик после того, как мы это поймем.
— Внесите изменения в приложение. В некоторых случаях вы можете значительно снизить частоту взаимоблокировок, разбив длинную транзакцию на более мелкие, чтобы блокировки снимались быстрее. В других случаях взаимоблокировка возникает из-за того, что две транзакции касаются одного и того же набора данных в одной или нескольких таблицах с разными порядками. Затем измените их, чтобы получить доступ к данным в том же порядке, другими словами, сериализовать доступ. Таким образом, вы могли бы ожидать блокировки вместо тупика, когда транзакции происходят одновременно.
— Внесите изменения в схему таблиц, например, удалите ограничение внешнего ключа, чтобы отсоединить две таблицы, или добавьте индексы, чтобы минимизировать отсканированные и заблокированные строки.
— В случае блокировки промежутка вы можете изменить уровень изоляции транзакции, чтобы прочитать зафиксированный для сеанса или транзакции, чтобы избежать этого. Но тогда формат binlog для сеанса или транзакции должен быть ROW или MIXED.