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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-1043464/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703656/viewspace-1043464/