MYSQL中一個特殊的MDL LOCK死鎖案列
水平有限 如有錯誤請指出共同探討
版本:5.7.19 mysql+innodb
本文中所說的上文是如下文章，也是討論MDL LOCK死鎖的基礎，包含了很多MDL LOCK的基礎知識建議
好好閱讀
http://blog.itpub.net/7728585/viewspace-2143093/
一、問題由來
前段開發反饋時間線上資料庫老是出現死鎖情況，而我們設定了innodb_print_all_deadlocks，但是在
相應的時間點沒有找到任何相應的死鎖的資訊，從而導致我們獲得任何有用的資訊，也不能定位問題的
原因。
二、問題思考和分析
後來開發將出錯碼發給我，我看到這個錯誤碼確實是MYSQL報出來的如下：
{ "ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction" },
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
那麼能夠肯定一點這個死鎖是MYSQL報出來的。那麼為什麼innodb沒有任何表示呢？難道是什麼BUG
繼而在
找了一下BUGS也沒有找，後來我思考這個問題，既然是死鎖就會是相應的死鎖檢測演算法丟擲來，我們知道MYSQL
上層還有MDL LOCK，並不是只有innodb相應的lock才會有進行死鎖檢測，會不是因為MDL LOCK死鎖照成的呢？
我在MDL.CC中找到了如下程式碼其實也是我上文說的MDL_context::acquire_lock 函式，上文說過這個函式是
根據MDL_REQUEST嘗試獲得MDL LOCK的主要函式。
注意紅色部分，其實這裡死鎖問題基本確定不是innodb層觸發的，既然不是innodb下層觸發，innodb當然不會列印
出任何資訊。為什麼innodb層不列印死鎖資訊的原因找到了，但是什麼情況下會出現MDL LOCK的死鎖呢?
三、問題定位
既然要產生死鎖必須滿足一些條件：
1、至少2個獨立的執行緒(會話)。
2、單位操作中包含多個相對獨立的加鎖步驟，有一定的時間差
比如一個事物裡面的多個操作
還比如repeat操作(雖然非常段也是有的)。 關於replace的死鎖問題參考我的文章如下：
http://blog.itpub.net/7728585/viewspace-2141409/
3、多個執行緒(會話)之間加鎖物件必須有相互等待的情況發生，並且等待出現環狀。
順便提一句，死鎖一般處理方式有3種
A、事前預測
B、資源分級
C、事後檢測釋放
而MDL LOCK和INNODB都使用了C時候檢測釋放，其演算法應該是利用圖的(DSF or BSF)遍歷進行判定，這一塊以後要好好看一下。
那麼我考慮到使用MYSQLDUMP進行的備份的時候可能出現一些MDL LOCK的情況，比如加上-F, --flush-logs --single-transaction會
短暫的使用 flush table with read lock;
如下：
2017-08-08T06:22:44.916055Z 15 Connect root@localhost on using Socket
2017-08-08T06:22:44.916270Z 15 Query /*!40100 SET @@SQL_MODE='' */
2017-08-08T06:22:44.916521Z 15 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-08T06:22:44.916604Z 15 Query FLUSH TABLES
2017-08-08T06:22:44.922889Z 15 Query FLUSH TABLES WITH READ LOCK
2017-08-08T06:22:44.923009Z 15 Refresh
/mysqldata/mysql5.7/bin/mysqld, Version: 5.7.13-log (Source distribution). started with:
Tcp port: 3307 Unix socket: /mysqldata/mysql5.7/mysqld3307.sock
Time Id Command Argument
2017-08-08T06:22:44.949003Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-08T06:22:44.949089Z 15 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-08T06:22:44.949200Z 15 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-08T06:22:44.953060Z 15 Query SELECT @@GLOBAL.GTID_EXECUTED
2017-08-08T06:22:44.953160Z 15 Query UNLOCK TABLES
如前文所述這個操作會在GLOBAL上加一把S鎖
2017-08-03T18:19:11.603971Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-03T18:19:11.603994Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S)
而導致所有的需要在GLOBAL上加IX鎖的操作全部等待比如(DML操作/DDL操作)，但是這裡的flush table with read lock
並不符合上面描述的死鎖產生的條件，不管如何先檢查一下是不是MYSQLDUMP的問題，這一檢查真的檢查到了問題，我們
MYSQL備份的時候，進行了一次單獨的表結構的備份，同事認為很快而沒有加--single-transaction，然後檢查備份結束的
時間，基本和死鎖出現的時間點一致，那麼問題轉為如果不加--single-transaction，MYSQLDUMP如何加鎖。
實際上這個時候從general日誌來看，加鎖如下：
2017-08-08T06:33:22.427691Z 20 Init DB dumptest
2017-08-08T06:33:22.427794Z 20 Query SHOW CREATE DATABASE IF NOT EXISTS `dumptest`
2017-08-08T06:33:22.428100Z 20 Query show tables
2017-08-08T06:33:22.428443Z 20 Query LOCK TABLES `kkkk` READ /*!32311 LOCAL */,`llll` READ /*!32311 LOCAL */
2017-08-08T06:33:22.428551Z 20 Query show table status like 'kkkk'
2017-08-08T06:33:22.428870Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.428929Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429026Z 20 Query show create table `kkkk`
2017-08-08T06:33:22.429157Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.429212Z 20 Query show fields from `kkkk`
2017-08-08T06:33:22.429534Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `kkkk`
2017-08-08T06:33:22.429680Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429721Z 20 Query use `dumptest`
2017-08-08T06:33:22.429769Z 20 Query select @@collation_database
2017-08-08T06:33:22.429830Z 20 Query SHOW TRIGGERS LIKE 'kkkk'
2017-08-08T06:33:22.430141Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430195Z 20 Query show table status like 'llll'
2017-08-08T06:33:22.430411Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.430456Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430493Z 20 Query show create table `llll`
2017-08-08T06:33:22.430557Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430599Z 20 Query show fields from `llll`
2017-08-08T06:33:22.430813Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `llll`
2017-08-08T06:33:22.430909Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430945Z 20 Query use `dumptest`
2017-08-08T06:33:22.431003Z 20 Query select @@collation_database
2017-08-08T06:33:22.431098Z 20 Query SHOW TRIGGERS LIKE 'llll'
2017-08-08T06:33:22.431330Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.431375Z 20 Query UNLOCK TABLES
我的dumptest資料只有兩個表kkkk和llll，我們可以看到mysqldump透過LOCK TABLES `kkkk` READ ,`llll` READ 進行加鎖，
備份完成後使用unlock tables解鎖。其實這就是問題的根本原因。它會照成MDL死鎖的產生。如果加上--single-transaction 則不會，
會使用SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ改變隔離級別為RR使用事物來保證資料的一致，
而不是LOCK TABLES來保證資料的一致性。
四、模擬MDL死鎖和相容性分析
首先我們需要看一下 LOCK TABLES a READ ,b READ 到底加了哪些MDL LOCK，這個透過我在MDL.CC中加入的列印函式my_print_ticket可以看到(具體在上文)
如下：
LOCK TABLES a READ ,b READ ;
2017-08-08T07:12:11.764164Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764184Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764201Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764258Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:12:11.764344Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764363Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764376Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:12:11.764586Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764605Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764620Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764634Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:12:11.764648Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764687Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764704Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
我們可以清楚的看到本語句會對a和b分別呼叫函式MDL_context::acquire_lock進行加鎖為object的SRO MDL鎖型別，雖然是一個語句但是加鎖卻是分開的，
對於SRO鎖相容性如下
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SRO | + + + - - + + + - - |
可以看到SRO和SW\SNRW\X均不相容，也就是和DML(SW)\SNRW(LOCK TABLE WRITE)\X(DDL) 不相容。
如果有另外一個事物需要對a和b進行DML操作，那麼MDL 死鎖出現瞭如下：
THREAD1 THREAD2
begin;(事物開始)
insert into b values(1);
LOCK TABLES a READ ,b READ ; (堵塞)
insert into a values(1);(堵塞死鎖)
現在我們來分析一下
執行緒1
begin;(事物開始)
insert into b values(1);
獲得MDL LOCK 如下：
2017-08-08T07:25:45.875676Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875699Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875713Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:25:45.875726Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875740Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:25:45.875757Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:25:45.875772Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:25:45.875798Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875812Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875826Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:25:45.875839Z 3 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:25:45.875853Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:25:45.875875Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875888Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:25:45.875900Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:25:45.875913Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
GLOBAL先不考慮，可以看到會在b表上獲得SW的object mdl lock.
執行緒2
LOCK TABLES a READ ,b READ ; (堵塞)
2017-08-08T07:27:28.684508Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:27:28.684534Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684546Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684558Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:27:28.684569Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684580Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684592Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684604Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:27:28.684622Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:27:28.684633Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684644Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684824Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:27:28.684838Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684850Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684871Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684884Z 2 [Note] (------->MDL PRINT) Mdl status is:EMPTY
我們可以清楚的看到a表上的SRO獲得成功，但是b表上的MDL LOCK 需求則進入了MDL_LOCK WAIT QUE等待了
及由於b表已經被執行緒1的insert語句拿到了SW鎖，執行緒2想要獲得b表的SRO鎖需要等待
Waiting for table metadata lock | lock table a read,b read
執行緒1
再次執行
mysql> insert into a values(1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2017-08-08T07:31:13.053322Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.053359Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053388Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:31:13.053401Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:31:13.053417Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:31:13.053589Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:31:13.053613Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.053658Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:31:13.053676Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053694Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.053711Z 3 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.053728Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.054065Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:31:13.054092Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.054118Z 3 [Note] (-------->MDL PRINT) Mdl status is:VICTIM
我們這裡執行緒1想要獲得a表的sw mdl lock，但是執行緒2持有a表的SRO mdl lock，顯然他們不相容，死鎖出現(VICTIM)
MDL死鎖出現後根據一個權重來進行回滾如下：
static const uint DEADLOCK_WEIGHT_DML= 0;
static const uint DEADLOCK_WEIGHT_ULL= 50;
static const uint DEADLOCK_WEIGHT_DDL= 100;
雖然沒有研究死鎖檢測的原理，但是這裡應該是帶權重的一個圖，回滾權重小的操作。最後執行緒1的事物操作被回滾了。
從列印來看事物回滾後，LOCK TABLES a READ ,b READ ;獲得了全部的SRO MDL LOCK成功了，列印如下：
2017-08-08T07:31:13.062693Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062710Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062725Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.062741Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.062756Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.062768Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.062781Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.062795Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.062954Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062974Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062989Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.063005Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:31:13.063023Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.063039Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.063052Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.063065Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
總結一下這裡的死鎖
執行緒1 獲得b的SW MDL LOCK-->執行緒2 獲得a的SRO MDL LOCK，而拿不到b的SRO MDL LOCK(堵塞)-->執行緒1 拿不到a的SW MDL LOCK(堵塞)
這樣 執行緒1和執行緒2都堵塞了，根據MDL LOCK死鎖圖的權重釋放 執行緒1因為它都是DML操作。
五、處理
知道原因處理就相對簡單了，備份表結構也加上 --single-transaction就好了，雖然備份表結構時間很短，但是高壓力下也會出現死鎖的情況。
作者微信:
版本:5.7.19 mysql+innodb
本文中所說的上文是如下文章，也是討論MDL LOCK死鎖的基礎，包含了很多MDL LOCK的基礎知識建議
好好閱讀
http://blog.itpub.net/7728585/viewspace-2143093/
一、問題由來
前段開發反饋時間線上資料庫老是出現死鎖情況，而我們設定了innodb_print_all_deadlocks，但是在
相應的時間點沒有找到任何相應的死鎖的資訊，從而導致我們獲得任何有用的資訊，也不能定位問題的
原因。
二、問題思考和分析
後來開發將出錯碼發給我，我看到這個錯誤碼確實是MYSQL報出來的如下：
{ "ER_LOCK_DEADLOCK", 1213, "Deadlock found when trying to get lock; try restarting transaction" },
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
那麼能夠肯定一點這個死鎖是MYSQL報出來的。那麼為什麼innodb沒有任何表示呢？難道是什麼BUG
繼而在
找了一下BUGS也沒有找，後來我思考這個問題，既然是死鎖就會是相應的死鎖檢測演算法丟擲來，我們知道MYSQL
上層還有MDL LOCK，並不是只有innodb相應的lock才會有進行死鎖檢測，會不是因為MDL LOCK死鎖照成的呢？
我在MDL.CC中找到了如下程式碼其實也是我上文說的MDL_context::acquire_lock 函式，上文說過這個函式是
根據MDL_REQUEST嘗試獲得MDL LOCK的主要函式。
點選(此處)摺疊或開啟
-
switch (wait_status)
-
{
-
case MDL_wait::VICTIM:
-
my_error(ER_LOCK_DEADLOCK, MYF(0));
-
break;
-
case MDL_wait::TIMEOUT:
-
my_error(ER_LOCK_WAIT_TIMEOUT, MYF(0));
-
break;
-
case MDL_wait::KILLED:
-
if (get_owner()->is_killed() == ER_QUERY_TIMEOUT)
-
my_error(ER_QUERY_TIMEOUT, MYF(0));
-
else
-
my_error(ER_QUERY_INTERRUPTED, MYF(0));
-
break;
-
default:
-
DBUG_ASSERT(0);
-
break;
- }
出任何資訊。為什麼innodb層不列印死鎖資訊的原因找到了，但是什麼情況下會出現MDL LOCK的死鎖呢?
三、問題定位
既然要產生死鎖必須滿足一些條件：
1、至少2個獨立的執行緒(會話)。
2、單位操作中包含多個相對獨立的加鎖步驟，有一定的時間差
比如一個事物裡面的多個操作
還比如repeat操作(雖然非常段也是有的)。 關於replace的死鎖問題參考我的文章如下：
http://blog.itpub.net/7728585/viewspace-2141409/
3、多個執行緒(會話)之間加鎖物件必須有相互等待的情況發生，並且等待出現環狀。
順便提一句，死鎖一般處理方式有3種
A、事前預測
B、資源分級
C、事後檢測釋放
而MDL LOCK和INNODB都使用了C時候檢測釋放，其演算法應該是利用圖的(DSF or BSF)遍歷進行判定，這一塊以後要好好看一下。
那麼我考慮到使用MYSQLDUMP進行的備份的時候可能出現一些MDL LOCK的情況，比如加上-F, --flush-logs --single-transaction會
短暫的使用 flush table with read lock;
如下：
2017-08-08T06:22:44.916055Z 15 Connect root@localhost on using Socket
2017-08-08T06:22:44.916270Z 15 Query /*!40100 SET @@SQL_MODE='' */
2017-08-08T06:22:44.916521Z 15 Query /*!40103 SET TIME_ZONE='+00:00' */
2017-08-08T06:22:44.916604Z 15 Query FLUSH TABLES
2017-08-08T06:22:44.922889Z 15 Query FLUSH TABLES WITH READ LOCK
2017-08-08T06:22:44.923009Z 15 Refresh
/mysqldata/mysql5.7/bin/mysqld, Version: 5.7.13-log (Source distribution). started with:
Tcp port: 3307 Unix socket: /mysqldata/mysql5.7/mysqld3307.sock
Time Id Command Argument
2017-08-08T06:22:44.949003Z 15 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2017-08-08T06:22:44.949089Z 15 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2017-08-08T06:22:44.949200Z 15 Query SHOW VARIABLES LIKE 'gtid\_mode'
2017-08-08T06:22:44.953060Z 15 Query SELECT @@GLOBAL.GTID_EXECUTED
2017-08-08T06:22:44.953160Z 15 Query UNLOCK TABLES
如前文所述這個操作會在GLOBAL上加一把S鎖
2017-08-03T18:19:11.603971Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-03T18:19:11.603994Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED(S)
而導致所有的需要在GLOBAL上加IX鎖的操作全部等待比如(DML操作/DDL操作)，但是這裡的flush table with read lock
並不符合上面描述的死鎖產生的條件，不管如何先檢查一下是不是MYSQLDUMP的問題，這一檢查真的檢查到了問題，我們
MYSQL備份的時候，進行了一次單獨的表結構的備份，同事認為很快而沒有加--single-transaction，然後檢查備份結束的
時間，基本和死鎖出現的時間點一致，那麼問題轉為如果不加--single-transaction，MYSQLDUMP如何加鎖。
實際上這個時候從general日誌來看，加鎖如下：
2017-08-08T06:33:22.427691Z 20 Init DB dumptest
2017-08-08T06:33:22.427794Z 20 Query SHOW CREATE DATABASE IF NOT EXISTS `dumptest`
2017-08-08T06:33:22.428100Z 20 Query show tables
2017-08-08T06:33:22.428443Z 20 Query LOCK TABLES `kkkk` READ /*!32311 LOCAL */,`llll` READ /*!32311 LOCAL */
2017-08-08T06:33:22.428551Z 20 Query show table status like 'kkkk'
2017-08-08T06:33:22.428870Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.428929Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429026Z 20 Query show create table `kkkk`
2017-08-08T06:33:22.429157Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.429212Z 20 Query show fields from `kkkk`
2017-08-08T06:33:22.429534Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `kkkk`
2017-08-08T06:33:22.429680Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.429721Z 20 Query use `dumptest`
2017-08-08T06:33:22.429769Z 20 Query select @@collation_database
2017-08-08T06:33:22.429830Z 20 Query SHOW TRIGGERS LIKE 'kkkk'
2017-08-08T06:33:22.430141Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430195Z 20 Query show table status like 'llll'
2017-08-08T06:33:22.430411Z 20 Query SET SQL_QUOTE_SHOW_CREATE=1
2017-08-08T06:33:22.430456Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430493Z 20 Query show create table `llll`
2017-08-08T06:33:22.430557Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.430599Z 20 Query show fields from `llll`
2017-08-08T06:33:22.430813Z 20 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `llll`
2017-08-08T06:33:22.430909Z 20 Query SET SESSION character_set_results = 'binary'
2017-08-08T06:33:22.430945Z 20 Query use `dumptest`
2017-08-08T06:33:22.431003Z 20 Query select @@collation_database
2017-08-08T06:33:22.431098Z 20 Query SHOW TRIGGERS LIKE 'llll'
2017-08-08T06:33:22.431330Z 20 Query SET SESSION character_set_results = 'utf8'
2017-08-08T06:33:22.431375Z 20 Query UNLOCK TABLES
我的dumptest資料只有兩個表kkkk和llll，我們可以看到mysqldump透過LOCK TABLES `kkkk` READ ,`llll` READ 進行加鎖，
備份完成後使用unlock tables解鎖。其實這就是問題的根本原因。它會照成MDL死鎖的產生。如果加上--single-transaction 則不會，
會使用SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ改變隔離級別為RR使用事物來保證資料的一致，
而不是LOCK TABLES來保證資料的一致性。
四、模擬MDL死鎖和相容性分析
首先我們需要看一下 LOCK TABLES a READ ,b READ 到底加了哪些MDL LOCK，這個透過我在MDL.CC中加入的列印函式my_print_ticket可以看到(具體在上文)
如下：
LOCK TABLES a READ ,b READ ;
2017-08-08T07:12:11.764164Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764184Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764201Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764258Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:12:11.764344Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764363Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764376Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:12:11.764586Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:12:11.764605Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:12:11.764620Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:12:11.764634Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:12:11.764648Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:12:11.764687Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:12:11.764704Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
我們可以清楚的看到本語句會對a和b分別呼叫函式MDL_context::acquire_lock進行加鎖為object的SRO MDL鎖型別，雖然是一個語句但是加鎖卻是分開的，
對於SRO鎖相容性如下
Request | Granted requests for lock |
type | S SH SR SW SWLP SU SRO SNW SNRW X |
----------+---------------------------------------------+
SRO | + + + - - + + + - - |
可以看到SRO和SW\SNRW\X均不相容，也就是和DML(SW)\SNRW(LOCK TABLE WRITE)\X(DDL) 不相容。
如果有另外一個事物需要對a和b進行DML操作，那麼MDL 死鎖出現瞭如下：
THREAD1 THREAD2
begin;(事物開始)
insert into b values(1);
LOCK TABLES a READ ,b READ ; (堵塞)
insert into a values(1);(堵塞死鎖)
現在我們來分析一下
執行緒1
begin;(事物開始)
insert into b values(1);
獲得MDL LOCK 如下：
2017-08-08T07:25:45.875676Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875699Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875713Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:25:45.875726Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875740Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:25:45.875757Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:25:45.875772Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:25:45.875798Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:25:45.875812Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:25:45.875826Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:25:45.875839Z 3 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:25:45.875853Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:25:45.875875Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:25:45.875888Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:25:45.875900Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:25:45.875913Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
GLOBAL先不考慮，可以看到會在b表上獲得SW的object mdl lock.
執行緒2
LOCK TABLES a READ ,b READ ; (堵塞)
2017-08-08T07:27:28.684508Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:27:28.684534Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684546Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684558Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:27:28.684569Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684580Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684592Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684604Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:27:28.684622Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:27:28.684633Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:27:28.684644Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:27:28.684824Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:27:28.684838Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:27:28.684850Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:27:28.684871Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:27:28.684884Z 2 [Note] (------->MDL PRINT) Mdl status is:EMPTY
我們可以清楚的看到a表上的SRO獲得成功，但是b表上的MDL LOCK 需求則進入了MDL_LOCK WAIT QUE等待了
及由於b表已經被執行緒1的insert語句拿到了SW鎖，執行緒2想要獲得b表的SRO鎖需要等待
Waiting for table metadata lock | lock table a read,b read
執行緒1
再次執行
mysql> insert into a values(1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
2017-08-08T07:31:13.053322Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.053359Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053388Z 3 [Note] (--->MDL PRINT) Namespace is:GLOBAL
2017-08-08T07:31:13.053401Z 3 [Note] (---->MDL PRINT) Fast path is:(Y)
2017-08-08T07:31:13.053417Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_INTENTION_EXCLUSIVE(IX)
2017-08-08T07:31:13.053589Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_STATEMENT
2017-08-08T07:31:13.053613Z 3 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.053658Z 3 [Note] (acquire_lock)THIS MDL LOCK acquire WAIT(MDL_LOCK WAIT QUE)!
2017-08-08T07:31:13.053676Z 3 [Note] (>MDL PRINT) Thread id is 3:
2017-08-08T07:31:13.053694Z 3 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.053711Z 3 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.053728Z 3 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.054065Z 3 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_WRITE(SW)
2017-08-08T07:31:13.054092Z 3 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.054118Z 3 [Note] (-------->MDL PRINT) Mdl status is:VICTIM
我們這裡執行緒1想要獲得a表的sw mdl lock，但是執行緒2持有a表的SRO mdl lock，顯然他們不相容，死鎖出現(VICTIM)
MDL死鎖出現後根據一個權重來進行回滾如下：
static const uint DEADLOCK_WEIGHT_DML= 0;
static const uint DEADLOCK_WEIGHT_ULL= 50;
static const uint DEADLOCK_WEIGHT_DDL= 100;
雖然沒有研究死鎖檢測的原理，但是這裡應該是帶權重的一個圖，回滾權重小的操作。最後執行緒1的事物操作被回滾了。
從列印來看事物回滾後，LOCK TABLES a READ ,b READ ;獲得了全部的SRO MDL LOCK成功了，列印如下：
2017-08-08T07:31:13.062693Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062710Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062725Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.062741Z 2 [Note] (-->MDL PRINT) OBJ_name is:a
2017-08-08T07:31:13.062756Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.062768Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.062781Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.062795Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
2017-08-08T07:31:13.062954Z 2 [Note] (acquire_lock)THIS MDL LOCK acquire ok!
2017-08-08T07:31:13.062974Z 2 [Note] (>MDL PRINT) Thread id is 2:
2017-08-08T07:31:13.062989Z 2 [Note] (->MDL PRINT) DB_name is:test
2017-08-08T07:31:13.063005Z 2 [Note] (-->MDL PRINT) OBJ_name is:b
2017-08-08T07:31:13.063023Z 2 [Note] (--->MDL PRINT) Namespace is:TABLE
2017-08-08T07:31:13.063039Z 2 [Note] (----->MDL PRINT) Mdl type is:MDL_SHARED_READ_ONLY(SRO)
2017-08-08T07:31:13.063052Z 2 [Note] (------>MDL PRINT) Mdl duration is:MDL_TRANSACTION
2017-08-08T07:31:13.063065Z 2 [Note] (------->MDL PRINT) Mdl status is:GRANTED
總結一下這裡的死鎖
執行緒1 獲得b的SW MDL LOCK-->執行緒2 獲得a的SRO MDL LOCK，而拿不到b的SRO MDL LOCK(堵塞)-->執行緒1 拿不到a的SW MDL LOCK(堵塞)
這樣 執行緒1和執行緒2都堵塞了，根據MDL LOCK死鎖圖的權重釋放 執行緒1因為它都是DML操作。
五、處理
知道原因處理就相對簡單了，備份表結構也加上 --single-transaction就好了，雖然備份表結構時間很短，但是高壓力下也會出現死鎖的情況。
作者微信:
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-2143277/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql觀測METADATA LOCK(MDL)鎖MySql
- MySQL：理解MDL LockMySql
- MySQL:MDL LOCK的“穿越行為”MySql
- innodb_lock_monitor解決mysql死鎖MySql
- MYSQL METADATA LOCK(MDL LOCK)學習(1) 理論知識和加鎖型別測試MySql型別
- MySQL：一個死鎖分析 (未分析出來的死鎖)MySql
- MySQL:Innodb 讓MDL LOCK和ROW LOCK 記錄到errlogMySql
- MySQL：RR分析死鎖一列MySql
- MYSQL INNODB replace into 死鎖 及 next key lock 淺析MySql
- 剖析6個MySQL死鎖案例的原因以及死鎖預防策略MySql
- MySQL:Innodb 一個死鎖案例MySql
- MySQL:RR模式下死鎖一列MySql模式
- Oracle中的死鎖Dead Lock（一）Oracle
- Oracle中的死鎖Dead Lock（二）Oracle
- 一個MySQL死鎖問題的反思MySql
- [CareerCup] 16.4 A Lock Without Deadlocks 無死鎖的鎖
- 面試：什麼是死鎖，如何避免或解決死鎖；MySQL中的死鎖現象，MySQL死鎖如何解決面試MySql
- 一個MySQL死鎖問題的復現MySql
- MySQL 死鎖和鎖等待MySql
- MySQL 由於MDL讀鎖select被阻塞MySql
- 【Mysql】metadata lock鎖MySql
- MYSQL 鎖:metadata lockMySql
- MySQL 的next-lock 鎖MySql
- 一個最不可思議的 MySQL 死鎖分析MySql
- mysql死鎖deadlock相關幾個系統變數innodb_lock_wait_timeoutMySql變數AI
- MySQL死鎖系列-線上死鎖問題排查思路MySql
- mysql innodb lock鎖之record lock之一MySql
- MySQL 死鎖解決MySql
- MySQL死鎖問題MySql
- 【MySQL】漫談死鎖MySql
- MySQL解決死鎖MySql
- mysql表鎖與lock tablesMySql
- mysql行鎖和死鎖檢測MySql
- Mysql 兩階段鎖和死鎖MySql
- MySQL 共享鎖 (lock in share mode)，排他鎖 (for update)MySql
- 【Mysql】一個最不可思議的MySQL死鎖分析--何登成MySql
- java安全編碼指南之:死鎖dead lockJava
- MySQL 死鎖問題分析MySql