快捷導(dǎo)航

Mysql并發(fā)常見的死鎖及解決方法

更新時(shí)間：2023年12月11日 15:26:31 作者：cwh_hot

死鎖是在并發(fā)執(zhí)行的過程中,兩個(gè)或多個(gè)事務(wù)相互等待對方釋放資源的情況,本文主要介紹了Mysql并發(fā)常見的死鎖及解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下

死鎖是指兩個(gè)或兩個(gè)以上的事務(wù)在執(zhí)行過程中，因爭奪資源而造成的一種互相等待的現(xiàn)象。就是所謂的鎖資源請求產(chǎn)生了回路現(xiàn)象，即死循環(huán)，此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖。常見的報(bào)錯(cuò)信息為“Deadlock found when trying to get lock...”。

上圖中，很明顯是右側(cè)的四輛汽車造成了死鎖。

死鎖發(fā)生以后，只有部分或完全回滾其中一個(gè)事務(wù)，才能打破死鎖。多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。下面我們通過一個(gè)實(shí)例來了解死鎖是如何產(chǎn)生的。

例 1

為了方便讀者閱讀，操作之前我們先查詢 tb_student 表的數(shù)據(jù)和表結(jié)構(gòu)。

mysql> SELECT * FROM tb_student;
+----+------+------+------+------+
| id | name | age  | sex  | num  |
+----+------+------+------+------+
|  1 | 張三 |   31 | 男   |    4 |
|  2 | 李四 |   28 | 男   |    4 |
|  3 | 王五 |   13 | 女   |    4 |
|  4 | 張四 |   13 | 女   |    4 |
|  5 | 王四 |   15 | 男   |    4 |
|  6 | 趙六 |   12 | 女   |    4 |
+----+------+------+------+------+
6 rows in set (0.01 sec)

mysql> DESC tb_student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(25) | NO   |     | NULL    |                |
| age   | int(11)     | YES  | MUL | NULL    |                |
| sex   | char(1)     | YES  |     | NULL    |                |
| num   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

以下操作需要打開兩個(gè)會話窗口，即下面所提到的 A窗口和 B窗口。

在 A窗口中執(zhí)行以下命令：

mysql> BEGIN;
mysql> UPDATE tb_student SET num=5 WHERE age=13;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

緊接著在 B窗口中執(zhí)行以下命令。由于 age 是索引字段，與 A窗口中更新的是不同行的數(shù)據(jù)，所以這時(shí)不會出現(xiàn)鎖等待現(xiàn)象。

mysql> BEGIN;
mysql> UPDATE tb_student SET num=8 WHERE age=15;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

然后在 A窗口中，執(zhí)行以下命令，這時(shí)就會出現(xiàn)鎖等待現(xiàn)象了。

mysql> UPDATE tb_student SET num=10 WHERE age=15;

最后在 B窗口中，執(zhí)行以下命令，這時(shí)會出現(xiàn)相互等待資源的現(xiàn)象，也就是死鎖現(xiàn)象。

mysql> UPDATE tb_student SET num=12 WHERE age=13;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

我們可以通過 SHOW ENGINE INNODB STATUS 命令查看死鎖的信息，運(yùn)行結(jié)果如下（這里只展示了部分信息）：

LATEST DETECTED DEADLOCK
------------------------
2020-08-24 16:22:23 0x3944
*** (1) TRANSACTION:
TRANSACTION 22656, ACTIVE 108 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 33, OS thread handle 8808, query id 1689 localhost ::1 root updating
UPDATE tb_student SET num=10 WHERE age=15
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 197 page no 8 n bits 80 index index_age of table `test`.`tb_student` trx id 22656 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000f; asc     ;;
1: len 4; hex 80000005; asc     ;;
......

通過以上日志，我們就能確定造成死鎖的事務(wù)和 SQL 語句。

死鎖檢測

InnoDB 的并發(fā)寫操作會觸發(fā)死鎖，同時(shí) InnoDB 也提供了死鎖檢測機(jī)制。通過設(shè)置 innodb_deadlock_detect 參數(shù)的值來控制是否打開死鎖檢測。

innodb_deadlock_detect = ON ：默認(rèn)值，打開死鎖檢測。數(shù)據(jù)庫發(fā)生死鎖時(shí)，系統(tǒng)會自動(dòng)回滾其中的某一個(gè)事務(wù)，讓其它事務(wù)可以繼續(xù)執(zhí)行。
innodb_deadlock_detect = OFF：關(guān)閉死鎖檢測。發(fā)生死鎖時(shí)，系統(tǒng)會用鎖等待來處理。

鎖等待是指在事務(wù)過程中產(chǎn)生的鎖，其它事務(wù)需要等待上一個(gè)事務(wù)釋放鎖，才能占用該資源。如果該事務(wù)一直不釋放，就需要持續(xù)等待下去，直到超過了鎖等待時(shí)間。當(dāng)超過鎖等待允許的最大時(shí)間，就會出現(xiàn)死鎖，然后當(dāng)前事務(wù)執(zhí)行失敗，自動(dòng)執(zhí)行回滾操作。

MySQL 通過 innodb_lock_wait_timeout 參數(shù)控制鎖等待的時(shí)間，單位是秒。

mysql> SHOW VARIABLES LIKE '%innodb_lock_wait%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 120   |
+--------------------------+-------+
1 row in set, 1 warning (0.02 sec)

在實(shí)際應(yīng)用中，我們要盡量防止鎖等待現(xiàn)象的發(fā)生，下面介紹幾種避免死鎖的方法：

如果不同程序會并發(fā)存取多個(gè)表，或者涉及多行記錄時(shí)，盡量約定以相同的順序訪問表，這樣可以大大降低死鎖的發(fā)生。
業(yè)務(wù)中要及時(shí)提交或者回滾事務(wù)，可減少死鎖產(chǎn)生的概率。
在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率。
對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖粒度，通過表鎖定來減少死鎖產(chǎn)生的概率（表級鎖不會產(chǎn)生死鎖）。

到此這篇關(guān)于Mysql并發(fā)常見的死鎖及解決方法的文章就介紹到這了,更多相關(guān)Mysql并發(fā)死鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: