MySQL事務中的元數據鎖

首先看兩個例子:

mysql -uroot -p

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.41    |
+-----------+
1 row in set (0.00 sec)

# Session 1
mysql> USE test;
Database changed
mysql> DROP TABLE t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> CREATE TABLE t1
    -> (id int auto_increment primary key,
    -> name varchar(20),
    -> password varchar(20),
    -> age int) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> DROP TABLE t2;
ERROR 1051 (42S02): Unknown table 't5'
mysql> CREATE TABLE t2 
    -> (id int auto_increment primary key,
    -> name varchar(20),
    -> password varchar(20),
    -> age int) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2(name, password, age) VALUES('robin', '123456', '18');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(30);
+-----------+
| SLEEP(30) |
+-----------+
|         0 |
+-----------+
1 row in set (30.00 sec)

在Sleep中，打開另一個窗口，開始另一個會話。

mysql -uroot -p

# Session 2
mysql> USE test;

Database changed

mysql> DROP TABLE t1;
# 發生鎖等待

Session 1 Sleep完成後，Commit。

# Session 1
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

此時可以看到Session 2中的刪表操作完成。

mysql> DROP TABLE t1;
Query OK, 0 rows affected (24.17 sec)

同樣，在MySQL 5.1中做相同的測試。

/usr/local/mysql_5.1/bin/mysqld_multi \
--defaults-extra-file=/etc/my_mutli.cnf \
start 5173

mysql --socket=/tmp/mysql5173.sock -uroot -p

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.1.73    |
+-----------+
1 row in set (0.00 sec)

# Session 1
mysql> USE test;
Database changed
mysql> DROP TABLE t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> CREATE TABLE t1
    -> (id int auto_increment primary key,
    -> name varchar(20),
    -> password varchar(20),
    -> age int) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

mysql> DROP TABLE t2;
ERROR 1051 (42S02): Unknown table 't5'
mysql> CREATE TABLE t2 
    -> (id int auto_increment primary key,
    -> name varchar(20),
    -> password varchar(20),
    -> age int) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t2(name, password, age) VALUES('robin', '123456', '18');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT SLEEP(30);
+-----------+
| SLEEP(30) |
+-----------+
|         0 |
+-----------+
1 row in set (30.00 sec)

在Sleep中，打開另一個窗口，開始另一個會話。

mysql --socket=/tmp/mysql5173.sock -uroot -p

# Session 2
mysql> USE test;
Database changed

#  不會發生鎖等待，直接刪除。
mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)

Session 1 Sleep完成後，Commit。

# Session 1
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

可以刪除，MySQL 5.1和MySQL 5.5在元數據鎖中的實現略有不同，5.1刪表不會發生鎖等待，而5.5會。

具體的原因，我查了下官方文檔。

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.

也就是說，5.5中表的“元數據鎖”一直到整個”事務”全部完成後才會釋放，而5.1中，一個事務請求表的“元數據鎖”直到“語句”執行完畢。這個特性的好處在於可以避免復制過程中日志順序錯誤的問題。