mysql - 事务永远等待 InnoDB Lock

Question

我正在使用 innoDB block ，我正在尝试模拟我在生产服务器中遇到的问题。当尝试在 MYSQL 的“事务”表中插入一行时。

我使用以下命令锁定了表:

LOCK TABLES transaction AS tran WRITE;

之后，我从我的网络应用程序创建了一个事务，并且该事务一直被锁阻止。问题是这笔交易被永远阻止。

通过运行以下命令，我可以在 MYSQL 中看到它正在等待:

show full processlist;

并且就呆在那里。

我将 MYSQL 变量 innodb_lock_wait_timeout 更改为 10 秒，但从未失败。

这是进程列表中的响应:

Waiting for table metadata lock

提前致谢!

Answer 1

After that I tried to create a transaction from my web app, and the transaction is kept blocked by the lock. The problem is that this transaction is blocked forever.

请出示您的代码:锁定并插入

LOCK TABLES and UNLOCK TABLES Syntax

LOCK TABLES explicitly acquires table locks for the current client session. Table locks can be acquired for base tables or (as of MySQL 5.0.6) views. You must have the LOCK TABLES privilege, and the SELECT privilege for each object to be locked. UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases.

A table lock protects only against inappropriate reads or writes by other sessions. The session holding the lock, even a read lock, can perform table-level operations such as DROP TABLE. Truncate operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a table lock.

你看到了吗Configurable InnoDB Auto-Increment Lockinginnodb_autoinc_lock_mode？

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

mysql> DELETE  FROM `shop`;
Query OK, 1 row affected (0.00 sec)

mysql> LOCK TABLES shop AS tran WRITE READ;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO shop AS tran VALUES (1,'A',3.45);
        ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS tran VALUES (1,'A',3.45)' at line 1
Imysql> INSERT INTO tran VALUES (1,'A',3.45);
        ERROR 1100 (HY000): Table 'tran' was not locked with LOCK TABLES
mysql> INSERT INTO shop  VALUES (1,'A',3.45);
        ERROR 1100 (HY000): Table 'shop' was not locked with LOCK TABLES
mysql>
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
D
mysql> DELETE  FROM `shop`;
Query OK, 0 rows affected (0.00 sec)