MySQL 8.0 新特性之死鎖檢測(cè)控制

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


在 MySQL 中，如果兩個(gè)不同的事務(wù)在執(zhí)行時(shí)，互相持有了對(duì)方所需的鎖，此時(shí)由于它們都在等待某個(gè)資源，永遠(yuǎn)不會(huì)釋放自己獲得的鎖，因此就會(huì)產(chǎn)生死鎖（deadlock）。

以下是一個(gè)產(chǎn)生死鎖的示例。首先，在客戶端 A 中創(chuàng)建一個(gè)表 t，它只有一行數(shù)據(jù)。然后開始一個(gè)事務(wù)，并且通過(guò)共享查詢模式獲取該行數(shù)據(jù)上的 S 鎖。

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

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

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+


接下來(lái)，在客戶端 B 中開始另一個(gè)事務(wù)，并且嘗試刪除該行數(shù)據(jù)：

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

mysql> DELETE FROM t WHERE i = 1;


刪除操作需要獲得一個(gè)排他鎖（X）。由于客戶端 A 已經(jīng)獲得了一個(gè) S 鎖，客戶端 B 的鎖請(qǐng)求需要進(jìn)入鎖的等待隊(duì)列，因此客戶端 B 被阻塞。

最后，在客戶端 A 中也嘗試刪除該行數(shù)據(jù)：

mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.00 sec)



同時(shí)，在客戶端 B 返回以下錯(cuò)誤信息：

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction



此時(shí)產(chǎn)生了死鎖，因?yàn)榭蛻舳?A 需要獲得一個(gè) X 鎖才能刪除該行數(shù)據(jù)。然而，客戶端 A 無(wú)法獲得該鎖，因?yàn)榭蛻舳?B 已經(jīng)請(qǐng)求了一個(gè) X 鎖，并且等待客戶端 A 釋放該數(shù)據(jù)行上的 S 鎖?？蛻舳?A 上的 S 鎖也無(wú)法升級(jí)為 X 鎖，因?yàn)榭蛻舳?B 的 X 鎖請(qǐng)求優(yōu)先級(jí)更高。結(jié)果就是，InnoDB 將會(huì)在某個(gè)客戶端產(chǎn)生錯(cuò)誤，并且釋放它所獲取的鎖。

至此，另一個(gè)客戶端（A）能夠獲得請(qǐng)求的鎖，并且刪除該數(shù)據(jù)行。

通過(guò)以上示例可以看出，當(dāng)啟用了死鎖檢測(cè)時(shí)（默認(rèn)設(shè)置），InnoDB 自動(dòng)執(zhí)行事務(wù)的死鎖檢測(cè)，并且回滾一個(gè)或多個(gè)事務(wù)以解決死鎖。InnoDB 嘗試回滾更小的事務(wù)，事務(wù)的大小由它所插入、更新或者刪除的數(shù)據(jù)行數(shù)決定。

在 MySQL 8.0 中，增加了一個(gè)新的動(dòng)態(tài)變量：innodb_deadlock_detect，可以用于控制 InnoDB 是否執(zhí)行死鎖檢測(cè)。該參數(shù)的默認(rèn)值為 ON，即打開死鎖檢測(cè)。

mysql> show variables like 'innodb_deadlock_detect';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.00 sec)


對(duì)于高并發(fā)的系統(tǒng)，當(dāng)大量線程等待同一個(gè)鎖時(shí)，死鎖檢測(cè)可能會(huì)導(dǎo)致性能的下降。此時(shí)，如果禁用死鎖檢測(cè)，而改為依靠參數(shù) innodb_lock_wait_timeout 執(zhí)行發(fā)生死鎖時(shí)的事務(wù)回滾可能會(huì)更加高效。

接下來(lái)，先在客戶端 A 中關(guān)閉死鎖檢測(cè)：

mysql> set global innodb_deadlock_detect=off;
Query OK, 0 rows affected (0.04 sec)

 

重復(fù)上文中的示例，在客戶端 A 中執(zhí)行以下操作：

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;
+------+
| i    |
+------+
|    1 |
+------+


在客戶端 B 執(zhí)行刪除操作：

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

mysql> DELETE FROM t WHERE i = 1;



客戶端 B 等待 X 鎖，仍然被阻塞。再回到客戶端 A，嘗試刪除該行數(shù)據(jù)：

mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (37.87 sec)


此時(shí)客戶端 B 不會(huì)提示死鎖錯(cuò)誤，而是等待 50 s （innodb_lock_wait_timeout 設(shè)置值）后提示等待超時(shí)：

mysql> DELETE FROM t WHERE i = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


同時(shí)客戶端 A 在此之后成功刪除數(shù)據(jù)，可以看到刪除操作的時(shí)間為 37.87 s 。這個(gè)時(shí)間依取決于最后在客戶端 A 中輸入刪除語(yǔ)句的時(shí)間。

通常來(lái)說(shuō)，應(yīng)該啟用死鎖檢測(cè)，并且在應(yīng)用程序中盡量避免產(chǎn)生死鎖，同時(shí)對(duì)死鎖進(jìn)行相應(yīng)的處理，例如重新開始事務(wù)。

只有在確認(rèn)死鎖檢測(cè)影響了系統(tǒng)的性能，并且禁用死鎖檢測(cè)不會(huì)帶來(lái)負(fù)面影響時(shí)，可以嘗試關(guān)閉 innodb_deadlock_detect 選項(xiàng)。另外，如果禁用了 InnoDB 死鎖檢測(cè)，需要調(diào)整參數(shù) innodb_lock_wait_timeout 的值，以滿足實(shí)際的需求。

官方文檔：MySQL 8.0 Reference Manual