MySQL InnoDB锁问题

Question

我有一个关于 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错误：Lock wait timeout超过；尝试重新启动事务。

这是有道理的，因为线程 A 在该行上放置了“X”锁，因此线程 B 无法获得“X”锁，直到线程 A 释放该锁。

现在，我的问题是：从线程B的角度来看，当MySQL向我返回错误1205时，我如何知道哪个线程/连接阻止了我的请求（以获得表'SeqNum'的'UPDATE'权限）？ 如果线程A在获得X锁后什么也不做，并且我在线程B中运行“显示进程列表”，那么我所拥有的只是：几个具有“睡眠”状态的线程（我假设有两个以上的线程连接到数据库），我无法识别哪个线程阻止了我的请求？

希望我清楚地解释了这个问题。谢谢！

Answer 1

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 插件。

Answer 2

您只能轻松判断您是否使用较新的（InnoDB 插件）版本。 information_schema 中有一些表可以查询：

• SELECT * FROM information_schema.innodb_trx;
• SELECT * FROM information_schema.innodb_locks；

在 innodb_trx 表中应该有一个名为“trx_mysql_thread_id”的列（或类似的列 - 在 MySQL 5.5 中是 trx_mysql_thread_id）。这是 SHOW PROCESSLIST 中的 id。

（请注意，innodb_locks 的命名错误。它只会填充锁等待，而不填充锁）。

Answer 3

我认为在这个阶段，查询“SHOW ENGINE INNODB STATUS\G”可以解决我的问题。
以下是 MySQL 手册中的信息：
http://dev.mysql.com /doc/refman/5.0/en/innodb-monitors.html#innodb-standard-monitor

在线程 B 中运行此查询，您将在“事务”部分获得阻止您的请求的线程

Answer 4

您是否在两个会话上都处于事务中，即您是否键入了

START TRANSACTION

通常的行为将如您所愿，即 FOR UPDATE 只会阻塞，直到锁可用（由于 COMMIT code> 或来自第一个事务的 ROLLBACK）