MySQL 8.0 新特性之死鎖檢測控制

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


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

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


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

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

mysql> DELETE FROM t WHERE i = 1;


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

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

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



同時，在客戶端 B 返回以下錯誤信息：

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



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

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

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

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

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


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

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

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)


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

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


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

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

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

官方文檔：MySQL 8.0 Reference Manual