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 exceeded; try restarting transaction。这是有道理的，因为线程A在该行上放置了“X”锁，所以线程B在线程A释放锁之前不能获取“X”锁。

现在，我的问题是：从线程B的角度来看，当MySQL向我返回错误1205时，我怎样才能知道哪个线程/连接阻止了我获取表“SeqNum”的“UPDATE”权限？如果线程A在获取“X”锁后什么都不做，并且我在线程B中运行'show processlist'，那么我所拥有的只有：几个处于“Sleep”状态的线程（我假设有两个以上连接到数据库的线程），我无法确定哪个线程阻止了我的请求。

希望我已经清晰地解释了问题。谢谢!

- WilliamLou

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插件可以为您提供锁定和阻塞事务功能。

- Rituparna Kashyap

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的名称不正确。它仅在锁等待而不是锁定时才会填充。）

- Morgan Tocker

Answer 3

我认为在这个阶段，查询 "SHOW ENGINE INNODB STATUS\G" 可以解决我的问题。

以下是MySQL手册中的信息: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html#innodb-standard-monitor 在线程 B 中运行此查询，您将在“事务”部分中获得阻止您请求的线程。

- WilliamLou

Answer 4

你在两个会话中都进行了事务操作吗？也就是说，你输入了

START TRANSACTION

通常情况下，行为应该是您所期望的，即FOR UPDATE只会在锁可用时（由第一次事务的COMMIT或ROLLBACK）才会阻塞。

- Adrian Smith

---

是的，两个会话都在一个事务中。我刚刚在我的问题中添加了事务语句。我的问题不是第一个会话为什么阻塞了第二个会话。但是，从第二个会话的角度来看，如何知道哪个会话阻塞了它的请求？当然，我假设有超过两个会话。 - WilliamLou

我误解了你的问题，我以为你立即超时了，对不起！ - Adrian Smith