MySQL 5.5 -- Metadata Locking Within Transactions

在以前的版本中，一個事務請求表的“後設資料鎖”直到“語句”執行完畢；（這個時候整個事務沒有完成）；
這個時候，如果有人執行了DDL語句（如DROP table，可以馬上執行）；那麼在BINLOG日誌中，會先記錄drop table ,再記錄事務的相關語句（因為事務後COMMIT）；這時候到SLAVE上面去應用就會報錯；[@more@]

在5.5中，這個表的“後設資料鎖”一直到整個"事務"全部完成後才會釋放 ；
這也意味著當一個表被一個事務使用（哪怕只有SELECT），在事務結束前就不對這個表作DDL；
這樣也就可以避免上面說到的日誌順序錯誤的問題；

以下是原文：
In previous MySQL versions 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 data definition language (“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.

MySQL 5.5 ensures transaction serialization by not permitting one session to perform a DDL statement
on a table that is used in an incomplete transaction in another session.
This is achieved by acquiring metadata locks on tables
used within a transaction and deferring release of those locks until the transaction ends.
This metadata 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 will block until the transaction ends.

These changes, along with optimizations made to how MySQL internally manages table locking
(LOCK_open) improve performance for OLTP applications, specifically those that require frequent
DDL activity.

## SESSION1

use test
drop table t1;
create table t1
( id int auto_increment primary key ,
c1 char(200) ,
c2 char(200),
c4 int ) engine=innodb default charset = utf8 ;

drop table t5;
create table t5
( id int auto_increment primary key ,
c1 char(200) ,
c2 char(200),
c4 int ) engine=innodb default charset = utf8 ;

start transaction;
insert into t5 (c1,c2,c4) values ('aaaa','bbbbbbb',889);
select count(*) from t1 ;
select sleep(30);
commit;

## Session2
drop table t1 ;

# at V5.1 , this statement will be done ;
# at V5.5 , cause lock waiting ;