主从复制出现metadata lock

[lvxiaoqing]

现象：主从复制的时候，如果在Master顺序执行建表(test)，insert数据，commit。然后在Slave查询test，再返回Master进行drop test操作，这个时候发现Slave中出现Waiting for table metadata lock 的情况，但是如果不进行Slave的查询操作的话，Slave上能正常安装Master的操作复制。

我是关闭的自动提交的，难道是因为查询也会默认开始一个事务？

Master操作

MariaDB [rmsdb]> create table test(a int);
Query OK, 0 rows affected (0.62 sec)

MariaDB [rmsdb]> insert into test values(1);
Query OK, 1 row affected (0.01 sec)

MariaDB [rmsdb]> commit;
Query OK, 0 rows affected (0.17 sec)

然后Slave操作

MariaDB [rmsdb]> select * from test;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

接着Master操作

MariaDB [rmsdb]> drop table test;
Query OK, 0 rows affected (0.02 sec)

然后Slave查看

MariaDB [rmsdb]> show processlist;
+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+
| Id | User        | Host      | db    | Command | Time  | State                            | Info                                        | Progress |
+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+
|  4 | system user |           | rmsdb | Connect |  -511 | Waiting for table metadata lock  | DROP TABLE `test` /* generated by server */ |    0.000 |
| 14 | system user |           | NULL  | Connect | 73740 | Waiting for master to send event | NULL                                        |    0.000 |
| 15 | root        | localhost | rmsdb | Query   |     0 | init                             | show processlist                            |    0.000 |
+----+-------------+-----------+-------+---------+-------+----------------------------------+-----------------------------------------

PS:楼主用的mariadb，这个应该不影响吧？

[rucypli]

怎么time是负数 两台机器系统时间差的有点多哦

[lvxiaoqing]

引用 1 楼 rucypli 的回复:

怎么time是负数 两台机器系统时间差的有点多哦

我重新调整了时间，还是有问题，不明白为什么这样。。

MariaDB [rmsdb]> show processlist;
+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+
| Id | User        | Host      | db    | Command | Time  | State                            | Info                                        | Progress |
+----+-------------+-----------+-------+---------+-------+----------------------------------+---------------------------------------------+----------+
|  4 | system user |           | rmsdb | Connect |     7 | Waiting for table metadata lock  | DROP TABLE `test` /* generated by server */ |    0.000 |
| 14 | system user |           | NULL  | Connect | 94223 | Waiting for master to send event | NULL                                        |    0.000 |
| 15 | root        | localhost | rmsdb | Query   |     0 | init                             | show

[lvxiaoqing]

原因找着了，InnoDB引擎的话，关闭自动提交，所有查询都在同一个事务中，直到显式的commit或者rollback
参考书籍：mysql高性能 1.3节