Waiting for table metadata lock. why?
Reason:
8.10.4. Metadata Locking Within Transactions
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted transaction in another session.
As of MySQL 5.5.3, the server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. For example, if a table t1 is in use by a transaction, another session that attempts to execute DROP TABLE t1 blocks until the transaction ends.
If the server acquires metadata locks for a statement that is syntactically valid but fails during execution, it does not release the locks early. Lock release is still deferred to the end of the transaction because the failed statement is written to the binary log and the locks protect log consistency.
Metadata locks acquired during a PREPARE statement are released once the statement has been prepared, even if preparation occurs within a multiple-statement transaction.
Before MySQL 5.5.3, when a transaction acquired a metadata lock for a table used within a statement, it released the lock at the end of the statement. This approach had the disadvantage that if a DDL statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order.
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html
分享到：
相关推荐
MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）...
MySQL在进行alter table等DDL操作时，有时会出现Waiting for table metadata lock的等待场景。而且，一旦alter table TableA的操作停滞在Waiting for table metadata lock的状态，后续对TableA的任何操作（包括读）...
想必玩过mysql的人对Waiting for table metadata lock肯定不会陌生，一般都是进行alter操作时被堵住了，导致了我们在show processlist 时，看到线程的状态是在等metadata lock。本文会对MySQL表结构变更的Metadata ...
#### 状态：Waiting for table metadata lock 当查询长时间不返回时，可能是由于表级元数据锁（Metadata Lock, MDL）导致的。MDL锁用于保护表结构的安全，确保在结构变更期间数据的一致性。当一个线程试图获取MDL锁...
当你看到 waiting for table metadata lock 时，那就是遇到MDL元数据锁了。本篇文章将会介绍MDL锁的产生与排查过程。 1.什么是MDL锁 MDL全称为metadata lock，即元数据锁。MDL锁主要作用是维护表元数据的数据一致性...
2. `DROP TABLE A` - 在等待`Waiting for table metadata lock`，这意味着想要删除表A，但无法获取到相应的MDL锁。 3. `SELECT * FROM A` - 同样处于`Waiting for table metadata lock`状态，表示查询A表时，需要的...
这种情况通常表现为在`SHOW PROCESSLIST`命令中看到查询状态为“Waiting for table metadata lock”。这表明另一个线程正在请求或持有表的MDL（Metadata Lock）写锁，从而阻止了读取操作，如简单的`SELECT`语句。在...
7. **State**：这是线程执行SQL语句的具体阶段，例如Waiting for table metadata lock（等待表元数据锁）、Sending data（发送数据）或Locked（锁定）。这些状态可以帮助分析查询的执行流程。 8. **Info**：展示...
使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。 这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。在第 6 篇文章《全局锁和表锁：给表加个...
1. **Waiting for table metadata lock：** - **含义：**表明当前线程正在等待获取表的元数据锁。 - **解决方法：** - 检查是否有其他线程正在对同一张表进行结构修改（如ALTER TABLE）。 - 尽量减少对表结构的...
`STATE`列显示了每个线程当前的活动状态，如`Waiting for table metadata lock`、`Sending data`等，有助于诊断阻塞和性能问题。 17. 松散索引和紧凑索引 松散索引（也称为非聚簇索引）不包含主键数据，只存储索引...
如果某个会话处于等待状态（Waiting for table metadata lock），这表明它正在等待另一个会话释放元数据锁。 2. 通过`information_schema.innodb_trx`表：这个内部表包含了所有活跃的InnoDB事务信息。通过查询此表...
SHOW FULL PROCESSLIST WHERE State = 'Waiting for table metadata lock'; -- 强制结束指定ID的进程 KILL [process_id]; ``` 请注意，以上操作应谨慎执行，尤其是KILL命令，因为它可能会导致未保存的数据丢失或...
- `State`列中的各种状态可以帮助识别潜在的问题，比如长时间的`Waiting for table metadata lock`可能表明有锁定问题，`Copying to tmp table on disk`可能表示内存不足，导致数据需写入磁盘临时表。 5. **优化和...
7. **State**：线程执行SQL语句的具体状态，如Waiting for table metadata lock（等待表元数据锁）或Sending data（发送数据）等，这些状态提供了关于查询执行进度的线索。 8. **Info**：显示正在执行的SQL语句，但...