MySQL metadata lock

Yesterday I encountered a problem in the MySQL replication experiment:

Database Version: 5.5.21-log
Operating System: windows xp

For master/slave replication, autocommit = 0 binlog-format = mixed tx-isolation = read-committed

Experiment process:
1.
Create a table on the master:
Create table t1 (a int) engine = innodb;
2.
Query on slave:
Select * from t1;
Empty set...
Normal execution
3.
Delete a table on the master:
Drop table t1;
4.
Query on slave:
Select * from t1;
Empty set...
The results at this time are confusing. How can we query them?
5.
View thread on slave:
Show processlist \ G
...
State: waiting for table metadata lock
Info: drop table 't1'/* generated by server */
It is shown that a select statement is executed on slave while waiting for metadata lock. Will a transaction be started?
6.
Execute commit on slave;
Commit;
Select * from T1;
Table 'T1 'doesn' t exist
Now the table is gone.

Why does the drop statement not be copied immediately on slave? What generates metadata lock? How can we copy the drop statement immediately without the need for commit first?

 

This is because metadata lock is added after MySQL 5.5.3. This lock limits the execution of DDL statements before a transaction is committed. What's confusing is that even if only select is used, the DDL will be blocked as long as no commit (which is obviously different from the "transaction" that Oracle considers), as shown in the experiment: autocommit = 0, A select statement on slave enables a transaction, and the replication thread and the select issuing thread are two threads. Therefore, the drop statement can be executed only after the SELECT statement issuing thread executes commit.

 

Reference: http://www.chriscalender.com /? Tag = metadata-Lock