Активный бизнес проект на базе LAMP + Битрикс версии 8, десятки тысяч хитов в сутки. MySQL версии 5.032 Innodb, обычная нагрузка на сервер ~ 300 запросов в секунду. Периодически требуется пакетная загрузка данных о товарах PHP-скриптом из CSV файла: 100-200 тысяч записей. Во время выполнения возникает ошибка:
Deadlock found when trying to get lock; try restarting transaction
Клиент взволнованно спрашивает: "... скажите, импортировать в ИБ 170 тыс элементов это вообще проблема для Битрикса? Может ли это быть связано с переходом на 8ку?"
Два традиционных вопроса: кто виноват и что делать? [spoiler] Чтобы выянить причину на сервере БД запускаем утилиту innotop и в разделе deadlock видим:
CXN ID User Victim Query Text loc 52 bitrix Yes INSERT INTO b_iblock_element_property... loc 76 interes No INSERT INTO b_iblock_element_property... ________________________________ Deadlock Locks __________________________________ CXN ID Txn Status Mode Table Index loc 52 waits_for X b_iblock_element_property ix_iblock_element_property_1 loc 76 waits_for AUTO-INC b_iblock_element_property loc 76 holds X b_iblock_element_property ix_iblock_element_property_1
Более подробную информацию можно посмотреть с помощью монитора блокировок innodb:
mysql> drop table if exists innodb_lock_monitor; mysql> CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; mysql> show innodb status;
------------------------ LATEST DETECTED DEADLOCK ------------------------ *** (1) TRANSACTION: TRANSACTION 1 1923867079, ACTIVE 0 sec, process no 27020, OS thread id 1143179616 inserting mysql tables in use 2, locked 2 LOCK WAIT 5 lock struct(s), heap size 1216, undo log entries 1 MySQL thread id 5291, query id 2563262 Sending data INSERT INTO b_iblock_element_property (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID, VALUE, VALUE_NUM, DESCRIPTION) SELECT ... FROM b_iblock_property WHERE ID=1230 ... *** (2) TRANSACTION: ... MySQL thread id 7615, query id 2563269 Sending data INSERT INTO b_iblock_element_property (IBLOCK_ELEMENT_ID, IBLOCK_PROPERTY_ID, VALUE, VALUE_NUM, DESCRIPTION) SELECT ... FROM b_iblock_property WHERE ID=1192 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 58640 n bits 664 index `ix_iblock_element_property_1` of table `bitrix/b_iblock_element_property` trx id 1 1923866935 lock_mode X ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `bitrix/b_iblock_element_property` trx id 1 1923866935 lock mode AUTO-INC waiting *** WE ROLL BACK TRANSACTION (1)
Auto increment столбцы в таблицах MySQL - очень удобная функция, проблема в реализации - при "традиционном" механизме блокирования типа Auto-Increment Locking: "... InnoDB использует специльную AUTO-INC блокировку на уровне таблицы, которая удерживается до окончания выполнения SQL оператора". Именно такую блокировку ждёт в примере транзакция 2 (AUTO-INC waiting), что порождает deadlock в случае конкурентной вставки записи транзакцией 1. Проблема встречается, в частности, при интенсивных операциях вставки (bulk insert) типа INSERT ... SELECT ..., и, к счастью, уже решена в версиях MySQL, начиная с 5.1.22.
Клиенту предложили следующие варианты решения проблемы:
1. Изменить приложение (PHP скрипт) , добавив что-то типа эксклюзивной блокировки всей проблемной таблицы на запись (некрасиво, небыстро, немасштабируемо):
2. Обновить сервер MySQL до версии >= 5.1.22 (с обязательным предварительным тестированием), после чего появится возможность изменить механизм блокирования (AUTO-INC locking) с помощью нового параметра innodb_autoinc_lock_mode. Для решения проблемы в нашем случае (bulk inserts) подойдёт значение:
В этом случае "... не используются блокировки AUTO-INC уровня таблицы, и несколько SQL могут выполнятся одновременно. Этот тип блокирования является самым быстрым и масштабируемым ..."
Проблема успешно решена клиентом обновлением MySQL до версии 5.1.34. Битрикс невиновен
При innodb_autoinc_lock_mode = 2 не будет работать репликация на основе бинарного лога... и айдишники не будут содержать последовательные значения. Т.е вместо 1-2-3 может быть 123-5445-45999 и т.д. Но это все мелочи, т.к по всей видимости репликации нет ни на одном битрикс проекте.
Группы на сайте создаются не только сотрудниками «1С-Битрикс», но и партнерами компании. Поэтому мнения участников групп могут не совпадать с позицией компании «1С-Битрикс».