面試題1：先說(shuō)一下什么是MySQL事務(wù)吧

正經(jīng)回答：

簡(jiǎn)單說(shuō)，事務(wù)就是一組原子性的SQL執(zhí)行單元。如果數(shù)據(jù)庫(kù)引擎能夠成功地對(duì)數(shù)據(jù)庫(kù)應(yīng) 用該組査詢(xún)的全部語(yǔ)句，那么就執(zhí)行該組SQL。如果其中有任何一條語(yǔ)句因?yàn)楸罎⒒蚱?他原因無(wú)法執(zhí)行，那么所有的語(yǔ)句都不會(huì)執(zhí)行。要么全部執(zhí)行成功（commit），要么全部執(zhí)行失?。╮ollback）。

這里引用銀行轉(zhuǎn)賬的例子，假設(shè)銀行的數(shù)據(jù)庫(kù)有兩張表：信用卡(credit)表和儲(chǔ)蓄(savings)表。用戶(hù)陳哈哈要把信用卡里最后100塊錢(qián)額度轉(zhuǎn)到他 的儲(chǔ)蓄賬戶(hù)用來(lái)吃飯，那么需要至少三個(gè)步驟：

• 檢査信用卡余額是否髙于100塊錢(qián)。
• 從信用卡賬戶(hù)余額中減去100塊錢(qián)。
• 在儲(chǔ)蓄賬戶(hù)余額中增加100塊錢(qián)。

上述三個(gè)步驟必須在同一個(gè)事務(wù)中執(zhí)行，任何一個(gè)SQL失敗，則必須回滾所有的SQL。這里用START TRANSACTION語(yǔ)句開(kāi)啟事務(wù)，要么使用COMMIT提交事務(wù)將修改的數(shù)據(jù)持久保留，要么使用ROLLBACK銷(xiāo)所有的修改。事務(wù)SQL的樣本如下：

START TRANSACTION;
-- 檢查信用卡賬戶(hù)額度
SELECT balance FROM credit WHERE customer_id = 'chenhh';
-- 信用卡表扣錢(qián)
UPDATE credit SET balance = balance - 100.00 WHERE customer_id = 'chenhh';
-- 儲(chǔ)蓄表加錢(qián)
UPDATE savings SET balance = balance + 100.00 WHERE customer_id = 'chenhh';
COMMIT;

試想一下，如果執(zhí)行到第四條語(yǔ)句時(shí)服務(wù)器崩潰了，會(huì)發(fā)生什么？廢話(huà)，我被坑了100塊錢(qián)，中午只能餓肚子！再假如，在執(zhí)行到第三條語(yǔ)句和第四 條語(yǔ)句之間時(shí)，同一時(shí)間，另外一個(gè)進(jìn)程，來(lái)自商場(chǎng)結(jié)賬的女朋友，也要信用卡賬戶(hù)的100塊，那么結(jié)果可能就是銀行在不知道這個(gè)邏輯的情況下白白給了陳哈哈女朋友100塊錢(qián)？

深入追問(wèn)： 追問(wèn)1：說(shuō)一下你對(duì)ACID四大特性的理解

該問(wèn)題來(lái)自“MySQL江湖路”專(zhuān)欄中的博文：《數(shù)據(jù)庫(kù)ACID四大特性到底為了啥，一文帶你看通透》

ACID特性：原子性、一致性、隔離性、持久性

原子性(Atomicity)

單個(gè)事務(wù)，為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部commit成功，要么全部失敗rollback，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)，不可能只執(zhí)行其中的一部分SQL操作，這就是事務(wù)的原子性。

一致性(Consistency)

數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)。在前面的例子中， 一致性確保了，即使在執(zhí)行第三、四條語(yǔ)句之間時(shí)系統(tǒng)崩潰，信用卡賬戶(hù)也不會(huì)損 失100塊，因?yàn)槭聞?wù)最終沒(méi)有提交，所以事務(wù)中所做的修改也不會(huì)保存到數(shù)據(jù)庫(kù)中，保證數(shù)據(jù)一致性。

隔離性(Isolation)

通常來(lái)說(shuō)，一個(gè)事務(wù)所做的修改在最終提交以前，對(duì)其他事務(wù)是不可見(jiàn)的。在前面 的例子中，當(dāng)執(zhí)行完第三條語(yǔ)句、第四條語(yǔ)句還未開(kāi)始時(shí)，此時(shí)有另外一個(gè)賬戶(hù)查詢(xún)余額SQL開(kāi)始運(yùn)行，則其看到的信用卡賬戶(hù)的余額并沒(méi)有被減去100元。后面我們討論隔離級(jí)別(Isolation level)的時(shí)候，會(huì)發(fā)現(xiàn)為什么我們要說(shuō)事務(wù)通常來(lái)說(shuō)是不可見(jiàn)的。

持久性(Durability)

一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中。此時(shí)即使系統(tǒng)崩潰，修改的數(shù)據(jù)也不會(huì)丟失。

事務(wù)的ACID特性可以確保銀行不會(huì)弄丟你的錢(qián)。而在應(yīng)用邏輯中，要實(shí)現(xiàn)這一點(diǎn)非常難, 甚至可以說(shuō)是不可能完成的任務(wù)。一個(gè)兼容ACID的數(shù)據(jù)庫(kù)系統(tǒng)，需要做很多復(fù)雜但可能用戶(hù)并沒(méi)有覺(jué)察到的工作，才能確保ACID的實(shí)現(xiàn)。

追問(wèn)2：可以從原理上聊一下ACID具體是怎么實(shí)現(xiàn)的么？

對(duì)MySQL來(lái)說(shuō)，邏輯備份日志（binlog）、重做日志（redolog）、回滾日志（undolog）、鎖技術(shù) + MVCC就是MySQL實(shí)現(xiàn)事務(wù)的基礎(chǔ)。

• 原子性：通過(guò)undolog來(lái)實(shí)現(xiàn)。
• 持久性：通過(guò)binlog、redolog來(lái)實(shí)現(xiàn)。
• 隔離性：通過(guò)(讀寫(xiě)鎖+MVCC)來(lái)實(shí)現(xiàn)。
• 一致性：MySQL通過(guò)原子性，持久性，隔離性最終實(shí)現(xiàn)（或者說(shuō)定義）數(shù)據(jù)一致性。

1、原子性原理

事務(wù)通常是以BEGIN TRANSACTION 開(kāi)始，以 COMMIT 或 ROLLBACK 結(jié)束。

• COMMIT 表示提交，即提交事務(wù)的所有操作并持久化到數(shù)據(jù)庫(kù)中。
• ROLLBACK表示回滾，即在事務(wù)中運(yùn)行的過(guò)程中發(fā)生了某種故障，事務(wù)不能繼續(xù)執(zhí)行，系統(tǒng)將事務(wù)中對(duì)數(shù)據(jù)庫(kù)所有已完成的操作全部撤銷(xiāo)，回滾到事務(wù)開(kāi)始時(shí)的狀態(tài)，這里的操作指對(duì)數(shù)據(jù)庫(kù)的更新操作，已執(zhí)行的查詢(xún)操作不用管。這時(shí)候也就需要用到 undolog 來(lái)進(jìn)行回滾。

undolog：

• 每條數(shù)據(jù)變更（INSERT/UPDATE/DELETE/REPLACE）等操作都會(huì)生成一條undolog記錄，在SQL執(zhí)行前先于數(shù)據(jù)持久化到磁盤(pán)。
• 當(dāng)事務(wù)需要回滾時(shí)，MySQL會(huì)根據(jù)回滾日志對(duì)事務(wù)中已執(zhí)行的SQL做逆向操作，比如 DELETE 掉一行數(shù)據(jù)的逆向操作就是再把這行數(shù)據(jù) INSERT回去，其他操作同理。

2、持久性原理

先了解一下MySQL的數(shù)據(jù)存儲(chǔ)機(jī)制，MySQL的表數(shù)據(jù)是存放在磁盤(pán)上的，因此想要存取的時(shí)候都要經(jīng)歷磁盤(pán) IO,然而即使是使用 SSD 磁盤(pán) IO 也是非常消耗性能的。為此，為了提升性能 InnoDB 提供了緩沖池(Buffer Pool)，Buffer Pool 中包含了磁盤(pán)數(shù)據(jù)頁(yè)的映射，可以當(dāng)做緩存來(lái)使用：

• 讀數(shù)據(jù)：會(huì)首先從緩沖池中讀取，如果緩沖池中沒(méi)有，則從磁盤(pán)讀取在放入緩沖池；
• 寫(xiě)數(shù)據(jù)：會(huì)首先寫(xiě)入緩沖池，緩沖池中的數(shù)據(jù)會(huì)定期同步到磁盤(pán)中；

我們知道，MySQL表數(shù)據(jù)是持久化到磁盤(pán)中的，但如果所有操作都去操作磁盤(pán)，等并發(fā)上來(lái)了，那處理速度誰(shuí)都吃不消，因此引入了緩沖池(Buffer Pool)的概念，Buffer Pool 中包含了磁盤(pán)中部分?jǐn)?shù)據(jù)頁(yè)的映射，可以當(dāng)做緩存來(lái)用；這樣當(dāng)修改表數(shù)據(jù)時(shí)，我們把操作記錄先寫(xiě)到Buffer Pool中，并標(biāo)記事務(wù)已完成，等MySQL空閑時(shí)，再把更新操作持久化到磁盤(pán)里（你可能會(huì)問(wèn)，到底什么時(shí)候執(zhí)行持久化呢？1、MySQL線(xiàn)程低于高水位；2、當(dāng)有其他查詢(xún)、更新語(yǔ)句操作該數(shù)據(jù)頁(yè)時(shí)），從而大大緩解了MySQL并發(fā)壓力。

但是它也帶來(lái)了新的問(wèn)題，當(dāng)MySQL系統(tǒng)宕機(jī)，斷電時(shí)Buffer Pool數(shù)據(jù)不就丟了？

因?yàn)槲覀兊臄?shù)據(jù)已經(jīng)提交了，但此時(shí)是在緩沖池里頭，還沒(méi)來(lái)得及在磁盤(pán)持久化，所以我們急需一種機(jī)制需要存一下已提交事務(wù)的數(shù)據(jù)，為恢復(fù)數(shù)據(jù)使用。

于是 redo log + binlog的經(jīng)典組合就登場(chǎng)了，這里不在擴(kuò)展贅述。

3、隔離性原理

隔離性是事務(wù)ACID特性里最復(fù)雜的一個(gè)。在SQL標(biāo)準(zhǔn)里定義了四種隔離級(jí)別，每一種級(jí)別都規(guī)定一個(gè)事務(wù)中的修改，哪些是事務(wù)之間可見(jiàn)的，哪些是不可見(jiàn)的。

級(jí)別越低的隔離級(jí)別可以執(zhí)行越高的并發(fā)，但同時(shí)實(shí)現(xiàn)復(fù)雜度以及開(kāi)銷(xiāo)也越大。

Mysql 隔離級(jí)別有以下四種（級(jí)別由低到高）：

搞懂了隔離級(jí)別以及實(shí)現(xiàn)原理其實(shí)就可以理解ACID里的隔離性了。前面說(shuō)過(guò)原子性，隔離性，持久性的目的都是為了要做到一致性，但隔離型跟其他兩個(gè)有所區(qū)別，原子性和持久性是為了要實(shí)現(xiàn)數(shù)據(jù)的正確、可用，比如要做到宕機(jī)后的恢復(fù)、事務(wù)的回滾等，保證數(shù)據(jù)是正確可用的！

那么隔離性是要做到什么呢？

隔離性要管理的是：多個(gè)并發(fā)讀寫(xiě)請(qǐng)求（事務(wù)）過(guò)來(lái)時(shí)的執(zhí)行順序。像交警在馬路口兒指揮交通一樣，當(dāng)并發(fā)處理多個(gè)DML更新操作時(shí)，如何讓事務(wù)操作他該看到的數(shù)據(jù)，出現(xiàn)多個(gè)事務(wù)處理同一條數(shù)據(jù)時(shí)，讓事務(wù)該排隊(duì)的排隊(duì)，別插隊(duì)搗亂，保證數(shù)據(jù)和事務(wù)的相對(duì)隔離，這就是隔離性要干的事兒。

所以，從隔離性的實(shí)現(xiàn)原理上，我們可以看出這是一場(chǎng)數(shù)據(jù)的可靠性與性能之間的權(quán)衡。

4、一致性原理

一致性，我們要保障的是數(shù)據(jù)一致性，數(shù)據(jù)庫(kù)中的增刪改操作，使數(shù)據(jù)庫(kù)不斷從一個(gè)一致性的狀態(tài)轉(zhuǎn)移到另一個(gè)一致性的狀態(tài)。

事務(wù)該回滾的回滾，該提交的提交，提交后該持久化磁盤(pán)的持久化磁盤(pán)，該寫(xiě)緩沖池的寫(xiě)緩沖池+寫(xiě)日志；對(duì)于數(shù)據(jù)可見(jiàn)性，通過(guò)四種隔離級(jí)別進(jìn)行控制，使得庫(kù)表中的有效數(shù)據(jù)范圍可控，保證業(yè)務(wù)數(shù)據(jù)的正確性的前提下，進(jìn)而提高并發(fā)程度，支撐服務(wù)高QPS的穩(wěn)定運(yùn)行，保證數(shù)據(jù)的一致性，這就是咱們叨叨叨說(shuō)的清楚想不明白的數(shù)據(jù)庫(kù)ACID四大特性。

面試題2：并發(fā)場(chǎng)景下事務(wù)會(huì)存在哪些數(shù)據(jù)問(wèn)題？ 正經(jīng)回答：

并發(fā)場(chǎng)景下MySQL事務(wù)可能會(huì)出現(xiàn)臟讀、幻讀、不可重復(fù)讀問(wèn)題；

臟讀(Drity Read)：某個(gè)事務(wù)已更新一份數(shù)據(jù)，另一個(gè)事務(wù)在此時(shí)讀取了同一份數(shù)據(jù)，由于某些原因，前一個(gè)RollBack了操作，則后一個(gè)事務(wù)所讀取的數(shù)據(jù)就會(huì)是不正確的。

不可重復(fù)讀(Non-repeatable read):在一個(gè)事務(wù)的兩次查詢(xún)之中數(shù)據(jù)不一致，這可能是兩次查詢(xún)過(guò)程中間插入了一個(gè)事務(wù)更新了原有的數(shù)據(jù)。

幻讀(Phantom Read):在一個(gè)事務(wù)的兩次查詢(xún)中數(shù)據(jù)筆數(shù)不一致，例如有一個(gè)事務(wù)查詢(xún)了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻在此時(shí)插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來(lái)的查詢(xún)中，就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒(méi)有的。

深入追問(wèn)：

追問(wèn)1：那Innodb是如何解決幻讀問(wèn)題的呢？

先說(shuō)結(jié)論，MySQL 存儲(chǔ)引擎 InnoDB 在可重復(fù)讀（RR）隔離級(jí)別下是解決了幻讀問(wèn)題的。

方法是通過(guò)next-key lock在當(dāng)前讀事務(wù)開(kāi)啟時(shí)，1.給涉及到的行加寫(xiě)鎖（行鎖）防止寫(xiě)操作；2.給涉及到的行兩端加間隙鎖(Gap Lock)防止新增行寫(xiě)入；從而解決了幻讀問(wèn)題。

幻讀出現(xiàn)的場(chǎng)景： 1.幻讀出現(xiàn)在可重復(fù)讀（RR）隔離級(jí)別下，普通的SELECT查詢(xún)就是快照讀，是不會(huì)看到別的事務(wù)插入的數(shù)據(jù)的。因此，幻讀在“當(dāng)前讀”下才會(huì)出現(xiàn)。（當(dāng)前讀會(huì)生成行鎖，但行鎖只能鎖定存在的行，針對(duì)新插入的操作沒(méi)有限定）

2.上面 session B 的修改結(jié)果，被 session A 之后的 select 語(yǔ)句用“當(dāng)前讀”看到，不能稱(chēng)為幻讀?；米x僅專(zhuān)指“新插入的行”。

3.因?yàn)檫@三個(gè)查詢(xún)都是加了 for update，都是當(dāng)前讀。而當(dāng)前讀的規(guī)則，就是要能讀到所有已經(jīng)提交的記錄的最新值。并且，session B 和 sessionC 的兩條語(yǔ)句，執(zhí)行后就會(huì)提交，所以 Q2 和 Q3 就是應(yīng)該看到這兩個(gè)事務(wù)的操作效果，而且也看到了，這跟事務(wù)的可見(jiàn)性規(guī)則并不矛盾。

幻讀場(chǎng)景實(shí)例：

測(cè)試表數(shù)據(jù)如下：

mysql> select * from LOL; +----+--------------+--------------+-------+ | id | hero_title | hero_name | price | +----+--------------+--------------+-------+ | 1 | 刀鋒之影 | 泰隆 | 6300 | | 2 | 迅捷斥候 | 提莫 | 6300 | | 3 | 光輝女郎 | 拉克絲 | 1350 | | 4 | 發(fā)條魔靈 | 奧莉安娜 | 6300 | | 5 | 至高之拳 | 李青 | 6300 | | 6 | 無(wú)極劍圣 | 易 | 450 | | 7 | 疾風(fēng)劍豪 | 亞索 | 6300 | +----+--------------+--------------+-------+ 7 rows in set (0.00 sec)

下面是一個(gè)出現(xiàn)幻讀情況的示例流程：

可以看到，session A 里執(zhí)行了三次查詢(xún)，分別是 Q1、Q2 和 Q3。它們的 SQL 語(yǔ)句相同，都是 select * from LOL where price=450 for update。這個(gè)語(yǔ)句的意思你應(yīng)該很清楚了，查所有 price=450 的行，而且使用的是當(dāng)前讀，并且加上寫(xiě)鎖?，F(xiàn)在，我們來(lái)看一下這三條 SQL 語(yǔ)句，分別會(huì)返回什么結(jié)果。

1.Q1 只返回 “無(wú)極劍圣” 這一行；

2.在 T2 時(shí)刻，session B 把 “疾風(fēng)劍豪” 這一行的 price 值改成了 450，因此 T3 時(shí)刻 Q2 查出來(lái)的是 “無(wú)極劍圣” 和 “疾風(fēng)劍豪” 這兩行；

3.在 T4 時(shí)刻，session C 又插入一行 (10,‘雪人騎士',‘努努',‘450')，因此 T5 時(shí)刻 Q3 查出來(lái) price = 450 的是"無(wú)極劍圣" 、“疾風(fēng)劍豪” 和 “雪人騎士” 這三行。

其中，Q3 讀到 (10,‘雪人騎士',450) 這一行的現(xiàn)象，被稱(chēng)為“幻讀”。也就是說(shuō)，幻讀指的是一個(gè)事務(wù)在前后兩次查詢(xún)同一個(gè)范圍的時(shí)候，后一次查詢(xún)看到了前一次查詢(xún)沒(méi)有看到的行。

一、原理解讀

那么幻讀能僅通過(guò)行鎖解決么？答案是否定的，如上面示例，首先說(shuō)明一下，select xx for update（當(dāng)前讀）是將所有條件涉及到的（符合where條件）行加上行鎖。但是，就算我在select xx for update 事務(wù)開(kāi)啟時(shí)將所有的行都加上行鎖。那么也鎖不住Session C新增的行，因?yàn)樵谖医o數(shù)據(jù)加鎖的時(shí)刻，壓根就還沒(méi)有新增的那行，自然也不會(huì)給新增行加上鎖。

所以要解決幻讀，就必須得解決新增行的問(wèn)題。

現(xiàn)在你應(yīng)該明白了，產(chǎn)生幻讀的原因是：行鎖只能鎖住行，但是新插入記錄這個(gè)動(dòng)作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問(wèn)題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)。顧名思義，間隙鎖，鎖的就是兩個(gè)值之間的空隙。比如文章開(kāi)頭的表 LOL，初始化插入了 7 個(gè)記錄，這就產(chǎn)生了 8 個(gè)間隙。

二、next-key lock

這樣，當(dāng)你執(zhí)行 select * from LOL where hero_title = ‘疾風(fēng)劍豪' for update 的時(shí)候，就不止是給數(shù)據(jù)庫(kù)中已有的 7 個(gè)記錄加上了行鎖，還同時(shí)加了 8 個(gè)間隙鎖。這樣就確保了無(wú)法再插入新的記錄，也就是Session C在T4新增(10,‘雪人騎士',‘努努',‘450') 行時(shí)，由于ID大于7，被間隙鎖（7，+∞）鎖住。

在一行行掃描的過(guò)程中，不僅將給行加上了行鎖，還給行兩邊的空隙，也加上了間隙鎖。MySQL將行鎖 + 間隙鎖組合統(tǒng)稱(chēng)為 next-key lock，通過(guò) next-key lock 解決了幻讀問(wèn)題。

注意：

next-key lock的確是解決了幻讀問(wèn)題，但是next-key lock在并發(fā)情況下也經(jīng)常會(huì)造成死鎖。死鎖檢測(cè)和處理也會(huì)花費(fèi)時(shí)間，一定程度上影響到并發(fā)量。

面試題3：說(shuō)一下MySQL中你都知道哪些鎖？

正經(jīng)回答：

1.按鎖粒度從大到小分類(lèi)：表鎖，頁(yè)鎖和行鎖；以及特殊場(chǎng)景下使用的全局鎖

2.如果按鎖級(jí)別分類(lèi)則有：共享（讀）鎖、排他（寫(xiě)）鎖、意向共享（讀）鎖、意向排他（寫(xiě)）鎖；

3.以及Innodb引擎為解決幻讀等并發(fā)場(chǎng)景下事務(wù)存在的數(shù)據(jù)問(wèn)題，引入的Record Lock（行記錄鎖）、Gap Lock（間隙鎖）、Next-key Lock（Record Lock + Gap Lock結(jié)合）等；

4.還有就是我們面向編程的兩種鎖思想：悲觀(guān)鎖、樂(lè)觀(guān)鎖。

深入追問(wèn)： 追問(wèn)1：那你來(lái)談一談你對(duì)表鎖、行鎖的理解吧。

表鎖

表級(jí)別的鎖定是MySQL各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來(lái)的系統(tǒng)負(fù)面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問(wèn)題。

當(dāng)然，鎖定顆粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，大大降低并發(fā)度。

使用表級(jí)鎖定的主要是MyISAM，MEMORY，CSV等一些非事務(wù)性存儲(chǔ)引擎。

行鎖

與表鎖正相反，行鎖最大的特點(diǎn)就是鎖定對(duì)象的顆粒度很小，也是目前各大數(shù)據(jù)庫(kù)管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力從而提高系統(tǒng)的整體性能。

雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢(shì)，但是行級(jí)鎖定也因此帶來(lái)了不少弊端。由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來(lái)的消耗自然也就更大了。此外，行級(jí)鎖定也最容易發(fā)生死鎖。

使用行級(jí)鎖定的主要是InnoDB存儲(chǔ)引擎。

• 適用場(chǎng)景：從鎖的角度來(lái)說(shuō)，表級(jí)鎖更適合于以查詢(xún)?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新數(shù)據(jù)的情況，同時(shí)又有并發(fā)查詢(xún)的應(yīng)用場(chǎng)景。

頁(yè)鎖

除了表鎖、行鎖外，MySQL還有一種相對(duì)偏中性的頁(yè)級(jí)鎖，頁(yè)鎖是MySQL中比較獨(dú)特的一種鎖定級(jí)別，在其他數(shù)據(jù)庫(kù)管理軟件中也并不是太常見(jiàn)。頁(yè)級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，所以獲取鎖定所需要的資源開(kāi)銷(xiāo)，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外，頁(yè)級(jí)鎖定和行級(jí)鎖定一樣，會(huì)發(fā)生死鎖。

使用頁(yè)級(jí)鎖定的主要是BerkeleyDB存儲(chǔ)引擎。

追問(wèn)2：那全局鎖是什么時(shí)候用的呢？

首先全局鎖，是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖。使用場(chǎng)景一般在全庫(kù)邏輯備份時(shí)。

MySQL提供加全局讀鎖的命令：Flush tables with read lock (FTWRL)

這個(gè)命令可以使整個(gè)庫(kù)處于只讀狀態(tài)。使用該命令之后，數(shù)據(jù)更新語(yǔ)句、數(shù)據(jù)定義語(yǔ)句和更新類(lèi)事務(wù)的提交語(yǔ)句等修改數(shù)據(jù)庫(kù)的操作都會(huì)被阻塞。

風(fēng)險(xiǎn)：

1.如果在主庫(kù)備份，在備份期間不能更新，業(yè)務(wù)停擺

2.如果在從庫(kù)備份，備份期間不能執(zhí)行主庫(kù)同步的binlog，導(dǎo)致主從延遲同步

還有一種鎖全局的方式：set global readonly=true ，相當(dāng)于將整個(gè)庫(kù)設(shè)置成只讀狀態(tài)，但這種修改global配置量級(jí)較重，和全局鎖不同的是：如果執(zhí)行Flush tables with read lock 命令后，如果客戶(hù)端發(fā)生異常斷開(kāi)，那么MySQL會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫(kù)回到可以正常更新的狀態(tài)。但將庫(kù)設(shè)置為readonly后，客戶(hù)端發(fā)生異常斷開(kāi)，數(shù)據(jù)庫(kù)依舊會(huì)保持readonly狀態(tài)，會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間處于不可寫(xiě)狀態(tài)，試想一下微信只能看，不能打字~~

追問(wèn)2：那你再說(shuō)一下按鎖級(jí)別劃分的那幾種鎖的使用場(chǎng)景和理解吧？

MySQL基于鎖級(jí)別又分為：共享（讀）鎖、排他（寫(xiě)）鎖、意向共享（讀）鎖、意向排他（寫(xiě)）鎖

對(duì)于共享（讀）鎖、排他（寫(xiě)）鎖，比如咱們住酒店，入住前顧客都是有權(quán)看房的，只看不住想白嫖都是可以的，前臺(tái)小姐姐會(huì)把門(mén)給你打開(kāi)。當(dāng)然，也允許不同的顧客一起看（共享 讀），

看房時(shí)房間相當(dāng)于公共場(chǎng)所，小姐姐囑咐不能亂涂亂畫(huà)，也不能偷喝免費(fèi)的礦泉水。。如果你覺(jué)得不錯(cuò)，偷偷跑到前臺(tái)要定這間房，交錢(qián)后會(huì)給你這個(gè)房間的鑰匙并將房間狀態(tài)改為已入住，不再允許其他人看房（排他 寫(xiě)）。

對(duì)了，當(dāng)辦理入住時(shí)前臺(tái)小姐姐也會(huì)通知看房的殺馬特小伙子說(shuō)這間房已經(jīng)有人定了??！等看房的殺馬特小伙兒罵罵咧咧出門(mén)后，看到滿(mǎn)頭大汗的你，鄙夷著咽了一口口水，咳tui！然后你鎖上門(mén)哼著歌兒，開(kāi)始干那些見(jiàn)不得人的事兒~~直到你退房前，其他人無(wú)法在看你的房。

可見(jiàn)，讀鎖是可以并發(fā)獲取的（共享的），而寫(xiě)鎖只能給一個(gè)事務(wù)處理（排他的）。當(dāng)你想獲取寫(xiě)鎖時(shí)，需要等待之前的讀鎖都釋放后方可加寫(xiě)鎖；而當(dāng)你想獲取讀鎖時(shí)，只要數(shù)據(jù)沒(méi)有被寫(xiě)鎖鎖住，你都可以獲取到讀鎖，然后去看房。

另外還有意向讀\寫(xiě)鎖，嚴(yán)格來(lái)說(shuō)他們并不是一種鎖，而是存放表中所有行鎖的信息。就像我們?cè)诰频?，?dāng)我們預(yù)定一個(gè)房間時(shí)，就對(duì)該行（房間）添加 意向?qū)戞i，但是同時(shí)會(huì)在酒店的前臺(tái)對(duì)該行（房間）做一個(gè)信息登記（旅客姓名、男女、住多長(zhǎng)時(shí)間、家里幾頭牛等）。大家可以把意向鎖當(dāng)成這個(gè)酒店前臺(tái)，它并不是真正意義上的鎖（鑰匙），它維護(hù)表中每行的加鎖信息，是共用的。后續(xù)的旅客通過(guò)酒店前臺(tái)來(lái)看哪個(gè)房間是可選的，那么，如果沒(méi)有意圖鎖，會(huì)出現(xiàn)什么情況呢？假設(shè)我要住房間，那么我每次都要到每一個(gè)房間看看這個(gè)房間有沒(méi)有住人，顯然這樣做的效率是很低下的。殺馬特小伙兒表示支持！

讀寫(xiě)鎖、意向鎖的兼容性如下所示；

我們?cè)倩氐組ySQL原理上講

1、共享（讀）鎖（Share Lock）

共享鎖，又叫讀鎖，是讀取操作（SELECT）時(shí)創(chuàng)建的鎖。其他用戶(hù)可以并發(fā)讀取數(shù)據(jù)，但在讀鎖未釋放前，也就是查詢(xún)事務(wù)結(jié)束前，任何事務(wù)都不能對(duì)數(shù)據(jù)進(jìn)行修改（獲取數(shù)據(jù)上的寫(xiě)鎖），直到已釋放所有讀鎖。

如果事務(wù)A對(duì)數(shù)據(jù)B（1024房）加上讀鎖后，則其他事務(wù)只能對(duì)數(shù)據(jù)B上加讀鎖，不能加寫(xiě)鎖。獲得讀鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)。

SQL顯示加鎖寫(xiě)法:

SELECT … LOCK IN SHARE MODE;

在查詢(xún)語(yǔ)句后面增加LOCK IN SHARE MODE，MySQL就會(huì)對(duì)查詢(xún)結(jié)果中的每行都加讀鎖，當(dāng)沒(méi)有其他線(xiàn)程對(duì)查詢(xún)結(jié)果集中的任何一行使用寫(xiě)鎖時(shí)，可以成功申請(qǐng)讀鎖，否則會(huì)被阻塞。其他線(xiàn)程也可以讀取使用了讀鎖的表，而且這些線(xiàn)程讀取的是同一個(gè)版本的數(shù)據(jù)。

2、排他（寫(xiě)）鎖（Exclusive Lock）

排他鎖又稱(chēng)寫(xiě)鎖、獨(dú)占鎖，如果事務(wù)A對(duì)數(shù)據(jù)B加上寫(xiě)鎖后，則其他事務(wù)不能再對(duì)數(shù)據(jù)B加任何類(lèi)型的鎖。獲得寫(xiě)鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。

SQL顯示加鎖寫(xiě)法:

SELECT … FOR UPDATE;

在查詢(xún)語(yǔ)句后面增加FOR UPDATE，MySQL 就會(huì)對(duì)查詢(xún)結(jié)果中的每行都加寫(xiě)鎖，當(dāng)沒(méi)有其他線(xiàn)程對(duì)查詢(xún)結(jié)果集中的任何一行使用寫(xiě)鎖時(shí)，可以成功申請(qǐng)寫(xiě)鎖，否則會(huì)被阻塞。另外成功申請(qǐng)寫(xiě)鎖后，也要先等待該事務(wù)前的讀鎖釋放才能操作。

3、意向鎖（Intention Lock）

意向鎖屬于表級(jí)鎖，其設(shè)計(jì)目的主要是為了在一個(gè)事務(wù)中揭示下一行將要被請(qǐng)求鎖的類(lèi)型。InnoDB 中的兩個(gè)表鎖：

• 意向共享鎖（IS）：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說(shuō)一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖；
• 意向排他鎖（IX）：類(lèi)似上面，表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖，說(shuō)明事務(wù)在一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

意向鎖是 InnoDB 自動(dòng)加的，不需要用戶(hù)干預(yù)。

再?gòu)?qiáng)調(diào)一下，對(duì)于INSERT、UPDATE和DELETE，InnoDB 會(huì)自動(dòng)給涉及的數(shù)據(jù)加排他鎖；對(duì)于一般的SELECT語(yǔ)句，InnoDB 不會(huì)加任何鎖，事務(wù)可以通過(guò)以下語(yǔ)句顯式加共享鎖或排他鎖。

共享鎖：SELECT … LOCK IN SHARE MODE; 排他鎖：SELECT … FOR UPDATE;

總結(jié)

本篇文章就到這里了，希望能給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!

最新評(píng)論