MySQL · 特性分析 · 跟蹤Metadata lock · 數據庫內核月報

## 背景 MySQL 從5.5.3版本，對Metadata lock進行了調整，主要是MDL鎖持有的周期從語句變成了事務，其原因主要是解決兩個問題：問題1: 破壞事務隔離級別在repeatable read的隔離級別下，多次的select語句執行過程中，會因為其它session的DDL語句，而導致select語句執行的結果不相同，破壞了RR的隔離級別。問題2: 破壞binlog的順序在對表的DML過程中，會因為其它session的DDL語句，導致binlog里的event順序在備庫執行的結果和主庫不一致。從MySQL 5.5.3開始，MDL鎖的持有周期變成了事務，解決了上面提到的兩個問題，但在autocommit=off的情況下，也大大增加了阻塞的可能性。DBA對于阻塞的case，處理起來又比較麻煩，原因就是MDL鎖的阻塞情況沒有暴露明確的信息。從MySQL 5.7.6開始，可以通過performance schema來查詢MDL鎖的持有情況。在開始介紹5.7的跟蹤Metadata lock之前，小編還想討論一下前面提到的這兩個問題，在Oracle數據庫中是如何處理的。 ## Oracle的處理方式首先，Oracle只實現了兩種隔離級別，即read committed和serializable，我們來看下serializable級別下，怎么來處理問題1: 先看如下的case: ~~~ session 1: session 2: -- create table t1(id number); -- insert into t1 values(1); -- commit; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- TEST/TEST@ORCL>select * from t1; -- ID ---------- 1 1 row selected. -- alter table t1 add col number; TEST/TEST@ORCL>select * from t1; -- ID COL ---------- ---------- 1 -- alter table t1 add col1 number default 10; TEST/TEST@ORCL>select * from t1; -- ID COL COL1 ---------- ---------- ---------- 1 ~~~ 可以看到，雖然session是serializable隔離級別，但并沒有產生阻塞的情況，Oracle保證了session1的多次select查詢的返回結果是一樣的，但t1表數據字典的變化是馬上可見的，這個也是符合serializable的要求的，因為隔離級別只定義了數據的可見性，而沒有定義數據字典的可見性。那MySQL能否不要MDL鎖，來達到這樣的效果？答案是否定的，因為Oracle是堆表，alter的操作只更改了數據字典，數據記錄沒有發生變化，縱使加了default值，也是在原記錄上進行的update，完全可以使用scn號來構建一致性讀版本，這樣就不會產生阻塞。而MySQL是IOT表，alter的過程進行了表重建，無法完成read view的構建。那我們再來看問題2，Oracle的處理方式: 對于redo日志，Oracle的處理方式和InnoDB的處理方式一致，也就是當使用redo的時候，日志的寫入并不和事務的提交與否有必然的關系，也不用和提交的順序保持一致。這一點就和binlog區別開來，也就是物理日志是可以避免使用邏輯日志(binlog)帶來的問題。 MySQL如果要避免這兩個問題，而不引入Metadata lock，可以有以下兩個思路： 1. DDL只更改數據字典，行記錄的變更在原記錄上進行，這樣能夠實現多版本，也就是我們常說的在線加字段； 2. 使用物理redo日志，避免使用binlog。這兩種都會對現有的MySQL架構帶來調整，僅供參考。下面我們回來看下對5.7 MDL的tracing。 ## MySQL 5.7 首先，打開metadata locks的tracing功能。 ~~~ mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 ~~~ 打開兩個session，一個select，一個truncate。因為MDL鎖的情況，select會阻塞truncate的操作。 session 1: 操作如下： ~~~ mysql> set session autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit, @@tx_isolation; +--------------+----------------+ | @@autocommit | @@tx_isolation | +--------------+----------------+ | 0 | READ-COMMITTED | +--------------+----------------+ 1 row in set (0.00 sec) mysql> select * from t limit 1; +----+------+ | id | val | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec) ~~~ session 2: 操作如下： ~~~ mysql> truncate table t; ~~~ 結果看到的就是session2被阻塞，接下來check一下performance schema的信息： ~~~ mysql> select * from performance_schema.metadata_locks\G *************************** 1\. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450128308592 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 17 *************************** 2\. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195436144 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: STATEMENT LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5224 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 3\. row *************************** OBJECT_TYPE: SCHEMA OBJECT_SCHEMA: test OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195434272 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5209 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 4\. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450195434368 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 5\. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks OBJECT_INSTANCE_BEGIN: 140450128262384 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 18 5 rows in set (0.00 sec) ~~~ 如上所示，在t表上，持有一個SHARE_READ lock，而且還有一個EXCULSIVE lock請求是pending狀態，也就是我們被阻塞的session 2。在5.7之前，我們可以通過show processlist，來查看MDL阻塞的情況，但無法獲取session 1的信息: ~~~ mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | SCHEMA | test | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | TABLE | test | t | EXCLUSIVE | PENDING | 30 | 8 | truncate table t | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ 3 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+------------------+ | 5 | root | localhost | test | Query | 0 | starting | show processlist | | 8 | root | localhost | test | Query | 50 | Waiting for table metadata lock | truncate table t | +----+------+-----------+------+---------+------+---------------------------------+------------------+ 2 rows in set (0.00 sec) ~~~ 接下來當事務提交了后，釋放MDL鎖再查詢，就看不到MDL鎖的信息了。 ~~~ mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); Empty set (0.01 sec) mysql> select * from t; Empty set (0.00 sec) ~~~ MySQL 5.7可以通過performance schema來檢索MDL鎖阻塞情況，方便DBA來診斷問題。