Случай следующий:
при использовании Show innodb status для проверки состояния механизма была обнаружена проблема взаимоблокировки:
*** (1) ТРАНЗАКЦИЯ:
ТРАНЗАКЦИЯ 0 677833455, АКТИВНОСТЬ 0 секунд, номер процесса 11393, идентификатор потока ОС 278546, начальный индекс чтения
Используемые таблицы mysql 1, заблокированы 1
LOCK WAIT 3 структуры блокировки, размер кучи 320
Идентификатор потока MySQL 83, идентификатор запроса 162348740 dcnet03 dcnet Поиск строк для обновления
обновления TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now (), где STATUS_ID=1061 и MON_TIME
*** (1) ОЖИДАНИЕ ПРЕДОСТАВЛЕНИЯ ЭТОЙ БЛОКИРОВКИ:
RECORD LOCKS идентификатор пространства 0 номер страницы 849384 n биты 208 индекс `PRIMARY` таблицы `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X блокирует запись, но не пропускает ожидание
блокировки записи , номер кучи 92 ФИЗИЧЕСКАЯ ЗАПИСЬ: n_fields 11; информационные биты 0
0: len 8; asc b ;; 1: len 6; asc (f ;; 2: len 7; hex 00000d40040 110 @; ;; 3: шестнадцатеричный 8000000000050b2;; 4: шестнадцатеричный 800000000000502a; возрастающий P*;; 5: шестнадцатеричный 800000000005426; ж ;; 7: длина 23; шестнадцатеричный 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; asc +;; 9: len 4; asc ГК +;; len 8; по возрастанию Н$
;; * (2) ТРАНЗАКЦИЯ:
ТРАНЗАКЦИЯ 0 677833454, АКТИВНОСТЬ 0 секунд, номер процесса 11397, идентификатор потока ОС 344086, обновление или удаление, поток объявлен внутри InnoDB 499
используемых таблиц mysql 1, заблокировано 1
3 структуры блокировки, размер кучи 320, записи журнала отмены 1
идентификатор потока MySQL 84, идентификатор запроса 162348739 dcnet03 dcnet Обновление
обновления TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () где идентификатор в (9921180)
*** (2) УДЕРЖИВАЕТ БЛОКИРОВКИ:
ЗАПИСЬ БЛОКИРУЕТ идентификатор пространства 0 номер страницы 849384 n биты 208 индекс `PRIMARY` таблицы `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X блокирует запись, но не пробел
Блокировка записи, номер кучи 92 ФИЗИЧЕСКАЯ ЗАПИСЬ: n_fields 11 компактный формат информационных битов 0
0: len; 8; шестнадцатеричный 800000000097629c; по возрастанию b ;; 1: длина 6; шестнадцатеричный 00002866eaee; возраста (f ;; 2: длина 7; шестнадцатеричная 00000d40040110; возрастающая @ ;; 3: длина 8; шестнадцатеричная 8000000000050b2; ц П ;; 4:лен 8 ; шестнадцатеричный 800000000000502a; по возрастанию P*;; 5: длина 8; 0; по возрастанию uploadfire.com/hand.php;; 8: длина 8; шестнадцатеричный 80000000000042b; 8; hex 8000000000004e24; asc N$ ;;
*** (2) ОЖИДАНИЕ ПРЕДОСТАВЛЕНИЯ ЭТОЙ БЛОКИРОВКИ:
идентификатор пространства ЗАПИСИ 0, номер страницы 843102 n биты 600, индекс `KEY_TSKTASK_MONTIME2` таблицы `dcnet_db/TSK_TASK` trx id 0 6778 33454 lock_mode X блокирует запись, но не пропускает
блокировку записи, нет кучи 395 ФИЗИЧЕСКАЯ ЗАПИСЬ: n_fields 3; компактный формат 0
0: len 8; asc %;; 1: len 8; 97629c по возрастанию б; ;;
*** МЫ ОТКАТЫВАЕМ ТРАНЗАКЦИЮ (1)
Эта проблема взаимоблокировки связана с таблицей TSK_TASK, которая используется для сохранения задач мониторинга системы. Ниже приведены соответствующие поля и индексы:
ID: первичный ключ;
STATUS_ID:
время;
Статус задачи;
индекс: KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME).
Анализ показывает, что два задействованных оператора не должны использовать одну и ту же запись TSK_TASK, так почему же это вызывает взаимоблокировку?
Запросив документацию официального сайта MySQL, я обнаружил, что это связано с механизмом индексации MySQL. Механизм MySQL InnoDB использует блокировки на уровне строк. Первоначально я предполагал, что записи блокируются напрямую, но на самом деле это не так.
Ключевые моменты заключаются в следующем:
вместо блокировки записей индекс блокируется
во время операций UPDATE и DELETE, MySQL не только блокирует все записи индекса, сканируемые условием WHERE, но и блокирует соседние значения ключей, так называемый следующий ключ. блокировка,
например, оператор UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 заблокирует все записи с первичным ключом, большим или равным 1000. До завершения оператора вы не можете работать с записями с первичным ключом, равным 1000. до 10000;
когда некластерный индекс ( Когда запись некластерного индекса заблокирована, соответствующая запись индекса кластера также должна быть заблокирована для завершения соответствующей операции.
Анализируя два оператора SQL, в которых возникла проблема, нетрудно обнаружить проблему:
при «обновлении TSK_TASK установлено STATUS_ID=1064,UPDATE_TIME=now() где STATUS_ID=1061 и MON_TIME
Предполагая, что «обновление TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () где ID в (9921180)» выполняется почти одновременно, этот оператор сначала блокирует индекс кластера (первичный ключ), поскольку значение STATUS_ID необходимо обновить, оно должно быть обновлено. также необходимо заблокировать определенную часть KEY_TSKTASK_MONTIME2 некоторых записей индекса.
Таким образом, первый оператор блокирует запись KEY_TSKTASK_MONTIME2 и ожидает индекса первичного ключа, а второй оператор блокирует запись индекса первичного ключа и ожидает записи KEY_TSKTASK_MONTIME2. В этом случае возникает взаимоблокировка.
Автор решил проблему взаимоблокировки, разделив первый оператор:
сначала найдите квалифицированный идентификатор: выберите идентификатор из TSK_TASK, где STATUS_ID=1061 и MON_TIME < date_sub(now(), INTERVAL 30 минут); затем обновите статус: обновите TSK_TASK, установите STATUS_ID=; 1064, где ID в (….)
На этом этапе проблема тупиковой ситуации полностью решена.