Insert into select語句引發(fā)的生產(chǎn)事故

image

前言

??Insert into select請慎用钞翔。這天xxx接到一個需求，需要將表A的數(shù)據(jù)遷移到表B中去做一個備份哮笆。本想通過程序先查詢查出來然后批量插入汰扭。但xxx覺得這樣有點慢福铅，需要耗費大量的網(wǎng)絡(luò)I/O，決定采取別的方法進行實現(xiàn)滑黔。通過在Baidu的海洋里遨游，他發(fā)現(xiàn)了可以使用insert into select實現(xiàn)庵佣，這樣就可以避免使用網(wǎng)絡(luò)I/O汛兜，直接使用SQL依靠數(shù)據(jù)庫I/O完成，這樣簡直不要太棒了粥谬。然后他就被開除了。

事故發(fā)生的經(jīng)過派哲。

??由于數(shù)據(jù)數(shù)據(jù)庫中order_today數(shù)據(jù)量過大掺喻，當(dāng)時好像有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ù)题诵，計劃是9:00以后開始遷移层皱，但是xxx在8:00的時候，嘗試遷移了少部分?jǐn)?shù)據(jù)(1000條)叫胖，覺得沒啥問題草冈，就開始考慮大批量遷移怎棱。

image

在遷移的過程中，應(yīng)急群是先反應(yīng)有小部分用戶出現(xiàn)支付失敗凡资，隨后反應(yīng)大批用戶出現(xiàn)支付失敗的情況谬运，以及初始化訂單失敗的情況，同時騰訊也開始報警梆暖。

image

然后xxx就慌了，立即停止了遷移咐扭。

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

事故還原

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

建立表結(jié)構(gòu)

訂單表

CREATE TABLE `order_today` (
  `id` varchar(32) NOT NULL COMMENT '主鍵',
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商戶編號',
  `amount` decimal(15,2) NOT NULL COMMENT '訂單金額',
  `pay_success_time` datetime NOT NULL COMMENT '支付成功時間',
  `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付狀態(tài)  S：支付成功诱咏、F：訂單支付失敗',
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '備注',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間 -- 修改時自動更新',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商戶編號'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

訂單記錄表

CREATE TABLE order_record like order_today;

今日訂單表數(shù)據(jù)

image

模擬遷移

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

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

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

image

??從上面可以發(fā)現(xiàn)一開始能正常插入早处，但是后面突然就卡住了瘫析，并且耗費了23s才成功默责，然后才能繼續(xù)插入么库。這個時候已經(jīng)遷移成功了甘有，所以能正常插入了。

出現(xiàn)的原因

??在默認(rèn)的事務(wù)隔離級別下：insert into order_record select * from order_today 加鎖規(guī)則是：order_record表鎖亏掀，order_today逐步鎖（掃描一個鎖一個）。

分析執(zhí)行過程滤愕。

image

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

??這也就可以解釋付燥，為什么一開始只有少量用戶出現(xiàn)支付失敗，后續(xù)大量用戶出現(xià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字段添加一個idx_pay_suc_time索引就可以了，由于走索引查詢恢氯，就不會出現(xiàn)掃描全表的情況而鎖表了，只會鎖定符合條件的記錄勋拟。

最終的sql

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í)行過程

image

總結(jié)

??使用insert into tablA select * from tableB語句時敢靡，一定要確保tableB后面的where，order或者其他條件啸胧，都需要有對應(yīng)的索引幔虏，來避免出現(xiàn)tableB全部記錄被鎖定的情況。

參考文章

insert into ... select 由于SELECT表引起的死鎖情況分析

結(jié)尾

??如果覺得對你有幫助所计，可以多多評論，多多點贊哦主胧，也可以到我的主頁看看，說不定有你喜歡的文章焙格，也可以隨手點個關(guān)注哦，謝謝眷唉。

??我是不一樣的科技宅囤官，每天進步一點點冬阳，體驗不一樣的生活肝陪。我們下期見！