快捷導(dǎo)航

關(guān)于MySQL中“Insert into select“ 的死鎖情況分析

更新時(shí)間：2023年05月22日 08:58:38 作者：長安明月

這篇文章主要介紹了關(guān)于MySQL中“Insert into select“ 的死鎖情況分析,死鎖是指兩個(gè)或者多個(gè)事務(wù)在同一資源上的相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象,需要的朋友可以參考下

一、前言

這天 xxx 接到一個(gè)需求，需要將表 A 的數(shù)據(jù)遷移到表 B 中去做一個(gè)備份。本想通過程序先查詢出來然后批量插入。但 xxx 覺得這樣有點(diǎn)慢，需要耗費(fèi)大量的網(wǎng)絡(luò) I/O，決定采取別的方法進(jìn)行實(shí)現(xiàn)。

通過在百度的海洋里遨游，他發(fā)現(xiàn)了可以使用 insert into select 實(shí)現(xiàn)，這樣就可以避免使用網(wǎng)絡(luò) I/O，直接使用 SQL 依靠數(shù)據(jù)庫 I/O 完成，這樣簡直不要太棒了。

然后他就被開除了。

二、事故發(fā)生的經(jīng)過

由于數(shù)據(jù)庫中 order_today 數(shù)據(jù)量過大，當(dāng)時(shí)好像有 700W 了，并且每天在以 30W 的速度增加。

所以上司命令 xxx 將 order_today 內(nèi)的部分?jǐn)?shù)據(jù)遷移到 order_record 中，并將 order_today 中的數(shù)據(jù)刪除。

這樣來降低 order_today 表中的數(shù)據(jù)量。

由于考慮到會占用數(shù)據(jù)庫 I/O，為了不影響業(yè)務(wù)，計(jì)劃是 21:00 以后開始遷移，但是 xxx 在 20:00 的時(shí)候，嘗試遷移了少部分?jǐn)?shù)據(jù)(1000條)，覺得沒啥問題，就開始考慮大批量遷移。

在遷移過程中，應(yīng)急群是先反應(yīng)有小部分用戶出現(xiàn)支付失敗，隨后反應(yīng)大批用戶出現(xiàn)支付失敗的情況，以及初始化訂單失敗的情況，同時(shí)騰訊也開始報(bào)警。然后 xxx 就慌了，立即停止了遷移。

本以為停止遷移就可以恢復(fù)了，但是并沒有。后面發(fā)生的你們可以腦補(bǔ)一下。

三、事故還原

在本地建立一個(gè)精簡版的數(shù)據(jù)庫，并生成了 100w 的數(shù)據(jù)。模擬線上發(fā)生的情況。

建立訂單表結(jié)構(gòu)，語句如下：

CREATE TABLE `order_today` (
  `id` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '主鍵',
  `merchant_id` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '商戶編號',
  `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT '訂單金額',
  `pay_success_time` DATETIME NOT NULL DEFAULT '0000-01-01 00:00:00' COMMENT '支付成功時(shí)間',
  `order_status` VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '支付狀態(tài) S：支付成功、F：訂單支付失敗',
  `remark` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '備注',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商戶編號'  
) ENGINE=INNODB DEFAULT CHARSET=utf8;

創(chuàng)建訂單記錄表，語句如下：

CREATE TABLE order_record like order_today;

今日訂單表數(shù)據(jù)量：1740730。

模擬遷移，把 8 號之前的數(shù)據(jù)都遷移到 order_record 表中去。

INSERT INTO order_record 
SELECT 
  * 
FROM
  order_today 
WHERE pay_success_time < '2020-03-08 00:00:00' ;

在 navicat 中運(yùn)行遷移的 sql，同時(shí)開另一個(gè)窗口插入數(shù)據(jù)，模擬下單。

現(xiàn)象：一開始能正常插入，但是后面突然就卡住了，并且耗費(fèi)了 23s 才成功，然后才能繼續(xù)插入。這個(gè)時(shí)候已經(jīng)遷移成功了，所以能正常插入了。

四、出現(xiàn)的原因

在默認(rèn)的事務(wù)隔離級別下：insert into order_record select * from order_today WHERE pay_success_time < '2020-03-08 00:00:00' 加鎖規(guī)則是：order_record 表鎖，order_today 逐步鎖（掃描一個(gè)鎖一個(gè)）。

分析執(zhí)行過程：

mysql> explain insert into order_record select * from order_today WHERE pay_success_time < '2020-03-08 00:00:00';
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table                    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | INSERT      | order_record             | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL   |     NULL | NULL        |
|  1 | SIMPLE      | order_today              | NULL       | ALL  | NULL          | NULL | NULL    | NULL |1789916 |    33.33 | Using where |
+----+-------------+--------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set (0.00 sec)

通過觀察遷移 sql 的執(zhí)行情況，你會發(fā)現(xiàn) order_today 是全表掃描，也就意味著在執(zhí)行 insert into select from 語句時(shí)，mysql 會從上到下掃描 order_today 內(nèi)的記錄并且加鎖，這樣一來不就和直接鎖表是一樣了。

這也就可以解釋，為什么一開始只有少量用戶出現(xiàn)支付失敗，后續(xù)大量用戶出現(xiàn)支付失敗，初始化訂單失敗等情況。因?yàn)橐婚_始只鎖定了少部分?jǐn)?shù)據(jù)，沒有被鎖定的數(shù)據(jù)還是可以正常被修改為正常狀態(tài)。

由于鎖定的數(shù)據(jù)越來越多，就導(dǎo)致出現(xiàn)了大量支付失敗。最后全部鎖住，導(dǎo)致無法插入訂單，而出現(xiàn)初始化訂單失敗。

五、解決方案

由于查詢條件會導(dǎo)致 order_today 全表掃描，什么能避免全表掃描呢，很簡單，給 pay_success_time 字段添加一個(gè) idx_pay_suc_time 索引就可以了，由于走索引查詢，就不會出現(xiàn)掃描全表的情況而鎖表了，只會鎖定符合條件的記錄。

最終的 sql：

ALTER TABLE order_today ADD INDEX idx_pay_suc_time (pay_success_time);
INSERT INTO order_record 
SELECT 
  * 
FROM
  order_today FORCE INDEX (idx_pay_suc_time) 
WHERE pay_success_time <= '2020-03-08 00:00:00' ;

執(zhí)行過程：

mysql> EXPLAIN INSERT INTO order_record SELECT * FROM order_today FORCE INDEX (idx_pay_suc_time) WHERE pay_success_time <= '2020-03-08 00:00:00';
+----+-------------+--------------------------+------------+------+-----------------+------------------+---------+------+------+----------+-----------------------+
| id | select_type | table                    | partitions | type | possible_keys   | key  			   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------------------+------------+------+-----------------+------------------+---------+------+------+----------+-----------------------+
|  1 | INSERT      | order_record             | NULL       | ALL  | NULL            | NULL             | NULL    | NULL | NULL |     NULL | NULL                  |
|  1 | SIMPLE      | order_today              | NULL       | range| idx_pay_suc_time| idx_pay_suc_time | 5       | NULL |89916 |    100.00| Using index condition |
+----+-------------+--------------------------+------------+------+-----------------+------------------+---------+------+------+----------+-----------------------+
2 rows in set (0.00 sec)