本文主要講解MySQL中出現(xiàn)死鎖的應(yīng)用案例，以及相關(guān)的業(yè)務(wù)場(chǎng)景，不會(huì)純講理論，希望對(duì)這塊感興趣的朋友可以有所幫助。

什么是死鎖

多個(gè)線程在訪問某些資源的時(shí)候，需要等待對(duì)方釋放彼此所需資源，而進(jìn)入了等待互斥的狀態(tài)。

通俗一些來說，A線程持有B鎖，然后想要訪問A鎖，此時(shí)B線程持有A鎖，想要訪問B鎖，這種情況下就容易出現(xiàn)死鎖。

MySQL中鎖的類型有哪些？

下邊我們以用戶消息表案例來進(jìn)行說明：

CREATE TABLE `t_user_message` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '發(fā)信方id',
  `object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id',
  `relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '關(guān)聯(lián)id',
  `is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已讀（0未讀，1已讀）',
  `sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息條數(shù)',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '狀態(tài)（0無效 1有效）',
  `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息內(nèi)容',
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '類型（0文本，1語音，2圖片，3視頻，4表情，5分享鏈接）',
  `ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '擴(kuò)展字段',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '發(fā)信方id索引',
  KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引',
  KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '關(guān)聯(lián)id索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用戶消息表';

按照鎖的粒度來區(qū)分，可以分為以下兩種：

行鎖

只會(huì)鎖住特定的行記錄,例如下邊這條sql：

select * from t_user_message where user_id=1001 for update;

表鎖

會(huì)把整個(gè)表的數(shù)據(jù)給鎖住，性能較差,例如下邊這條sql：

select * from t_user_message  for update;

排它鎖和共享鎖的區(qū)別

共享鎖

在多個(gè)事務(wù)里面都可以讀取共享鎖所鎖住的內(nèi)容。

排它鎖

只能在一個(gè)事務(wù)里對(duì)同樣的數(shù)據(jù)進(jìn)行加鎖，假設(shè)A事務(wù)對(duì)某行數(shù)據(jù)加入了排它鎖之后，其他事務(wù)就無法再對(duì)該行記錄加入排它鎖。

關(guān)于排它鎖和共享鎖的使用

看到這里，你可能對(duì)共享鎖和排它鎖并不是理解得很徹底，那么先別著急，我們先從實(shí)戰(zhàn)來加深下你對(duì)它的理解。

排它鎖

在Innodb存儲(chǔ)引擎中，常見的update，insert，delete這些sql都會(huì)默認(rèn)加入上排他鎖，而我們的select語句如果沒有加入特殊關(guān)鍵字（下邊會(huì)講是什么樣的特殊關(guān)鍵字） ，是不會(huì)加入排他鎖的。

如果select語句希望加入排它鎖，那么可以嘗試以下方式：

使用 for update 關(guān)鍵字

select * from t_user_message  for update;

共享鎖

在正常的select語句中，是不會(huì)有加鎖的，例如下邊這條sql：

select * from t_user_message;

這條sql在innodb中，默認(rèn)是不會(huì)鎖表，也不會(huì)鎖行記錄。如果你希望加上一把共享鎖，那么可以嘗試以下的這種寫法：

使用 lock in share mode 關(guān)鍵字

select * from t_user_message lock in share mode;

lock in share mode 和 for update使用起來有什么區(qū)別？

來看看這個(gè)案例，我們準(zhǔn)備了兩個(gè)MySQL的會(huì)話窗口。

lock in share mode 測(cè)試

先來看會(huì)話A：會(huì)話A中，關(guān)閉了自動(dòng)提交功能，然后執(zhí)行這個(gè)lock in share mode的鎖，此時(shí)它使用了共享鎖鎖住了全表的內(nèi)容。

圖片

再來看會(huì)話B：會(huì)話B中也是相同的，關(guān)閉自動(dòng)提交后，執(zhí)行l(wèi)ock in share mode的共享鎖，發(fā)現(xiàn)依然可以正常查詢，沒有堵塞行為。

圖片

這時(shí)候我們將會(huì)話B的當(dāng)前事務(wù)先提交，然后在會(huì)話B中繼續(xù)執(zhí)行一條update語句（非事務(wù)狀態(tài)下） ，要知道update是默認(rèn)帶了拍它鎖的，此時(shí)因?yàn)槲覀兊臅?huì)話A沒有commit，所以會(huì)話B的這條update操作會(huì)進(jìn)入堵塞的狀態(tài)，如下圖：

圖片

只有當(dāng)會(huì)話A的事務(wù)執(zhí)行完畢了，將lock in share mode的鎖給釋放掉，會(huì)話B才會(huì)繼續(xù)執(zhí)行。

for update測(cè)試

下邊讓我們來看看 for update 加鎖的影響，會(huì)話A關(guān)閉了自動(dòng)提交，然后執(zhí)行了一條for update的sql，但是沒有commit；此時(shí)我們的會(huì)話B也開始了同樣的步驟，但是卻卡住了。

圖片

假如事務(wù)A一直都不提交的話，那么事務(wù)B最終會(huì)報(bào)出以下異常：

圖片

[查詢2中發(fā)生錯(cuò)誤] Lock wait timeout exceeded; try restarting transaction

再來看看for update鎖住的數(shù)據(jù)，對(duì)于其他會(huì)話的寫操作有何影響。

如下圖所示，我們的會(huì)話A依舊沒有commit，但是此時(shí)會(huì)話B中嘗試執(zhí)行一次update操作，由于update默認(rèn)帶了排他鎖，這條sql會(huì)鎖表，所以和會(huì)話A中的for update鎖出現(xiàn)了沖突，導(dǎo)致會(huì)話B一直處于堵塞狀態(tài)。

圖片

小總結(jié)

通過上述的幾個(gè)測(cè)試，大家應(yīng)該也有深刻的體會(huì)了，那么我們就來進(jìn)行下總結(jié)，加深下印象。

看到這里，你應(yīng)該對(duì)lock in share mode 和 for update 有一定了解了吧，但是這兩種鎖，光了解理論，其實(shí)還是不夠的，需要有實(shí)戰(zhàn)才能讓你對(duì)它理解更加深刻，來看下邊的案例。

lock in share mode使用不當(dāng)，導(dǎo)致死鎖

來看下邊的這個(gè)業(yè)務(wù)場(chǎng)景：

假設(shè)我們有一個(gè)賬戶表，表結(jié)構(gòu)如下：

CREATE TABLE `t_account` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `coin` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;

然后在業(yè)務(wù)操作上，我們的賬戶扣款和增款邏輯上的設(shè)計(jì)如下：

//開啟一個(gè)事務(wù)操作
set autocommit=0;
begin;
//如果賬戶存在，才進(jìn)行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111 lock in share mode;


//這里我們假設(shè)賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100 where user_id=111;


//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');


commit;

這里為了保證將賬戶流水記錄和打款兩個(gè)操作保證一致性，得加入一個(gè)本地事務(wù)去修飾。但是這段代碼中使用了一個(gè)lock in share mode的關(guān)鍵字，這個(gè)關(guān)鍵字是為了避免在并發(fā)的情況下，對(duì)賬戶記錄進(jìn)行讀的過程中，有其他地方對(duì)賬戶的coin值進(jìn)行寫的修改。

之所以可能會(huì)有其他地方對(duì)coin值進(jìn)行額外的寫操作，主要原因是因?yàn)橄到y(tǒng)業(yè)務(wù)中的老舊代碼存在，重復(fù)造輪子，本來A服務(wù)中只有一處地方對(duì)賬戶進(jìn)行修改操作，結(jié)果在B服務(wù)里，也有一段類似的代碼修改，直接操作了數(shù)據(jù)庫表，但是由于不好去調(diào)整那個(gè)服務(wù)的代碼，所以暫時(shí)只能用 lock in share mode 操作去加鎖。相比于for update鎖來說，使用lock in share mode加鎖，對(duì)于讀的影響不大，所以早期設(shè)計(jì)的時(shí)候，沒有考慮那么多，就直接用了它上線。并且上線之后并發(fā)度不高，暫時(shí)就沒有發(fā)現(xiàn)什么問題。

看到這里，你可能感覺似乎這種設(shè)計(jì)沒有什么問題，那么我們來看看下邊的這個(gè)場(chǎng)景：

隨著并發(fā)度的增加，我們將修改余額的這個(gè)操作，在A服務(wù)里面封裝成為了一個(gè)方法，并且供各個(gè)地方進(jìn)行調(diào)用。但是有一天，出現(xiàn)了這么一個(gè)業(yè)務(wù)場(chǎng)景：

在RocketMQ的消費(fèi)方，會(huì)對(duì)用戶的賬戶進(jìn)行打款操作。在這個(gè)消費(fèi)方的代碼中，同一個(gè)userId的消息會(huì)有許多條，而且是同一時(shí)刻的大量并發(fā)消費(fèi)，這就意味著，同一時(shí)刻會(huì)有大量的請(qǐng)求調(diào)用這個(gè)打款的操作，而且是并發(fā)，同一個(gè)userId。那么這種情況下，我們的 lock in share mode會(huì)發(fā)生什么樣的情況呢 -- 死鎖。

來看下圖：

圖片

由于我們的線程A持有了鎖，線程B也持有了鎖，但是它們接下來的update操作，都是得等對(duì)方將共享鎖釋放后才可以繼續(xù)執(zhí)行，所以就發(fā)生了死鎖的場(chǎng)景。

圖片

如何解決上述的lock in share mode死鎖

那么我們?nèi)绾螀s避免上邊的場(chǎng)景發(fā)生呢，這里我給出以下兩種思路。

不用鎖，提升事務(wù)隔離級(jí)別為讀已提交

//開啟一個(gè)事務(wù)操作
set autocommit=0;


//如果賬戶存在，才進(jìn)行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111;


//這里我們假設(shè)賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100,version=version+1 where user_id=111;


//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');


commit;

去掉使用lock in share mode，使用樂觀鎖。

例如加入一個(gè)version字段，那么我們?cè)趫?zhí)行賬戶扣款的時(shí)候，加入version的判斷。例如：

//開啟一個(gè)事務(wù)操作
set autocommit=0;


//如果賬戶存在，才進(jìn)行update，如果賬戶不存在，就得先insert
select * from t_account where user_id=111 and version=#{version};


//這里我們假設(shè)賬戶是存在的，那么就直接選擇打款入賬
update t_account set coin=coin+100,version=version+1 where user_id=111 and version=#{version};


//記錄到賬戶流水表中
INSERT INTO `transaction_log` (`id`, `business`, `foreign_key`)
VALUES (1, X'6F726465722D62697A', X'3234303938413535333031324444443044444137363036333744434233373834333643313138423441414332323236454644463430303034');


commit;

這里要注意，當(dāng)同時(shí)兩個(gè)會(huì)話針對(duì)同一行數(shù)據(jù)執(zhí)行上述更新操作的時(shí)候，可能會(huì)導(dǎo)致同一行的記錄被鎖，所以我們?cè)谶M(jìn)行update的時(shí)候，可以用一個(gè)version字段去管理。但是這種設(shè)計(jì)，可能會(huì)導(dǎo)致一次更新失敗，需要進(jìn)行重試，因此并發(fā)量高的情況下，容易對(duì)MySQL造成較大的壓力。

引入分布式鎖

直接在業(yè)務(wù)層引入一把分布式鎖，這種思路比較暴力，但是確實(shí)有效。

其實(shí)只要我們的select類型的sql中進(jìn)行顯示加鎖，就有可能會(huì)有死鎖情況發(fā)生，所以建議大家使用的時(shí)候謹(jǐn)慎。

行鎖的幾種類型

• Record Lock（記錄鎖）：?jiǎn)蝹€(gè)行記錄上的鎖。這個(gè)也是我們?nèi)粘ＵJ(rèn)為的行鎖。
• Gap Lock（間隙鎖）：間隙鎖，鎖定一個(gè)范圍，但不包括記錄本身（只不過它的鎖粒度比記錄鎖的鎖整行更大一些，他是鎖住了某個(gè)范圍內(nèi)的多個(gè)行，包括根本不存在的數(shù)據(jù)）。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況。該鎖只會(huì)在隔離級(jí)別是RR或者以上的級(jí)別內(nèi)存在。間隙鎖的目的是為了讓其他事務(wù)無法在間隙中新增數(shù)據(jù)。
• Next-Key Lock（臨鍵鎖）：它是記錄鎖和間隙鎖的結(jié)合，鎖定一個(gè)范圍，并且鎖定記錄本身。對(duì)于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。next-key鎖是InnoDB默認(rèn)的鎖，該鎖也只會(huì)在隔離級(jí)別是RR或者以上的級(jí)別內(nèi)存在。

行鎖的事故案例

消息數(shù)據(jù)更新設(shè)計(jì)不當(dāng)，導(dǎo)致出現(xiàn)Record Lock死鎖

這里我們需要先了解下消息記錄表的結(jié)構(gòu)；

CREATE TABLE `t_user_message` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL DEFAULT '0' COMMENT '發(fā)信方id',
  `object_id` int unsigned NOT NULL DEFAULT '0' COMMENT '收信方id',
  `relation_id` int unsigned NOT NULL DEFAULT '0' COMMENT '關(guān)聯(lián)id',
  `is_read` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否已讀（0未讀，1已讀）',
  `sid` int unsigned NOT NULL DEFAULT '0' COMMENT '消息條數(shù)',
  `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '狀態(tài)（0未審核 1審核失敗 2審核通過）',
  `content` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '消息內(nèi)容',
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '類型（0文本，1語音，2圖片，3視頻，4表情，5分享鏈接）',
  `ext_json` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '擴(kuò)展字段',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`) USING BTREE COMMENT '發(fā)信方id索引',
  KEY `idx_object_id` (`object_id`) USING BTREE COMMENT '收信方id索引',
  KEY `idx_relation_id` (`relation_id`) USING BTREE COMMENT '關(guān)聯(lián)id索引'
) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin COMMENT='用戶消息表';

假設(shè)我們的會(huì)話A執(zhí)行了以下事務(wù)操作：

START TRANSACTION;


//更新用戶的消息狀態(tài)，從未讀變?yōu)橐炎x
update t_user_message set is_read=1 where user_id=1003 and object_id=1004;
//...中間有些別的業(yè)務(wù)操作
update t_user_message set is_read=1 where user_id=1001 and object_id=1002;


commit;

而此時(shí)我們的會(huì)話B在執(zhí)行一個(gè)異步的消息是否合法的檢測(cè)工作，具體操作如下：

set autocommit=0;
START TRANSACTION;


//定時(shí)任務(wù)更新用戶的消息審核狀態(tài)，從未審核變?yōu)閷徍送ㄟ^
update t_user_message set status=2 where user_id=1001 and object_id=1002;
//...中間有些別的業(yè)務(wù)操作
update t_user_message set status=2 where user_id=1003 and object_id=1004;


commit;

這兩個(gè)事務(wù)如果并發(fā)執(zhí)行，并發(fā)度高的情況下，可能會(huì)出現(xiàn)死鎖情況，死鎖產(chǎn)生的步驟如下圖所示：

圖片

一般遇到這類情況，我們都會(huì)推薦在進(jìn)行更新的時(shí)候，盡可能的避免死鎖條件發(fā)生，例如調(diào)整sql的執(zhí)行順序。例如變更為如下操作：

圖片

另外，調(diào)整順序后，盡量將本地事務(wù)的顆粒度控制到最小，減少因?yàn)榧渔i堵塞帶來的性能問題。

間隙鎖堵塞案例分析

首先我們要將當(dāng)前會(huì)話的事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀：

set SESSION transaction ISOLATION LEVEL REPEATABLE READ;

如果你想確認(rèn)當(dāng)前的會(huì)話的事務(wù)隔離級(jí)別，那么可以使用以下命令去查詢：

SELECT @@transaction_isolation; （mysql8.0語法）
 SELECT @@tx_isolation; （mysql5.7語法）

還是針對(duì)我們的消息表t_user_message，在某些高并發(fā)場(chǎng)景下，如果使用可重復(fù)讀的話，尤其是事務(wù)場(chǎng)景中，出現(xiàn)死鎖的概率會(huì)加大。例如下邊這個(gè)場(chǎng)景：

事務(wù)1中，對(duì)消息表的可讀狀態(tài)進(jìn)行修改，修改的是記錄表中的前3條數(shù)據(jù)，由于是可重復(fù)讀，以及非唯一索引user_id和object_id所以這里會(huì)鎖住的是(0,100011]這個(gè)區(qū)間的id記錄，也就是說只要我們更新的行是超過了100011 id的都沒問題。

圖片

但是假設(shè)此時(shí)有個(gè)插入請(qǐng)求，打算往100009之前寫入一條記錄的話，就會(huì)出現(xiàn)間隙鎖堵塞的問題，例如下圖所示：

圖片

產(chǎn)生間隙鎖的原因

1.使用了update，delete，selecct... for update相關(guān)操作

2.使用了可重復(fù)讀的隔離級(jí)別

3.在執(zhí)行update/delete/select ... for update操作之后，在對(duì)應(yīng)的間隙中插入了新的數(shù)據(jù)（注意是insert了新的數(shù)據(jù)才會(huì)有間隙鎖問題產(chǎn)生)。

MySQL中的死鎖檢測(cè)

在mysql5.7、mysql5.8等5系版本中

查看死鎖代碼是

select * from information_schema.innodb_locks;

查看等待鎖的代碼

select * from information_schema.innodb_lock_waits

但是要注意，在mysql 8.0中查看死鎖代碼變了，如果繼續(xù)用5.7的代碼會(huì)提示報(bào)錯(cuò)

Unknown table ‘INNODB_LOCKS’ in information_schema

所以在8.0使用以下代碼

查看死鎖

select * from performance_schema.data_locks;

查看死鎖等待時(shí)間

select * from performance_schema.data_lock_waits;