不就加個(gè)字段嘛？怎么線上業(yè)務(wù)都掛了！
預(yù)熱
比較喜歡的一段話：不經(jīng)一番寒徹骨，怎得梅花撲鼻香，學(xué)習(xí)是枯燥的請(qǐng)大家堅(jiān)持！這篇文章的是向丁奇老師學(xué)習(xí)的。不懂的自己搜一下哈！ 閱讀這篇文章大概需要35分鐘！
大家好前面我們大概了解了MySQL索引的選擇，innodb數(shù)據(jù)結(jié)構(gòu)的選擇，索引，覆蓋索引，查詢優(yōu)化的細(xì)節(jié)。今天我們介紹一下數(shù)據(jù)庫(kù)全局鎖，表鎖，行鎖，兩階段鎖協(xié)議以及案例！
相信大家絕對(duì)聽(tīng)過(guò)這么一個(gè)事故。某互聯(lián)網(wǎng)大廠程序員新增了一個(gè)字段導(dǎo)致線上業(yè)務(wù)癱瘓。沒(méi)聽(tīng)過(guò)也沒(méi)有關(guān)系，我們接下來(lái)一起慢慢分析。
線上業(yè)務(wù)的癱瘓主要因?yàn)樗梨i導(dǎo)致的資源耗盡，導(dǎo)致崩盤。那么我們就開(kāi)始講述一下為什么會(huì)死鎖。MySQL的鎖主要分為全局鎖，表鎖，行級(jí)鎖。下面我們?cè)敿?xì)介紹一下。
開(kāi)始
全局鎖
定義
全局鎖：顧名思義這個(gè)是整個(gè)MySQL實(shí)例的鎖。這是MySQL自己提供的一種加鎖方法。命令是
Flush tables with read lock (FTWRL)
當(dāng)你需要把整個(gè)數(shù)據(jù)庫(kù)都加鎖的時(shí)候，或者使數(shù)據(jù)庫(kù)處于只讀狀態(tài)的話，可以通過(guò)這種方式。通過(guò)這種方式之后，所有的更新修改操作，定義操作都將阻塞。
tip：更新修改操作：新增，修改，刪除。定義操作：新建表，修改表結(jié)構(gòu)。
應(yīng)用場(chǎng)景
全局鎖是全庫(kù)邏輯備份的核心，也就是把所有數(shù)據(jù)輸出到文件上。以前這種做法在執(zhí)行的時(shí)候會(huì)把整個(gè)實(shí)例數(shù)據(jù)庫(kù)上鎖，變成只讀模式。聽(tīng)上去就很危險(xiǎn)。萬(wàn)一在這段期間有人寫庫(kù)怎么辦。沒(méi)錯(cuò)，沒(méi)辦法寫只能等。
如果分主庫(kù)，從庫(kù)的話。如果在主庫(kù)上備份，那么整個(gè)業(yè)務(wù)都將停頓。如果在從庫(kù)備份的話，由于數(shù)據(jù)庫(kù)主從復(fù)制的關(guān)系那么備份期間從庫(kù)不能執(zhí)行主庫(kù)同步的binlog日志，會(huì)導(dǎo)致主從延遲，甚至數(shù)據(jù)不一致。
看起來(lái)全局加鎖進(jìn)行備份看起來(lái)不太友好。我們?cè)賮?lái)反問(wèn)一下為什么備份要加鎖呢？我估計(jì)很多人應(yīng)該猜到了，為了保證數(shù)據(jù)在某一時(shí)刻的視圖是一致的。下面我們舉個(gè)例子來(lái)說(shuō)明一下這個(gè)問(wèn)題。
案例
就舉我個(gè)人開(kāi)發(fā)的電商系統(tǒng)來(lái)說(shuō)吧。我備份的是8月27日之前的所有數(shù)據(jù)，如果不進(jìn)行加鎖的話這個(gè)時(shí)候有個(gè)用戶正在下單，那么涉及的是訂單表的父子表，商品子表。庫(kù)存表。還有用戶的子表操作。訂單表要新增一條記錄，訂單子表要新增這個(gè)訂單的詳細(xì)商品信息。商品子表的購(gòu)買量。庫(kù)存表的消減庫(kù)存。還有用戶子表的積分情況。如果這個(gè)時(shí)候執(zhí)行完了 訂單表，庫(kù)存表。這個(gè)時(shí)候備份到了這個(gè)位置，就會(huì)存在這種很不符合常規(guī)的情況。訂單已下，商品已處理，用戶已付款，但是用戶的積分沒(méi)有加。
等恢復(fù)數(shù)據(jù)的時(shí)候用戶就會(huì)發(fā)現(xiàn)自己少了一筆積分。這樣的程序是錯(cuò)誤的。如果備份表的順序反過(guò)來(lái)就會(huì)存在用戶多了莫名的積分。但是訂單表沒(méi)下。這樣就導(dǎo)致了老板多贈(zèng)送了積分。這樣也是不對(duì)的。這就是加鎖與不加鎖最終的效果。
備份工具
簡(jiǎn)介
mysqldump 是 MySQL 自帶的邏輯備份工具。
它的備份原理是通過(guò)協(xié)議連接到 MySQL 數(shù)據(jù)庫(kù)，將需要備份的數(shù)據(jù)查詢出來(lái)，將查詢出的數(shù)據(jù)轉(zhuǎn)換成對(duì)應(yīng)的insert 語(yǔ)句，當(dāng)我們需要還原這些數(shù)據(jù)時(shí)，只要執(zhí)行這些 insert 語(yǔ)句，即可將對(duì)應(yīng)的數(shù)據(jù)還原。
當(dāng) mysqldump 使用參數(shù)–single-transaction 的時(shí)候，導(dǎo)數(shù)據(jù)之前就會(huì)啟動(dòng)一個(gè)事務(wù)，來(lái)確保拿到一致性視圖。而由于 MVCC 的支持，這個(gè)過(guò)程中數(shù)據(jù)是可以正常更新的。
結(jié)論
疑問(wèn)來(lái)了，MySQL自帶了一個(gè)備份工具mysqldump，那么我們?yōu)槭裁催€需要用全局鎖呢？因?yàn)檫@個(gè)mysqldump必須支持隔離級(jí)別。而myisam不支持事務(wù)的引擎，所以有些時(shí)候不支持事務(wù)的時(shí)候，并且要數(shù)據(jù)一致性的話就必須使用全局鎖啦。
只讀模式
MySQL是有一個(gè)只讀模式的，通過(guò)set global readonly=true可以達(dá)到整個(gè)庫(kù)都處于只讀模式。但是不建議使用這種方式進(jìn)行備份。
先介紹一下只讀模式的機(jī)制吧。在正常的情況下，有些業(yè)務(wù)邏輯上會(huì)借助readonly值用來(lái)做其他邏輯處理。比如判斷一個(gè)庫(kù)是主庫(kù)還是從庫(kù)。因此修改readonly有可能會(huì)引發(fā)不必要的麻煩。
在出現(xiàn)異常情況下，客戶端發(fā)生異常，則數(shù)據(jù)庫(kù)會(huì)一直保持只讀狀態(tài)。這樣會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間不可寫的狀態(tài)，風(fēng)險(xiǎn)極高。
全局鎖的話如果客戶端發(fā)生異常，那么MySQL就會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫(kù)回到正常的狀態(tài)。
tip：主機(jī)只負(fù)責(zé)增刪改，從庫(kù)只做查詢。
表級(jí)鎖
定義
業(yè)務(wù)的更新不只是增刪改數(shù)據(jù)（DML數(shù)據(jù)操縱語(yǔ)言)，還有可能是加字段等修改表結(jié)構(gòu)的操作（DDL數(shù)據(jù)定義語(yǔ)言）。不論是哪種方法，一個(gè)庫(kù)被全局鎖上以后，你要對(duì)里面任何一個(gè)表做加字段操作，都是會(huì)被鎖住的。即使沒(méi)有被全局鎖住，加字段也不是就能一帆風(fēng)順的，因?yàn)槟氵€會(huì)碰到表級(jí)鎖。
表級(jí)鎖中又分為表鎖和元數(shù)據(jù)鎖(MDL metadata lock)。
表鎖的語(yǔ)法是 lock t_user read/write 上鎖，unlock tables 手動(dòng)解鎖。也可以在客戶端斷開(kāi)的時(shí)候自動(dòng)釋放鎖。表鎖會(huì)限制別的線程的讀寫操作。
舉個(gè)例子, 如果在某個(gè)線程 A 中執(zhí)行 lock tables t1 read, t2 write; 這個(gè)語(yǔ)句，則其他線程寫 t1、讀寫 t2 的語(yǔ)句都會(huì)被阻塞。同時(shí)，線程 A 在執(zhí)行 unlock tables 之前，也只能執(zhí)行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許，自然也不能訪問(wèn)其他表。
再還沒(méi)有出現(xiàn)更細(xì)粒度鎖的時(shí)候，表鎖是處理并發(fā)的常用方式。而對(duì)于innodb來(lái)說(shuō)，表鎖不是最好的處理方式。最好的 處理方式是行鎖。下面我會(huì)一一介紹
元數(shù)據(jù)鎖(MDL)不需要顯式使用，在訪問(wèn)一個(gè)表的時(shí)候會(huì)自動(dòng)加上。MDL的作用就是保存讀寫的一致性。用白話文來(lái)說(shuō)就是，當(dāng)一個(gè)線程正在遍歷列表查詢數(shù)據(jù)的時(shí)候，另一個(gè)線程插了過(guò)來(lái)，修改了一個(gè)字段。那么查詢的結(jié)構(gòu)肯定對(duì)不上了呀。所以MDL解決的就是這種問(wèn)題。
MDL是MySQL5.5的時(shí)候引入的，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL 讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫鎖。
讀鎖之間不互斥，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。
讀寫鎖之間、寫鎖之間是互斥的，用來(lái)保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開(kāi)始執(zhí)行
使用MDL鎖時(shí)，有一個(gè)坑就是。雖然MDL是系統(tǒng)默認(rèn)加的，但是你不能忽略的一個(gè)機(jī)制。我們將舉例說(shuō)明如下圖
當(dāng)sessionA發(fā)起一個(gè)查詢請(qǐng)求的時(shí)候會(huì)先加一個(gè)讀鎖
sessionB發(fā)起一個(gè)查詢請(qǐng)求也會(huì)加一個(gè)讀鎖，讀鎖之前是不互斥的，所以不會(huì)互相影響
sessionC發(fā)起一個(gè)修改請(qǐng)求會(huì)加一個(gè)寫鎖，寫鎖之前需要獲得一個(gè)讀鎖，而讀鎖在sessionA與sessionB都沒(méi)有釋放。這個(gè)時(shí)候sessionC就堵塞了，這一個(gè)用戶堵塞還不要緊，最要命的話所有用戶都堵塞。
sessionD發(fā)起一個(gè)查詢請(qǐng)求。那么查詢請(qǐng)求之前要加一個(gè)讀鎖，讀鎖跟sessionC的寫鎖互斥導(dǎo)致讀寫爭(zhēng)持被死鎖。
如果某個(gè)表查詢請(qǐng)求頻繁，而客戶端又有重連機(jī)制的話，那么線程，內(nèi)存很快就被刷滿了。而且在執(zhí)行事務(wù)的時(shí)候不是立馬加鎖，而是語(yǔ)句執(zhí)行時(shí)再加鎖，語(yǔ)句結(jié)束后并不會(huì)立馬釋放鎖，而是要等事務(wù)提交之后才會(huì)釋放鎖。
那么問(wèn)題來(lái)了，如何安全的給表加字段呢？
首先我們要解決長(zhǎng)事務(wù)，事務(wù)不提交，就會(huì)一直占著 MDL 鎖。在 MySQL 的 information_schema 庫(kù)的 innodb_trx 表中，你可以查到當(dāng)前執(zhí)行中的事務(wù)。如果你要做 DDL 變更的表剛好有長(zhǎng)事務(wù)在執(zhí)行，要考慮先暫停 DDL，或者 kill 掉這個(gè)長(zhǎng)事務(wù)。
如果你要變更的表是一個(gè)熱點(diǎn)表，雖然數(shù)據(jù)量不大，但是上面的請(qǐng)求很頻繁，而你不得不加個(gè)字段，你該怎么做呢？這時(shí)候 kill 可能未必管用，因?yàn)樾碌恼?qǐng)求馬上就來(lái)了。
比較理想的機(jī)制是，在 alter table 語(yǔ)句里面設(shè)定等待時(shí)間，如果在這個(gè)指定的等待時(shí)間里面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞后面的業(yè)務(wù)語(yǔ)句，先放棄。之后開(kāi)發(fā)人員或者 DBA 再通過(guò)重試命令重復(fù)這個(gè)過(guò)程。MariaDB 已經(jīng)合并了 AliSQL 的這個(gè)功能，所以這兩個(gè)開(kāi)源分支目前都支持 DDL NOWAIT/WAIT n 這個(gè)語(yǔ)法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
行鎖
定義：
介紹完表鎖我們來(lái)聊一下行鎖。行鎖是表鎖的更小粒度的鎖。行鎖是在MySQL引擎層由各個(gè)引擎實(shí)現(xiàn)的。這并不意味著所有的引擎都支持，比如myisam就不支持行鎖。同時(shí)也就意味著myisam在處理并發(fā)需求的時(shí)候只能通過(guò)表鎖來(lái)實(shí)現(xiàn)相應(yīng)的需求。這也是MySQL默認(rèn)引擎選擇innodb的原則之一。
行鎖是針對(duì)數(shù)據(jù)表中的單個(gè)記錄的鎖。比如事務(wù)A更新了ID=10這條數(shù)據(jù)，這個(gè)時(shí)候事務(wù)B也更新了ID=10這條數(shù)據(jù)。那么事務(wù)B想正常執(zhí)行，就必須等待事務(wù)A提交事務(wù)之后才可以。也就可以完美的解決并發(fā)情況下的需求。
舉一個(gè)容易出錯(cuò)的鎖：兩階段鎖
通過(guò)如下圖我們繼續(xù)分析。事務(wù)A更新的是ID為1,2這兩條數(shù)據(jù)，而事務(wù)B是更新ID=1這條數(shù)據(jù)。那么兩條數(shù)據(jù)都在修改ID=1的這條數(shù)據(jù)。這種執(zhí)行流程就需要有一個(gè)先后順序，必須事務(wù)A執(zhí)行完之后事務(wù)B才可以執(zhí)行。
tip：修改完成之后，不釋放鎖，只有等事務(wù)A提交之后才釋放鎖。這就是兩階段鎖協(xié)議
應(yīng)用場(chǎng)景：
接下我們介紹一下使用手法問(wèn)題，如何提交系統(tǒng)的并發(fā)處理能力？
如果你的事務(wù)中需要鎖多個(gè)行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放。我給你舉個(gè)例子
這個(gè)例子是我設(shè)計(jì)過(guò)的一個(gè)項(xiàng)目，電影院交易系統(tǒng)，顧客 A 要在影院 B 購(gòu)買電影票。我們簡(jiǎn)化一點(diǎn)，這個(gè)業(yè)務(wù)需要涉及到以下操作：
從顧客 A 賬戶余額中扣除電影票價(jià)；
給影院 B 的賬戶余額增加這張電影票價(jià)；
記錄一條交易日志。
也就是說(shuō)，要完成這個(gè)交易，我們需要 update 兩條記錄，并 insert 一條記錄。當(dāng)然，為了保證交易的原子性，我們要把這三個(gè)操作放在一個(gè)事務(wù)中。那么，你會(huì)怎樣安排這三個(gè)語(yǔ)句在事務(wù)中的順序呢？
試想如果同時(shí)有另外一個(gè)顧客 C 要在影院 B 買票，那么這兩個(gè)事務(wù)沖突的部分就是語(yǔ)句 2 了。因?yàn)樗鼈円峦粋€(gè)影院賬戶的余額，需要修改同一行數(shù)據(jù)。
根據(jù)兩階段鎖協(xié)議，不論你怎樣安排語(yǔ)句順序，所有的操作需要的行鎖都是在事務(wù)提交的時(shí)候才釋放的。所以，如果你把語(yǔ)句 2 安排在最后，比如按照 3、1、2 這樣的順序，那么影院賬戶余額這一行的鎖時(shí)間就最少。這就最大程度地減少了事務(wù)之間的鎖等待，提升了并發(fā)度。
如果這個(gè)影院做活動(dòng)，可以低價(jià)預(yù)售一年內(nèi)所有的電影票，而且這個(gè)活動(dòng)只做一天。于是在活動(dòng)時(shí)間開(kāi)始的時(shí)候，你的 MySQL 就掛了。你登上服務(wù)器一看，CPU 消耗接近 100%，但整個(gè)數(shù)據(jù)庫(kù)每秒就執(zhí)行不到 100 個(gè)事務(wù)。這是什么原因呢？這里，我就要說(shuō)到死鎖和死鎖檢測(cè)了。
死鎖，死鎖檢測(cè)
當(dāng)并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時(shí)，就會(huì)導(dǎo)致這幾個(gè)線程都進(jìn)入無(wú)限等待的狀態(tài)，稱為死鎖。這里我用數(shù)據(jù)庫(kù)中的行鎖舉個(gè)例子。
事務(wù)A要修改ID=1和ID=2這兩條數(shù)據(jù)，事務(wù)B在要修改ID=2和ID=1這兩條數(shù)據(jù)，
事務(wù)A在等事務(wù)B釋放ID=2這條數(shù)據(jù)，事務(wù)B在等待事務(wù)A釋放ID=1這條數(shù)據(jù)的鎖。雙方都在等那么就進(jìn)入了僵持階段，導(dǎo)致死鎖。
這種問(wèn)題有兩種解決方案
通過(guò)設(shè)置 innodb_lock_wait_timeout這個(gè)參數(shù)來(lái)決定超時(shí)機(jī)制，自動(dòng)釋放。
發(fā)現(xiàn)死鎖后，通過(guò)回滾一條事務(wù)，使另一條事務(wù)能執(zhí)行下去，也就是放行的意思。通過(guò)把innodb_deadlock_detect 參數(shù)設(shè)置為 on（死鎖檢測(cè)）
我們來(lái)一一分析兩種方案的可行性。
通過(guò)超時(shí)機(jī)制的話，系統(tǒng)默認(rèn)的超市機(jī)制是50秒，也就意味著50秒后才會(huì)進(jìn)入超時(shí)機(jī)制。對(duì)于線上業(yè)務(wù)來(lái)說(shuō)，等待50秒這顯然是一件不現(xiàn)實(shí)的事情。而如果設(shè)置過(guò)短的話，有可能真正超時(shí)的時(shí)候，會(huì)有很多情況處理異常，會(huì)有誤傷的情況。
回滾事務(wù)目前是最好的解決方案。innodb_deadlock_detect 的默認(rèn)值本身就是 on。死鎖檢測(cè)是立即生效的。
每當(dāng)一個(gè)事務(wù)被鎖的時(shí)候，就要看看它所依賴的線程有沒(méi)有被別人鎖住，如此循環(huán)，最后判斷是否出現(xiàn)了循環(huán)等待，也就是死鎖。
那如果是我們上面說(shuō)到的所有事務(wù)都要更新同一行的場(chǎng)景呢？
每個(gè)新來(lái)的被堵住的線程，都要判斷會(huì)不會(huì)由于自己的加入導(dǎo)致了死鎖，這是一個(gè)時(shí)間復(fù)雜度是 O(n) 的操作。假設(shè)有 1000 個(gè)并發(fā)線程要同時(shí)更新同一行，那么死鎖檢測(cè)操作就是 100 萬(wàn)這個(gè)量級(jí)的。雖然最終檢測(cè)的結(jié)果是沒(méi)有死鎖，但是這期間要消耗大量的 CPU 資源。因此，你就會(huì)看到 CPU 利用率很高，但是每秒?yún)s執(zhí)行不了幾個(gè)事務(wù)。
解決方案： 我們從設(shè)計(jì)上入手。你可以考慮通過(guò)將一行改成邏輯上的多行來(lái)減少鎖沖突。還是以影院賬戶為例，可以考慮放在多條記錄上，比如 10 個(gè)記錄，影院的賬戶總額等于這 10 個(gè)記錄的值的總和。這樣每次要給影院賬戶加金額的時(shí)候，隨機(jī)選其中一條記錄來(lái)加。這樣每次沖突概率變成原來(lái)的 1/10，可以減少鎖等待個(gè)數(shù)，也就減少了死鎖檢測(cè)的 CPU 消耗。這個(gè)方案看上去是無(wú)損的，但其實(shí)這類方案需要根據(jù)業(yè)務(wù)邏輯做詳細(xì)設(shè)計(jì)。如果賬戶余額可能會(huì)減少，比如退票邏輯，那么這時(shí)候就需要考慮當(dāng)一部分行記錄變成 0 的時(shí)候，代碼要有特殊處理。