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

前言

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

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

事故發(fā)生的經(jīng)過(guò)。

由于數(shù)據(jù)數(shù)據(jù)庫(kù)中order_today數(shù)據(jù)量過(guò)大，當(dāng)時(shí)好像有700W了并且每天在以30W的速度增加。所以上司命令xxx將order_today內(nèi)的部分?jǐn)?shù)據(jù)遷移到order_record中，并將order_today中的數(shù)據(jù)刪除。這樣來(lái)降低order_today表中的數(shù)據(jù)量。

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

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

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

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

事故還原

在本地建立一個(gè)精簡(jiǎn)版的數(shù)據(jù)庫(kù)，并生成了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?'商戶編號(hào)',
`amount`?decimal(15,2)?NOT?NULL?COMMENT?'訂單金額',
`pay_success_time`?datetime?NOT?NULL?COMMENT?'支付成功時(shí)間',
`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)建時(shí)間',
`update_time`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'修改時(shí)間?--?修改時(shí)自動(dòng)更新',
??PRIMARY?KEY?(`id`)?USING?BTREE,
KEY?`idx_merchant_id`?(`merchant_id`)?USING?BTREE?COMMENT?'商戶編號(hào)'
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;

訂單記錄表

CREATE?TABLE?order_record?like?order_today;

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

模擬遷移

把8號(hào)之前的數(shù)據(jù)都遷移到order_record表中去。面試題推薦：100期面試題匯總

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

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

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

出現(xiàn)的原因

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

分析執(zhí)行過(guò)程。

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

這也就可以解釋，為什么一開始只有少量用戶出現(xiàn)支付失敗，后續(xù)大量用戶出現(xiàn)支付失敗，初始化訂單失敗等情況，因?yàn)橐婚_始只鎖定了少部分?jǐn)?shù)據(jù)，沒(méi)有被鎖定的數(shù)據(jù)還是可以正常被修改為正常狀態(tài)。由于鎖定的數(shù)據(jù)越來(lái)越多，就導(dǎo)致出現(xiàn)了大量支付失敗。最后全部鎖住，導(dǎo)致無(wú)法插入訂單，而出現(xiàn)初始化訂單失敗。

解決方案

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

最終的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í)行過(guò)程

總結(jié)

使用insert into tablA select * from tableB語(yǔ)句時(shí)，一定要確保tableB后面的where，order或者其他條件，都需要有對(duì)應(yīng)的索引，來(lái)避免出現(xiàn)tableB全部記錄被鎖定的情況。

參考

https://blog.csdn.net/asdfsadfasdfsa/article/details/83030011