MySQL metadata lock

14:14:13[test](;)> begin; Query OK, 0 rows affected (0.00 sec) 14:14:18[test](;)> select * from t1 limit 1; +----+------+------+------+ | id | col1 | col2 | col3 | +----+------+------+------+ | 2 | 3 | 5 | NULL | +----+------+------+------+ 1 row in set (0.00 sec) #session2 14:14:48[test](;)> alter table t1 add col4 int; #session3 | 4834501 | root | localhost | test | Query | 14 | Waiting for table metadata lock | alter table t1 add col4 int |

例二  

session1
14:28:36[test](;)> begin;
Query OK, 0 rows affected (0.00 sec)

14:28:40[test](;)> insert into t1(id) values(2); 
ERROR 1062 (23000): Duplicate entry ‘2‘ for key ‘PRIMARY‘

session2
14:28:40[test](;)> alter table t1 drop col4;

session3
| 4834501 | root        | localhost | test | Query   |      12 | Waiting for table metadata lock                        | alter table t1 drop col4 |

 

MDL的等待超时时长默认值为一年

14:17:32[(none)](;)> select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
|            31536000 |
+---------------------+
1 row in set (0.00 sec)

建议

在操作频繁的时候尽量避免大表的DDL操作；MDL超时时长可以根据业务场景设置；有必要做MDL的监控报警

 

官方文档：https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

MySQL metadata lock

标签：内容   mysq   通过   pre   业务   rom   默认   5.6   log