Вопрос о блокировке MySQL InnoDB

У меня вопрос по MySQL InnoDB. Например: у меня создана следующая таблица:

   mysql>CREATE TABLE IF NOT EXISTS `SeqNum`
   (
     `id` varchar(10) NOT NULL,
     `seq_num` BIGINT(30) default 0,
      PRIMARY KEY(`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  Query OK, 0 rows affected (0.00 sec)

  mysql>INSERT IGNORE INTO `SeqNum` VALUES('current',0);
  Query OK, 1 rows affected (0.00 sec)

Теперь у меня есть два подключения mysql к одной и той же базе данных, я называю их потоком A и B. В потоке A у меня есть следующий оператор SQL:

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select `seq_num` from SeqNum where `id`='current' FOR UPDATE;
       +---------+
       | seq_num |
       +---------+
       |       0 | 
       +---------+
       1 row in set (0.01 sec)

а затем я просто оставляю нить A как есть.

В потоке B я хотел бы выполнить тот же запрос:

   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)

   mysql>SELECT `current_seq_num` FROM SeqNum WHERE `id` = 'current' FOR UPDATE;

поток B выдаст ошибку MySQL 1205 по истечении тайм-аута ожидания блокировки: Превышен тайм-аут ожидания блокировки; попробуйте перезапустить транзакцию.

Это имеет смысл, потому что threadA установил блокировку «X» на эту строку, так что поток B НЕ сможет получить блокировку «X», пока поток A не снимет блокировку.

Теперь мой вопрос: с точки зрения потока B, как я могу узнать, какой поток / соединение блокирует мой запрос (для получения привилегии «UPDATE» для таблицы «SeqNum»), когда MySQL возвращает мне ошибку 1205? Если threadA ничего не делает после того, как получил блокировку X, и я запускаю 'show processlist' в потоке B, все, что у меня есть: несколько потоков со статусом 'Sleep' (я предполагаю, что к базе данных подключено более двух потоков), Я НЕ могу определить, какой поток заблокировал мой запрос?

Надеюсь, я ясно объяснил вопрос. Спасибо!


mysql innodb locking block
person WilliamLou    schedule 21.10.2010    source источник

Ответы (4)


arrow_upward
7
arrow_downward

Плагин InnoDB даст вам четкое представление о заблокированных и заблокированных запросах.

Например

SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
      r.trx_query waiting_query,
      b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
      b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;

предоставит вам блокировку и блокировку транзакций. Только у вас должен быть установлен плагин innodb.

person Rituparna Kashyap    schedule 18.07.2011

arrow_upward
4
arrow_downward

Вы можете легко определить, используете ли вы новую версию (InnoDB Plugin). В information_schema есть несколько таблиц, которые вы можете запросить:

  • ВЫБРАТЬ * ИЗ information_schema.innodb_trx;
  • ВЫБРАТЬ * ИЗ information_schema.innodb_locks;

В таблице innodb_trx должен быть столбец с именем trx_mysql_thread_id (или аналогичный - это trx_mysql_thread_id в MySQL 5.5). Это идентификатор в SHOW PROCESSLIST.

(Обратите внимание, что innodb_locks неправильно назван. Он будет заполнен только для ожидания блокировки, но не для блокировок).

person Morgan Tocker    schedule 27.10.2010

arrow_upward
2
arrow_downward

Я думаю, что на данном этапе мою проблему может решить запрос «SHOW ENGINE INNODB STATUS \ G». Вот информация из руководства MySQL: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html#innodb-standard-monitor

запустите этот запрос в потоке B, вы получите поток, который заблокировал ваш запрос в разделе «транзакция»

person WilliamLou    schedule 26.11.2010

arrow_upward
0
arrow_downward

Вы участвуете в транзакции в обоих сеансах, т.е. набрали ли вы

START TRANSACTION

Обычно поведение будет таким, как вы желаете, т.е. FOR UPDATE будет просто блокироваться до тех пор, пока блокировка не станет доступной (из-за COMMIT или ROLLBACK с первой транзакции)

person Adrian Smith    schedule 21.10.2010
comment
да, обе сессии находятся в транзакции. Я только что добавил в свой вопрос отчет о транзакции. Мой вопрос не в том, почему 1-я сессия заблокировала 2-ю сессию. Но с точки зрения 2-го сеанса, как узнать, какой сеанс заблокировал свой запрос? Конечно, я предполагаю, что сеансов больше двух. - person WilliamLou; 22.10.2010
comment
и я неправильно прочитал ваш вопрос, я думал, у вас сразу же таймаут, извините! - person Adrian Smith; 22.10.2010