ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an acti

最新推荐文章于 2024-07-11 17:20:35 发布

原创最新推荐文章于 2024-07-11 17:20:35 发布 · 1w 阅读

1 ·

CC 4.0 BY-SA版权

MariaDB Galera Cluster 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了解决MariaDB中因活跃事务导致无法执行DDL操作的问题。通过解锁表解决了rename table命令执行失败的情况，并探讨了如何查找持有锁的连接。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MariaDB [test]> rename table mgc_new to mgc;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

#perror  1192 
MySQL error code 1192 (ER_LOCK_OR_ACTIVE_TRANSACTION): Can't execute the given command because you have active locked tables or an active transaction

https://mariadb.com/kb/en/library/lock-tables-and-unlock-tables/

While a connection holds an explicit lock on a table, it cannot issue the following: INSERT DELAYED, CREATE TABLE, CREATE TABLE ... LIKE, and DDL statements involving stored programs and views (except for triggers). If you try, the following error will be produced:
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

意思是当一个连接在一个表上显示持有锁，那么这个连接不能做 DELAYED, CREATE TABLE, CREATE TABLE ... LIKE, and DDL ，包括存储过程，视图（触发器除外）。

检查了processlist和INNODB_TRX，INNODB_locks，INNODB_lock_waits但没发现有在执行的事务。
MariaDB [test]> show processlist;                             
+----+-------------+-----------+------+---------+-------+--------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time  | State              | Info             | Progress |
+----+-------------+-----------+------+---------+-------+--------------------+------------------+----------+
|  1 | system user |           | NULL | Sleep   | 77391 | wsrep aborter idle | NULL             |    0.000 |
|  2 | system user |           | NULL | Sleep   |  2856 | committed 1095405  | NULL             |    0.000 |
|  4 | system user |           | NULL | Sleep   |  2727 | committed 1095409  | NULL             |    0.000 |
|  6 | system user |           | NULL | Sleep   |  2886 | committed 1095404  | NULL             |    0.000 |
|  7 | system user |           | NULL | Sleep   |  2418 | committed 1095412  | NULL             |    0.000 |
| 14 | root        | localhost | test | Query   |     0 | init               | show processlist |    0.000 |
| 15 | root        | localhost | test | Sleep   |   978 |                    | NULL             |    0.000 |
| 16 | root        | localhost | test | Sleep   |   913 |                    | NULL             |    0.000 |
+----+-------------+-----------+------+---------+-------+--------------------+------------------+----------+
8 rows in set (0.00 sec)

MariaDB [test]> select * from information_schema.INNODB_TRX ;
Empty set (0.00 sec)


MariaDB [test]> select * from information_schema.INNODB_locks;
Empty set (0.00 sec)


MariaDB [test]> select * from information_schema.INNODB_lock_waits;
Empty set (0.00 sec)

祭出大招unlock tables

MariaDB [test]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

DDL SQL成功执行
MariaDB [test]> rename table mgc to mgc_new;
Query OK, 0 rows affected (0.01 sec)

问题是如何找出这种持有锁的连接呢？目前还没有找到好的方法。