Oracle mechanics

26.06.2009

Блокировки при использовании AUTO_INCREMENT столбцов в MySQL Innodb

Filed under: Блокировки,MySQL — Игорь Усольцев @ 11:20
Tags: ,

Новость о том, что разработчики MySQL серьёзно переработали блокировки этого типа и соответственно продвинули свою СУБД по пути масштабируемости, давно уже новостью не является  (см. InnoDB auto-inc scalability fixed, сентябрь 2007). Однако многие стабильные дистрибутивы Linux, например, Ubuntu 8.04LTS включают в себя пакет MySQL server версии 5.0, для которой проблема блокировок AUTO-INC вполне актуальна. Практический пример типичной проблемы и решения.

LAMP+CMS окружение, активный веб-магазин, десятки тысяч хитов в сутки. MySQL 5.032, обычная нагрузка на сервер ~ 300 запросов в секунду. Проблема возникала при выполнении пакетной загрузки данных о товарах PHP-скриптом: 100-200 тысяч записей, во время выполнения нагрузка на сервер ~ 1000 запросов в секунду, в логе выполнения — ошибка:

Deadlock found when trying to get lock; try restarting transaction

Innotop показывает в разделе последних deadlock’ов:

____________________________________Deadlock Locks____________________________________
CXN        ID    Txn Status  Mode      DB      Table   Index       Special  Ins Intent
localhost  5291  waits_for   X         db      t1      i1                       1
localhost  7615  waits_for   AUTO-INC  db      t1                               0
localhost  7615  holds       X         db      t1      i1                       0

mysql> show innodb status;

*** (1) TRANSACTION:
TRANSACTION 1 1923867079, ACTIVE 0 sec, ... 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 192.168.1.1 user1 Sending data
INSERT INTO t1 (col1,...) SELECT ... FROM    t2 WHERE   ID=1230
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index `i1` of table `db1/t1` ... lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 1 1923866935, ACTIVE 0 sec, ... setting auto-inc lock
mysql tables in use 2, locked 2
49 lock struct(s), heap size 6752, undo log entries 65
MySQL thread id 7615, query id 2563269 192.168.1.1 user2 Sending data
INSERT INTO t1 (col1,...) SELECT ... FROM    t2 WHERE   ID=1192
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index `i1` of table `db1/t1` trx id 1 1923866935 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `db1/t1` trx id 1 1923866935 lock mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (1)

Удобная функция MySQL auto increment порождает deadlock, являющийся следствием «традиционного» механизма блокировок типа Auto-Increment Locking:  «… InnoDB использует специльную AUTO-INC блокировку на уровне таблицы, которая удерживается до окончания выполнения SQL оператора». Именно такую блокировку ждёт в примере транзакция 2 (AUTO-INC waiting), что порождает deadlock в случае конкурентной вставки записи транзакцией 1.
Проблема встречается, в частности, при интенсивных операциях вставки (bulk insert) типа INSERT … SELECT …, решена в версиях MySQL, начиная с 5.1.22.

Предложенные варианты решения:
1. Изменить приложение (PHP скрипт) , добавив что-то типа эксклюзивной блокировки всей таблицы на запись (некрасиво, небыстро, немасштабируемо):

LOCK TABLES t1 WRITE;
добавление записей в таблицу INSERT INTO t1 (col1,...) SELECT
UNLOCK TABLES;

2. Обновить сервер MySQL до версии >= 5.1.22 (с обязательным предварительным тестированием), после чего появлялась возможность «побаловаться» новым параметром innodb_autoinc_lock_mode. Для решения проблемы в нашем случае (bulk inserts) подойдёт значение:

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

При этом значении «… не используются блокировки AUTO-INC уровня таблицы, и несколько SQL могут выполнятся одновременно. Этот тип блокирования является самым быстрым и масштабируемым …»

Проблема успешно решена обновлением MySQL до версии5.1.34 (вариант №2).

PS. Продолжаю удивляться склонности ИТ менеджмента к выбору бесплатных слабомасштабируемых инструментов (MySQL, в частности) для серьёзных бизнес проектов. Oracle Standard Edition One даже для интернет использования стоит совсем недорого по сравнению с годовыми зарплатами в ИТ секторе ~ $11600 лицензия на 2 процессора (независимо от количества ядер) + $3552 ежегодная техподдержка, но предоставляет несравненно большие возможности настройки и масштабирования. Либо ключевым словом является бесплатный, либо интернет индустрия ещё не подросла — никому же не приходит в голову экономить на стоимости поддержки СУБД в финансах или связи :)

5 комментариев »

  1. Было бы здорово увидеть обзорную статью или хотя бы какие-то ссылки на информацию по сравнению «несравненно больших возможностей настройки и масштабирования oracle seo» с mysql. Правда интересно! Как-то до сих пор не довелось поинтересоваться данной темой.

    комментарий от Иван Присяжный — 30.06.2010 @ 15:15 | Ответить

  2. Привет, Иван
    Про настройку и масштабирование — начать можно с первых глав отличной книги евангилиста Oracle Тома Кайта http://www.citforum.ru/database/oracle/kyte/
    Решающим аргументом о безусловном превосходстве Oracle в вопросах масштабирования считаю технологию Real Application Cluster — см., например, http://www.oracle.com/global/ru/pdfs/tech/oracle-rac_09.06.pdf

    комментарий от Igor Usoltsev — 30.06.2010 @ 15:59 | Ответить

  3. Я столкнулся с такой же проблемой, только в моем случае insert в обеих транзакциях был одной всего строчки. Возник вопрос, может есть какие-то мысли по этому поводу..

    > Именно такую блокировку ждёт в примере транзакция 2 (AUTO-INC waiting), что порождает deadlock в случае конкурентной вставки записи транзакцией 1.

    это вроде как подразумевает, что транзакция 1 держит табличную блокировку AUTO-INC (иначе почему бы сработал deadlock detector). мне тут интересно:
    1) почему show innodb status не говорит об этом (для транзакции 1 не указано «HOLDS THE LOCK(S)»)
    2) получается, что транзакция 1 взяла сначала AUTO-INC, затем захотела row lock, а транзакция 2 поступила наоборот. странно как-то

    комментарий от thirstydoh — 05.09.2010 @ 22:02 | Ответить

    • >1) почему show innodb status не говорит об этом (для транзакции 1 не указано «HOLDS THE LOCK(S)»)

      согласен, неклассический auto-increment deadlock, классический (также как у вас для одиночных insert) из бага MySQL выглядит так:
      LATEST DETECTED DEADLOCK
      ------------------------
      *** (1) TRANSACTION:
      TRANSACTION 0 ... setting auto-inc lock
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      TABLE LOCK table lock mode AUTO-INC waiting
      *** (2) TRANSACTION:
      TRANSACTION 0 ... inserting
      *** (2) HOLDS THE LOCK(S):
      TABLE LOCK table lock mode AUTO-INC
      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space ... index `PRIMARY` of table ... lock_mode X locks gap before rec insert intention waiting
      *** WE ROLL BACK TRANSACTION (1)

      > 2) получается, что транзакция 1 взяла сначала AUTO-INC, затем захотела row lock, а транзакция 2 поступила наоборот. странно как-то

      да, немного нелогично, но у MySQL 5.0 встречаются серьёзные проблемы с преобразованием уровня блокировок (lock_mode S lock_mode X, в частности, после после проверки по PRIMARY KEY в Shared mode и перед вставкой записи, которая требует eXclusive mode), и здесь похожий случай
      RECORD LOCKS ... index `i1` of table `db1/t1` ... lock_mode X insert intention waiting
      RECORD LOCKS ... index `i1` of table `db1/t1` trx id 1 1923866935 lock_mode X

      т.е. скорее речь идёт о «смешанном» deadlock

      комментарий от Igor Usoltsev — 06.09.2010 @ 17:09 | Ответить

  4. […] = 2 (“interleaved” lock mode) (более подробное описание есть тут). Дело в том что в версии MySQL 5.1+ эту проблему поправили […]

    Уведомление от MySQL 5.5 AUTO_INC Lock | Blomil — 08.04.2013 @ 01:54 | Ответить


RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: