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之类的状态,其实MDL LOCK是MYSQL上层一个非常复杂的子系统,有自己的死锁检测机制 (无向图?)而大家一般口中的是不是锁表了其实就是指的它,可见的它的关键性和严重性...
当你看到 waiting for table metadata lock 时,那就是遇到MDL元数据锁了。本篇文章将会介绍MDL锁的产生与排查过程。 1.什么是MDL锁 MDL全称为metadata lock,即元数据锁。MDL锁主要作用是维护表元数据的数据一致性...
遇到故障,我们往往想的是如何解决这个故障,而不是从故障...schema.processlist来看有大量的 Waiting for table metadata lock 情急之下他杀掉了一大堆线程后发现还是不能恢复,最后杀掉了一个没有及时提交的事物才恢
JDBC Metadata MySQL and Oracle Recipes
MySQL Metadata Locking
Cognos® 8 FRAMEWORK MANAGER GUIDELINES FOR MODELING METADATA
PACKET 保证完整版 MySQL for Python Integrate the flexibility of Python and the power of MySQL to boost the productivity of your ...Chapter 13: Showing MySQL Metadata 369 Chapter 14: Disaster Recovery 393
Create the City Table in the test Database for Code Examples ?Testing the MySQL Database Connectivity With the Connector/C++ ?Using Prepared Statements ?Using Transactions ?Accessing Result Set ...
SAP.Middleware.Connector.RfcCommunicationException:“destination XXXX failed when calling RFC_METADATA_GET -- see log for details” 猜测的原因: 老的DLL库在获取接口实例时,会触发“RFC_METADATA_GET”...
Display Metadata [3], while containing provisions for future HDR EOTFs and metadata. It is anticipated that these data structures will be extended to include additional EOTF and HDR metadata ...
Flink无法获取Kafka Topic Metadata异常及解决.docx
hibernate常见错误解决方案
Tagging People powered Metadata for the Social Web - New Riders Press.rar