session1> select * from slowtech.t1;
+------+------+------+
| id | name | c1 |
+------+------+------+
| 1 | a | NULL |
+------+------+------+
1 row in set (0.00 sec)
看看binlog的内容,可以看到,truncate操作记录在前,insert操作记录在后。
# at 7140
#180714 19:32:14 server id 1 end_log_pos 7261 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567934/*!*/;
create table t1(id int,name varchar(10)) engine=innodb
/*!*/;
# at 7261
#180714 19:32:30 server id 1 end_log_pos 7333 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
BEGIN
/*!*/;
# at 7333
#180714 19:32:30 server id 1 end_log_pos 7417 Query thread_id=32 exec_time=0 error_code=0
SET TIMESTAMP=1531567950/*!*/;
truncate table t1
/*!*/;
# at 7417
#180714 19:32:30 server id 1 end_log_pos 7444 Xid = 422
COMMIT/*!*/;
# at 7444
#180714 19:32:34 server id 1 end_log_pos 7516 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567954/*!*/;
BEGIN
/*!*/;
# at 7516
#180714 19:32:24 server id 1 end_log_pos 7611 Query thread_id=31 exec_time=0 error_code=0
SET TIMESTAMP=1531567944/*!*/;
insert into t1 values(1,'a')
/*!*/;
# at 7611
#180714 19:32:34 server id 1 end_log_pos 7638 Xid = 421
COMMIT/*!*/;
如果会话2执行的是drop table操作,还会导致主从中断。
有意思的是,如果会话2执行的是alter table操作,其依旧会被阻塞,阻塞时间受innodb_lock_wait_timeout参数限制。
mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
| 54 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 58 | root | localhost | slowtech | Sleep | 1062 | | NULL |
| 60 | root | localhost | slowtech | Query | 11 | copy to tmp table | alter table t1 add c1 int |
+----+------+-----------+----------+---------+------+-------------------+---------------------------+
3 rows in set (0.00 sec)
首先,看看官方的说法,
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 explicitly or implicitly started transaction in another session.
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.
从上面的描述可以看到,
1. MDL出现的初衷就是为了保护一个处于事务中的表的结构不被修改。
2. 这里提到的事务包括两类,显式事务和AC-NL-RO(auto-commit non-locking read-only)事务。显式事务包括两类:1. 关闭AutoCommit下的操作,2. 以begin或start transaction开始的操作。AC-NL-RO可理解为AutoCommit开启下的select操作。
3. MDL是事务级别的,只有在事务结束后才会释放。在此之前,其实也有类似的保护机制,只不过是语句级别的。
需要注意的是,MDL不仅仅适用于表,同样也适用于其它对象,如下表所示,其中,"等待状态"对应的是"show processlist"中的State。
为了提高数据库的并发度,MDL被细分为了11种类型。
MDL_INTENTION_EXCLUSIVE
MDL_SHARED
MDL_SHARED_HIGH_PRIO
MDL_SHARED_READ
MDL_SHARED_WRITE
MDL_SHARED_WRITE_LOW_PRIO
MDL_SHARED_UPGRADABLE
MDL_SHARED_READ_ONLY
MDL_SHARED_NO_WRITE
MDL_SHARED_NO_READ_WRITE
MDL_EXCLUSIVE
常用的有MDL_SHARED_READ,MDL_SHARE D_WRITE及MDL_EXCLUSIVE,其分别用于SELECT操作,DML操作及DDL操作。其它类型的对应操作可参考源码sql/mdl.h。
对于MDL_EXCLUSIVE,官方的解释是,