前言

最近老顧經(jīng)常碰到同事說，MySQL又死鎖了導(dǎo)致業(yè)務(wù)報(bào)錯(cuò)。今天我們就來聊聊死鎖以及怎么解決

鎖類型

mysql鎖級(jí)別：頁級(jí)、表級(jí)、行級(jí)

表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。

行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。

頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

死鎖產(chǎn)生原因和示例

1、產(chǎn)生原因：

死鎖是指兩個(gè)或兩個(gè)以上的進(jìn)程在執(zhí)行過程中,因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象,若無外力作用，它們都將無法推進(jìn)下去.此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖，這些永遠(yuǎn)在互相等待的進(jìn)程稱為死鎖進(jìn)程。表級(jí)鎖不會(huì)產(chǎn)生死鎖.所以解決死鎖主要還是針對(duì)于最常用的InnoDB。

死鎖的關(guān)鍵在于：兩個(gè)(或以上)的Session加鎖的順序不一致。

那么對(duì)應(yīng)的解決死鎖問題的關(guān)鍵就是：讓不同的session加鎖有次序

2、產(chǎn)生示例：

案例一

需求：將投資的錢拆成幾份隨機(jī)分配給借款人。

起初業(yè)務(wù)程序思路是這樣的：

投資人投資后，將金額隨機(jī)分為幾份，然后隨機(jī)從借款人表里面選幾個(gè)，然后通過一條條select for update 去更新借款人表里面的余額等。

例如兩個(gè)用戶同時(shí)投資，A用戶金額隨機(jī)分為2份，分給借款人1，2

B用戶金額隨機(jī)分為2份，分給借款人2，1

由于加鎖的順序不一樣，死鎖當(dāng)然很快就出現(xiàn)了。

對(duì)于這個(gè)問題的改進(jìn)很簡(jiǎn)單，直接把所有分配到的借款人直接一次鎖住就行了。

Select * from xxx where id in (xx,xx,xx) for update

在in里面的列表值mysql是會(huì)自動(dòng)從小到大排序，加鎖也是一條條從小到大加的鎖

第一個(gè)會(huì)話：

注意：關(guān)閉掉自動(dòng)提交set autocommit=0;

mysql> select * from goods where goods_id in (2,3) for update;
+----+--------+------+---------------------+
| good_id | goods_name | price             |
+----+--------+------+---------------------+
|  2 | bbbb     | 1.00 |
|  3 | vvv     | 3.00 |
+----+--------+------+---------------------+

第二個(gè)會(huì)話:

select * from goods where goods_id in (3,4,5) for update;

鎖等待中……

案例二

在開發(fā)中，經(jīng)常會(huì)做這類的判斷需求：根據(jù)字段值查詢（有索引），如果不存在，則插入；否則更新。

以id為主鍵為例，目前還沒有id=22的行

注意：關(guān)閉掉自動(dòng)提交set autocommit=0;

第一個(gè)會(huì)話:

select * from goods where goods_id=22 for update;

第二個(gè)會(huì)話:

select * from goods where goods_id=23  for update;

再在第一個(gè)會(huì)話:

insert into goods values(22,'ac',11.5);

鎖等待中……

再到第二個(gè)會(huì)話:

insert into goods values(23,'bc',23.0);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

當(dāng)對(duì)存在的行進(jìn)行鎖的時(shí)候(主鍵)，mysql就只有行鎖。

當(dāng)對(duì)未存在的行進(jìn)行鎖的時(shí)候(即使條件為主鍵)，mysql是會(huì)鎖住一段范圍

鎖住的范圍為：

(無窮小或小于表中鎖住id的最大值，無窮大或大于表中鎖住id的最小值)

如一：如果表中目前有已有的id為（11 ， 12）

那么就鎖住（12，無窮大）

如二：如果表中目前已有的id為（11 ， 30）

那么就鎖住（11，30）

對(duì)于這種死鎖的解決辦法是：

insert into goods(xx,xx) on duplicate key update `xx`='XX';

用mysql特有的語法來解決此問題。因?yàn)閕nsert語句對(duì)于主鍵來說，插入的行不管有沒有存在，都會(huì)只有行鎖

死鎖檢查處理

正常情況下，死鎖發(fā)生時(shí)，權(quán)重最小的連接將被kill并回滾。但是為了找出語句來優(yōu)化，啟用可啟用死鎖將死鎖信息記錄下來。

#step 1：窗口一
mysql> start transaction;
mysql> update aa set name='aaa' where id = 1;
 
#step 2：窗口二
mysql> start transaction;
mysql> update bb set name='bbb' where id = 1;
 
#step 3：窗口一
mysql> update bb set name='bbb';

查看方式

#step 4：窗口三
#是否自動(dòng)提交
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
 

查看當(dāng)前鏈接

#查看當(dāng)前連接
mysql> show processlist;
mysql> show full processlist;
mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  4 | root | localhost | test | Sleep   |  244 |       | NULL             |
|  5 | root | localhost | test | Sleep   |  111 |       | NULL             |
|  6 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
 

• id列:一個(gè)標(biāo)識(shí)，你要kill 一個(gè)語句的時(shí)候很有用。
• user列: 顯示當(dāng)前用戶，如果不是root，這個(gè)命令就只顯示你權(quán)限范圍內(nèi)的sql語句。
• host列:顯示這個(gè)語句是從哪個(gè)ip 的哪個(gè)端口上發(fā)出的。可用來追蹤出問題語句的用戶。
• db列:顯示這個(gè)進(jìn)程目前連接的是哪個(gè)數(shù)據(jù)庫。
• command列:顯示當(dāng)前連接的執(zhí)行的命令，一般就是休眠（sleep），查詢（query），連接（connect）
• time列:此這個(gè)狀態(tài)持續(xù)的時(shí)間，單位是秒。

這個(gè)命令中最關(guān)鍵的就是state列，mysql列出的狀態(tài)主要有以下幾種：

Checking table

正在檢查數(shù)據(jù)表（這是自動(dòng)的）。

Closing tables

正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中。

Connect Out

復(fù)制從服務(wù)器正在連接主服務(wù)器。

Copying to tmp table on disk

由于臨時(shí)結(jié)果集大于tmp_table_size，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存。

Creating tmp table

正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。

deleting from main table

服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。

deleting from reference tables

服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。

Flushing tables

正在執(zhí)行FLUSH TABLES，等待其他線程關(guān)閉數(shù)據(jù)表。

Killed

發(fā)送了一個(gè)kill請(qǐng)求給某線程，那么這個(gè)線程將會(huì)檢查kill標(biāo)志位，同時(shí)會(huì)放棄下一個(gè)kill請(qǐng)求。MySQL會(huì)在每次的主循環(huán)中檢查kill標(biāo)志位，不過有些情況下該線程可能會(huì)過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效。

Locked

被其他查詢鎖住了。

Sending data

正在處理SELECT查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端。

Sorting for group

正在為GROUP BY做排序。

Sorting for order

正在為ORDER BY做排序。

Opening tables

這個(gè)過程應(yīng)該會(huì)很快，除非受到其他因素的干擾。例如，在執(zhí)ALTER TABLE或LOCK TABLE語句行完以前，數(shù)據(jù)表無法被其他線程打開。正嘗試打開一個(gè)表。

Removing duplicates

正在執(zhí)行一個(gè)SELECT DISTINCT方式的查詢，但是MySQL無法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄。因此，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端。

Reopen table

獲得了對(duì)一個(gè)表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表。

Repair by sorting

修復(fù)指令正在排序以創(chuàng)建索引。

Repair with keycache

修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引。它會(huì)比Repair by sorting慢些。

Searching rows for update

正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了。

Sleeping

正在等待客戶端發(fā)送新請(qǐng)求.

System lock

正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒有運(yùn)行多個(gè)mysqld服務(wù)器同時(shí)請(qǐng)求同一個(gè)表，那么可以通過增加--skip-external-locking參數(shù)來禁止外部系統(tǒng)鎖。

Upgrading lock

INSERT DELAYED正在嘗試取得一個(gè)鎖表以插入新記錄。

Updating

正在搜索匹配的記錄，并且修改它們。

User Lock

正在等待GET_LOCK()。

Waiting for tables

該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表。以下幾種情況下會(huì)產(chǎn)生這個(gè)通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

Waiting for handler insert

INSERT DELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求。

查看當(dāng)前正在被鎖的事務(wù)

 
#查看當(dāng)前正在被鎖的事務(wù)（鎖請(qǐng)求超時(shí)后則查不到）
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index      | lock_space | lock_page | lock_rec | lock_data      |
+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
| 130718495:65:3:4 | 130718495   | X         | RECORD    | `test`.`bb` | GEN_CLUST_INDEX |         65 |         3 |        4 | 0x000000000300 |
| 130718496:65:3:4 | 130718496   | X         | RECORD    | `test`.`bb` | GEN_CLUST_INDEX |         65 |         3 |        4 | 0x000000000300 |
+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+
 

查看當(dāng)前等待鎖的事務(wù)

#查看當(dāng)前等待鎖的事務(wù)（鎖請(qǐng)求超時(shí)后則查不到）
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 130718499         | 130718499:65:3:4  | 130718500       | 130718500:65:3:4 |
+-------------------+-------------------+-----------------+------------------+
 

查看當(dāng)前未提交的事務(wù)

 
#查看當(dāng)前未提交的事務(wù)（如果死鎖等待超時(shí),事務(wù)可能還沒有關(guān)閉）
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

 

主要看箭頭指向的這幾個(gè)字段，如果有阻塞數(shù)據(jù)（不為0的就是阻塞的）,找到后在根據(jù)下圖這個(gè)字段：try_mysql_thread_id 作為這條數(shù)據(jù)的主鍵id執(zhí)行這個(gè)sql進(jìn)行刪除： kill id ;(殺死對(duì)應(yīng)id的進(jìn)程).假設(shè)這里try_mysql_thread_id=14的這條數(shù)據(jù)是鎖了。我們執(zhí)行 kill 14刪除就不再鎖表了。

查看正在被訪問的表

#查看正在被訪問的表
mysql> show OPEN TABLES where In_use > 0;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | bb    |      1 |           0 |
+----------+-------+--------+-------------+

死鎖異常

#step 3：窗口一 （若第三步中鎖請(qǐng)求太久，則出現(xiàn)鎖超時(shí)而終止執(zhí)行）
mysql> update bb set name='bbb';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
 
#"窗口一" 鎖請(qǐng)求超時(shí)前，執(zhí)行第五步，使死鎖產(chǎn)生，則該連接 "窗口二" 執(zhí)行終止，"窗口一" 順利執(zhí)行
#step 5：窗口二
mysql> update aa set name='aa';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看最近一個(gè)死鎖情況

mysql> SHOW ENGINE INNODB STATUS;

 

相關(guān)參數(shù)配置

死鎖日志

#死鎖記錄只記錄最近一個(gè)死鎖信息，若要將每個(gè)死鎖信息都保存到錯(cuò)誤日志，啟用以下參數(shù)：
mysql> show variables like 'innodb_print_all_deadlocks';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
 

鎖等待超時(shí)

 
#上面 【step 3：窗口一】若一直請(qǐng)求不到資源，默認(rèn)50秒則出現(xiàn)鎖等待超時(shí)。
mysql> show variables like 'innodb_lock_wait_timeout'; 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
 
#設(shè)置全局變量 鎖等待超時(shí)為60秒（新的連接生效）
#mysql> set session innodb_lock_wait_timeout=50; 
mysql> set global innodb_lock_wait_timeout=60; 
 

事務(wù)回滾

 
#上面測(cè)試中，當(dāng)事務(wù)中的某個(gè)語句超時(shí)只回滾該語句，事務(wù)的完整性屬于被破壞了。為了回滾這個(gè)事務(wù)，啟用以下參數(shù)：
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF   |
+----------------------------+-------+

最終參數(shù)設(shè)置如下：(重啟服務(wù)重新連接測(cè)試)

[mysqld]
log-error =/var/log/mysqld3306.log
innodb_lock_wait_timeout=60     #鎖請(qǐng)求超時(shí)時(shí)間(秒)
innodb_rollback_on_timeout = 1  #事務(wù)中某個(gè)語句鎖請(qǐng)求超時(shí)將回滾真?zhèn)€事務(wù)
innodb_print_all_deadlocks = 1  #死鎖都保存到錯(cuò)誤日志

Command='Sleep'

表示連接休眠，如果太多，可以手動(dòng)刪除

#若手動(dòng)刪除堵塞會(huì)話，刪除 Command='Sleep' 、無State、無Info、trx_weight 權(quán)重最小的。
show processlist;
SELECT trx_mysql_thread_id,trx_state,trx_started,trx_weight FROM INFORMATION_SCHEMA.INNODB_TRX;

總結(jié)

上面是老顧總結(jié)的mysql死鎖相關(guān)的內(nèi)容，尤其是死鎖檢查處理，是比較實(shí)用的，小伙伴們可以參考。

---

推薦閱讀

解決Api統(tǒng)一格式返回，遺留地返回String一個(gè)問題

SpringMVC全局異常方案、源碼分析，以及常見的入坑

Spring多種啟動(dòng)初始化方案，看這篇就夠了

來說說ThreadLocal內(nèi)存溢出問題

阿里面試題：強(qiáng)、軟、弱、虛引用的特點(diǎn)及應(yīng)用場(chǎng)景

企業(yè)常用的并發(fā)編程Queue的源碼分析

了解JAVA中的SPI機(jī)制，以及數(shù)據(jù)庫驅(qū)動(dòng)插件，這一篇就夠了

企業(yè)實(shí)戰(zhàn)之阿里druid統(tǒng)一監(jiān)控方案，你了解嗎？

千人千面精準(zhǔn)推薦之大白話講解協(xié)同算法（一）,看這篇就夠了

企業(yè)實(shí)戰(zhàn)之分布式鎖方案一步步的演變

你了解滑動(dòng)時(shí)間窗口嗎？Sentinel核心源碼剖析

Sentinel全局Feign默認(rèn)熔斷降級(jí)策略的思考

你所不知道的頭部參數(shù)傳遞的坑，來吧！抓緊出坑

5分鐘讓你理解K8S必備架構(gòu)概念，以及網(wǎng)絡(luò)模型（一）

5分鐘讓你理解K8S必備架構(gòu)概念，以及網(wǎng)絡(luò)模型（二）

5分鐘讓你理解K8S必備架構(gòu)概念，以及網(wǎng)絡(luò)模型（三）

大廠如何基于binlog解決多機(jī)房同步mysql數(shù)據(jù)（一）？

大廠如何基于binlog解決多機(jī)房同步mysql數(shù)據(jù)（二）？

基于binlog的canal組件有哪些使用場(chǎng)景（三）？

基于binlog日志之canal企業(yè)應(yīng)用及高可用原理（四）？

可用于大型應(yīng)用的微服務(wù)生態(tài)灰度發(fā)布如何實(shí)現(xiàn)？

一線大廠級(jí)別公共redis集群監(jiān)控，細(xì)化到每個(gè)項(xiàng)目實(shí)例

Sharding-jdbc的實(shí)戰(zhàn)入門之水平分表（一）

Sharding-Jdbc之水平分庫和讀寫分離（二）