MySQL 8.0命令行客户端Error 1205锁等待超时问题求助

问题现象 #

同时打开两个MySQL 8.0命令行客户端操作CIA_DATA.new_table，执行更新语句时触发锁等待超时错误，重启事务无法解决：

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

mysql> update CIA_DATA.new_table set c1 = 2 where c1 = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

调试过程及代码记录 #

后续调试操作及结果如下：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> drop table CIA_DATA.new_table;
ERROR 1051 (42S02): Unknown table 'cia_data.new_table'
mysql> create table CIA_DATA.new_table ( c1 int primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into CIA_DATA.new_table values (1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from CIA_DATA.new_table;
+----+
| c1 |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> update CIA_DATA.new_table set c1 = 2 where c1 = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> close transaction
    -> \c
mysql> close transaction;
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 'close transaction' at line 1
mysql> --innodb-lock-wait-timeout=#
    -> \c
mysql> --innodb-lock-wait-timeout=#;
    ->

问题分析与说明 #

1. 锁超时原因：初始报错是因为另一个客户端的事务持有了CIA_DATA.new_table中c1=1行的排他锁，且未执行COMMIT或ROLLBACK释放锁，导致当前事务等待锁超时。重新建表后无其他事务持有锁，因此更新成功。
2. 常见错误纠正：
   • 关闭事务的正确语法是COMMIT（提交）或ROLLBACK（回滚），close transaction是无效SQL。
   • 修改InnoDB锁等待超时时间，需执行SET GLOBAL innodb_lock_wait_timeout = 60;（数值可按需调整），或在MySQL配置文件中设置，注释写法--innodb-lock-wait-timeout=#无法生效。

内容的提问来源于stack exchange，提问作者kefuyuehan