Java面試題沖刺第十二天–數據庫(2)

面試題1：先說一下什麼是MySQL事務吧

正經回答：

簡單說，事務就是一組原子性的SQL執行單元。如果數據庫引擎能夠成功地對數據庫應 用該組査詢的全部語句，那麼就執行該組SQL。如果其中有任何一條語句因為崩潰或其 他原因無法執行，那麼所有的語句都不會執行。要麼全部執行成功（commit），要麼全部執行失敗（rollback）。

這裡引用銀行轉賬的例子，假設銀行的數據庫有兩張表：信用卡(credit)表和儲蓄(savings)表。用戶陳哈哈要把信用卡裡最後100塊錢額度轉到他 的儲蓄賬戶用來吃飯，那麼需要至少三個步驟：

• 檢査信用卡餘額是否髙於100塊錢。
• 從信用卡賬戶餘額中減去100塊錢。
• 在儲蓄賬戶餘額中增加100塊錢。

上述三個步驟必須在同一個事務中執行，任何一個SQL失敗，則必須回滾所有的SQL。這裡用START TRANSACTION語句開啟事務，要麼使用COMMIT提交事務將修改的數據持久保留，要麼使用ROLLBACK銷所有的修改。事務SQL的樣本如下：

START TRANSACTION;
-- 檢查信用卡賬戶額度
SELECT balance FROM credit WHERE customer_id = 'chenhh';
-- 信用卡表扣錢
UPDATE credit SET balance = balance - 100.00 WHERE customer_id = 'chenhh';
-- 儲蓄表加錢
UPDATE savings SET balance = balance + 100.00 WHERE customer_id = 'chenhh';
COMMIT;

試想一下，如果執行到第四條語句時服務器崩潰瞭，會發生什麼？廢話，我被坑瞭100塊錢，中午隻能餓肚子！再假如，在執行到第三條語句和第四 條語句之間時，同一時間，另外一個進程，來自商場結賬的女朋友，也要信用卡賬戶的100塊，那麼結果可能就是銀行在不知道這個邏輯的情況下白白給瞭陳哈哈女朋友100塊錢？

深入追問： 追問1：說一下你對ACID四大特性的理解

該問題來自“MySQL江湖路”專欄中的博文：《數據庫ACID四大特性到底為瞭啥，一文帶你看通透》

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

原子性(Atomicity)

單個事務，為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部commit成功，要麼全部失敗rollback，對於一個事務來說，不可能隻執行其中的一部分SQL操作，這就是事務的原子性。

一致性(Consistency)

數據庫總是從一個一致性的狀態轉換到另外一個一致性的狀態。在前面的例子中， 一致性確保瞭，即使在執行第三、四條語句之間時系統崩潰，信用卡賬戶也不會損 失100塊，因為事務最終沒有提交，所以事務中所做的修改也不會保存到數據庫中，保證數據一致性。

隔離性(Isolation)

通常來說，一個事務所做的修改在最終提交以前，對其他事務是不可見的。在前面 的例子中，當執行完第三條語句、第四條語句還未開始時，此時有另外一個賬戶查詢餘額SQL開始運行，則其看到的信用卡賬戶的餘額並沒有被減去100元。後面我們討論隔離級別(Isolation level)的時候，會發現為什麼我們要說事務通常來說是不可見的。

持久性(Durability)

一旦事務提交，則其所做的修改就會永久保存到數據庫中。此時即使系統崩潰，修改的數據也不會丟失。

事務的ACID特性可以確保銀行不會弄丟你的錢。而在應用邏輯中，要實現這一點非常難, 甚至可以說是不可能完成的任務。一個兼容ACID的數據庫系統，需要做很多復雜但可能用戶並沒有覺察到的工作，才能確保ACID的實現。

追問2：可以從原理上聊一下ACID具體是怎麼實現的麼？

對MySQL來說，邏輯備份日志（binlog）、重做日志（redolog）、回滾日志（undolog）、鎖技術 + MVCC就是MySQL實現事務的基礎。

• 原子性：通過undolog來實現。
• 持久性：通過binlog、redolog來實現。
• 隔離性：通過(讀寫鎖+MVCC)來實現。
• 一致性：MySQL通過原子性，持久性，隔離性最終實現（或者說定義）數據一致性。

1、原子性原理

事務通常是以BEGIN TRANSACTION 開始，以 COMMIT 或 ROLLBACK 結束。

• COMMIT 表示提交，即提交事務的所有操作並持久化到數據庫中。
• ROLLBACK表示回滾，即在事務中運行的過程中發生瞭某種故障，事務不能繼續執行，系統將事務中對數據庫所有已完成的操作全部撤銷，回滾到事務開始時的狀態，這裡的操作指對數據庫的更新操作，已執行的查詢操作不用管。這時候也就需要用到 undolog 來進行回滾。

undolog：

• 每條數據變更（INSERT/UPDATE/DELETE/REPLACE）等操作都會生成一條undolog記錄，在SQL執行前先於數據持久化到磁盤。
• 當事務需要回滾時，MySQL會根據回滾日志對事務中已執行的SQL做逆向操作，比如 DELETE 掉一行數據的逆向操作就是再把這行數據 INSERT回去，其他操作同理。

2、持久性原理

先瞭解一下MySQL的數據存儲機制，MySQL的表數據是存放在磁盤上的，因此想要存取的時候都要經歷磁盤 IO,然而即使是使用 SSD 磁盤 IO 也是非常消耗性能的。為此，為瞭提升性能 InnoDB 提供瞭緩沖池(Buffer Pool)，Buffer Pool 中包含瞭磁盤數據頁的映射，可以當做緩存來使用：

• 讀數據：會首先從緩沖池中讀取，如果緩沖池中沒有，則從磁盤讀取在放入緩沖池；
• 寫數據：會首先寫入緩沖池，緩沖池中的數據會定期同步到磁盤中；

我們知道，MySQL表數據是持久化到磁盤中的，但如果所有操作都去操作磁盤，等並發上來瞭，那處理速度誰都吃不消，因此引入瞭緩沖池(Buffer Pool)的概念，Buffer Pool 中包含瞭磁盤中部分數據頁的映射，可以當做緩存來用；這樣當修改表數據時，我們把操作記錄先寫到Buffer Pool中，並標記事務已完成，等MySQL空閑時，再把更新操作持久化到磁盤裡（你可能會問，到底什麼時候執行持久化呢？1、MySQL線程低於高水位；2、當有其他查詢、更新語句操作該數據頁時），從而大大緩解瞭MySQL並發壓力。

但是它也帶來瞭新的問題，當MySQL系統宕機，斷電時Buffer Pool數據不就丟瞭？

因為我們的數據已經提交瞭，但此時是在緩沖池裡頭，還沒來得及在磁盤持久化，所以我們急需一種機制需要存一下已提交事務的數據，為恢復數據使用。

於是 redo log + binlog的經典組合就登場瞭，這裡不在擴展贅述。

3、隔離性原理

隔離性是事務ACID特性裡最復雜的一個。在SQL標準裡定義瞭四種隔離級別，每一種級別都規定一個事務中的修改，哪些是事務之間可見的，哪些是不可見的。

級別越低的隔離級別可以執行越高的並發，但同時實現復雜度以及開銷也越大。

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

搞懂瞭隔離級別以及實現原理其實就可以理解ACID裡的隔離性瞭。前面說過原子性，隔離性，持久性的目的都是為瞭要做到一致性，但隔離型跟其他兩個有所區別，原子性和持久性是為瞭要實現數據的正確、可用，比如要做到宕機後的恢復、事務的回滾等，保證數據是正確可用的！

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

隔離性要管理的是：多個並發讀寫請求（事務）過來時的執行順序。像交警在馬路口兒指揮交通一樣，當並發處理多個DML更新操作時，如何讓事務操作他該看到的數據，出現多個事務處理同一條數據時，讓事務該排隊的排隊，別插隊搗亂，保證數據和事務的相對隔離，這就是隔離性要幹的事兒。

所以，從隔離性的實現原理上，我們可以看出這是一場數據的可靠性與性能之間的權衡。

4、一致性原理

一致性，我們要保障的是數據一致性，數據庫中的增刪改操作，使數據庫不斷從一個一致性的狀態轉移到另一個一致性的狀態。

事務該回滾的回滾，該提交的提交，提交後該持久化磁盤的持久化磁盤，該寫緩沖池的寫緩沖池+寫日志；對於數據可見性，通過四種隔離級別進行控制，使得庫表中的有效數據范圍可控，保證業務數據的正確性的前提下，進而提高並發程度，支撐服務高QPS的穩定運行，保證數據的一致性，這就是咱們叨叨叨說的清楚想不明白的數據庫ACID四大特性。

面試題2：並發場景下事務會存在哪些數據問題？ 正經回答：

並發場景下MySQL事務可能會出現臟讀、幻讀、不可重復讀問題；

臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取瞭同一份數據，由於某些原因，前一個RollBack瞭操作，則後一個事務所讀取的數據就會是不正確的。

不可重復讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入瞭一個事務更新瞭原有的數據。

幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢瞭幾列(Row)數據，而另一個事務卻在此時插入瞭新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。

深入追問：

追問1：那Innodb是如何解決幻讀問題的呢？

先說結論，MySQL 存儲引擎 InnoDB 在可重復讀（RR）隔離級別下是解決瞭幻讀問題的。

方法是通過next-key lock在當前讀事務開啟時，1.給涉及到的行加寫鎖（行鎖）防止寫操作；2.給涉及到的行兩端加間隙鎖(Gap Lock)防止新增行寫入；從而解決瞭幻讀問題。

幻讀出現的場景： 1.幻讀出現在可重復讀（RR）隔離級別下，普通的SELECT查詢就是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在“當前讀”下才會出現。（當前讀會生成行鎖，但行鎖隻能鎖定存在的行，針對新插入的操作沒有限定）

2.上面 session B 的修改結果，被 session A 之後的 select 語句用“當前讀”看到，不能稱為幻讀。幻讀僅專指“新插入的行”。

3.因為這三個查詢都是加瞭 for update，都是當前讀。而當前讀的規則，就是要能讀到所有已經提交的記錄的最新值。並且，session B 和 sessionC 的兩條語句，執行後就會提交，所以 Q2 和 Q3 就是應該看到這兩個事務的操作效果，而且也看到瞭，這跟事務的可見性規則並不矛盾。

幻讀場景實例：

測試表數據如下：

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

下面是一個出現幻讀情況的示例流程：

可以看到，session A 裡執行瞭三次查詢，分別是 Q1、Q2 和 Q3。它們的 SQL 語句相同，都是 select * from LOL where price=450 for update。這個語句的意思你應該很清楚瞭，查所有 price=450 的行，而且使用的是當前讀，並且加上寫鎖。現在，我們來看一下這三條 SQL 語句，分別會返回什麼結果。

1.Q1 隻返回 “無極劍聖” 這一行；

2.在 T2 時刻，session B 把 “疾風劍豪” 這一行的 price 值改成瞭 450，因此 T3 時刻 Q2 查出來的是 “無極劍聖” 和 “疾風劍豪” 這兩行；

3.在 T4 時刻，session C 又插入一行 (10,‘雪人騎士’,‘努努’,‘450′)，因此 T5 時刻 Q3 查出來 price = 450 的是”無極劍聖” 、“疾風劍豪” 和 “雪人騎士” 這三行。

其中，Q3 讀到 (10,‘雪人騎士’,450) 這一行的現象，被稱為“幻讀”。也就是說，幻讀指的是一個事務在前後兩次查詢同一個范圍的時候，後一次查詢看到瞭前一次查詢沒有看到的行。

一、原理解讀

那麼幻讀能僅通過行鎖解決麼？答案是否定的，如上面示例，首先說明一下，select xx for update（當前讀）是將所有條件涉及到的（符合where條件）行加上行鎖。但是，就算我在select xx for update 事務開啟時將所有的行都加上行鎖。那麼也鎖不住Session C新增的行，因為在我給數據加鎖的時刻，壓根就還沒有新增的那行，自然也不會給新增行加上鎖。

所以要解決幻讀，就必須得解決新增行的問題。

現在你應該明白瞭，產生幻讀的原因是：行鎖隻能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。因此，為瞭解決幻讀問題，InnoDB 隻好引入新的鎖，也就是間隙鎖 (Gap Lock)。顧名思義，間隙鎖，鎖的就是兩個值之間的空隙。比如文章開頭的表 LOL，初始化插入瞭 7 個記錄，這就產生瞭 8 個間隙。

二、next-key lock

這樣，當你執行 select * from LOL where hero_title = ‘疾風劍豪’ for update 的時候，就不止是給數據庫中已有的 7 個記錄加上瞭行鎖，還同時加瞭 8 個間隙鎖。這樣就確保瞭無法再插入新的記錄，也就是Session C在T4新增(10,‘雪人騎士’,‘努努’,‘450′) 行時，由於ID大於7，被間隙鎖（7，+∞）鎖住。

在一行行掃描的過程中，不僅將給行加上瞭行鎖，還給行兩邊的空隙，也加上瞭間隙鎖。MySQL將行鎖 + 間隙鎖組合統稱為 next-key lock，通過 next-key lock 解決瞭幻讀問題。

註意：

next-key lock的確是解決瞭幻讀問題，但是next-key lock在並發情況下也經常會造成死鎖。死鎖檢測和處理也會花費時間，一定程度上影響到並發量。

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

正經回答：

1.按鎖粒度從大到小分類：表鎖，頁鎖和行鎖；以及特殊場景下使用的全局鎖

2.如果按鎖級別分類則有：共享（讀）鎖、排他（寫）鎖、意向共享（讀）鎖、意向排他（寫）鎖；

3.以及Innodb引擎為解決幻讀等並發場景下事務存在的數據問題，引入的Record Lock（行記錄鎖）、Gap Lock（間隙鎖）、Next-key Lock（Record Lock + Gap Lock結合）等；

4.還有就是我們面向編程的兩種鎖思想：悲觀鎖、樂觀鎖。

深入追問： 追問1：那你來談一談你對表鎖、行鎖的理解吧。

表鎖

表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以獲取鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，大大降低並發度。

使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性存儲引擎。

行鎖

與表鎖正相反，行鎖最大的特點就是鎖定對象的顆粒度很小，也是目前各大數據庫管理軟件所實現的鎖定顆粒度最小的。由於鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的並發處理能力從而提高系統的整體性能。

雖然能夠在並發處理能力上面有較大的優勢，但是行級鎖定也因此帶來瞭不少弊端。由於鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大瞭。此外，行級鎖定也最容易發生死鎖。

使用行級鎖定的主要是InnoDB存儲引擎。

• 適用場景：從鎖的角度來說，表級鎖更適合於以查詢為主，隻有少量按索引條件更新數據的應用，如Web應用；而行級鎖則更適合於有大量按索引條件並發更新數據的情況，同時又有並發查詢的應用場景。

頁鎖

除瞭表鎖、行鎖外，MySQL還有一種相對偏中性的頁級鎖，頁鎖是MySQL中比較獨特的一種鎖定級別，在其他數據庫管理軟件中也並不是太常見。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。

使用頁級鎖定的主要是BerkeleyDB存儲引擎。

追問2：那全局鎖是什麼時候用的呢？

首先全局鎖，是對整個數據庫實例加鎖。使用場景一般在全庫邏輯備份時。

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

這個命令可以使整個庫處於隻讀狀態。使用該命令之後，數據更新語句、數據定義語句和更新類事務的提交語句等修改數據庫的操作都會被阻塞。

風險：

1.如果在主庫備份，在備份期間不能更新，業務停擺

2.如果在從庫備份，備份期間不能執行主庫同步的binlog，導致主從延遲同步

還有一種鎖全局的方式：set global readonly=true ，相當於將整個庫設置成隻讀狀態，但這種修改global配置量級較重，和全局鎖不同的是：如果執行Flush tables with read lock 命令後，如果客戶端發生異常斷開，那麼MySQL會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。但將庫設置為readonly後，客戶端發生異常斷開，數據庫依舊會保持readonly狀態，會導致整個庫長時間處於不可寫狀態，試想一下微信隻能看，不能打字~~

追問2：那你再說一下按鎖級別劃分的那幾種鎖的使用場景和理解吧？

MySQL基於鎖級別又分為：共享（讀）鎖、排他（寫）鎖、意向共享（讀）鎖、意向排他（寫）鎖

對於共享（讀）鎖、排他（寫）鎖，比如咱們住酒店，入住前顧客都是有權看房的，隻看不住想白嫖都是可以的，前臺小姐姐會把門給你打開。當然，也允許不同的顧客一起看（共享 讀），

看房時房間相當於公共場所，小姐姐囑咐不能亂塗亂畫，也不能偷喝免費的礦泉水。。如果你覺得不錯，偷偷跑到前臺要定這間房，交錢後會給你這個房間的鑰匙並將房間狀態改為已入住，不再允許其他人看房（排他 寫）。

對瞭，當辦理入住時前臺小姐姐也會通知看房的殺馬特小夥子說這間房已經有人定瞭！！等看房的殺馬特小夥兒罵罵咧咧出門後，看到滿頭大汗的你，鄙夷著咽瞭一口口水，咳tui！然後你鎖上門哼著歌兒，開始幹那些見不得人的事兒~~直到你退房前，其他人無法在看你的房。

可見，讀鎖是可以並發獲取的（共享的），而寫鎖隻能給一個事務處理（排他的）。當你想獲取寫鎖時，需要等待之前的讀鎖都釋放後方可加寫鎖；而當你想獲取讀鎖時，隻要數據沒有被寫鎖鎖住，你都可以獲取到讀鎖，然後去看房。

另外還有意向讀\寫鎖，嚴格來說他們並不是一種鎖，而是存放表中所有行鎖的信息。就像我們在酒店，當我們預定一個房間時，就對該行（房間）添加 意向寫鎖，但是同時會在酒店的前臺對該行（房間）做一個信息登記（旅客姓名、男女、住多長時間、傢裡幾頭牛等）。大傢可以把意向鎖當成這個酒店前臺，它並不是真正意義上的鎖（鑰匙），它維護表中每行的加鎖信息，是共用的。後續的旅客通過酒店前臺來看哪個房間是可選的，那麼，如果沒有意圖鎖，會出現什麼情況呢？假設我要住房間，那麼我每次都要到每一個房間看看這個房間有沒有住人，顯然這樣做的效率是很低下的。殺馬特小夥兒表示支持！

讀寫鎖、意向鎖的兼容性如下所示；

我們再回到MySQL原理上講

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

共享鎖，又叫讀鎖，是讀取操作（SELECT）時創建的鎖。其他用戶可以並發讀取數據，但在讀鎖未釋放前，也就是查詢事務結束前，任何事務都不能對數據進行修改（獲取數據上的寫鎖），直到已釋放所有讀鎖。

如果事務A對數據B（1024房）加上讀鎖後，則其他事務隻能對數據B上加讀鎖，不能加寫鎖。獲得讀鎖的事務隻能讀數據，不能修改數據。

SQL顯示加鎖寫法:

SELECT … LOCK IN SHARE MODE;

在查詢語句後面增加LOCK IN SHARE MODE，MySQL就會對查詢結果中的每行都加讀鎖，當沒有其他線程對查詢結果集中的任何一行使用寫鎖時，可以成功申請讀鎖，否則會被阻塞。其他線程也可以讀取使用瞭讀鎖的表，而且這些線程讀取的是同一個版本的數據。

2、排他（寫）鎖（Exclusive Lock）

排他鎖又稱寫鎖、獨占鎖，如果事務A對數據B加上寫鎖後，則其他事務不能再對數據B加任何類型的鎖。獲得寫鎖的事務既能讀數據，又能修改數據。

SQL顯示加鎖寫法:

SELECT … FOR UPDATE;

在查詢語句後面增加FOR UPDATE，MySQL 就會對查詢結果中的每行都加寫鎖，當沒有其他線程對查詢結果集中的任何一行使用寫鎖時，可以成功申請寫鎖，否則會被阻塞。另外成功申請寫鎖後，也要先等待該事務前的讀鎖釋放才能操作。

3、意向鎖（Intention Lock）

意向鎖屬於表級鎖，其設計目的主要是為瞭在一個事務中揭示下一行將要被請求鎖的類型。InnoDB 中的兩個表鎖：

• 意向共享鎖（IS）：表示事務準備給數據行加入共享鎖，也就是說一個數據行加共享鎖前必須先取得該表的IS鎖；
• 意向排他鎖（IX）：類似上面，表示事務準備給數據行加入排他鎖，說明事務在一個數據行加排他鎖前必須先取得該表的IX鎖。

意向鎖是 InnoDB 自動加的，不需要用戶幹預。

再強調一下，對於INSERT、UPDATE和DELETE，InnoDB 會自動給涉及的數據加排他鎖；對於一般的SELECT語句，InnoDB 不會加任何鎖，事務可以通過以下語句顯式加共享鎖或排他鎖。

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

總結

本篇文章就到這裡瞭，希望能給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容!

推薦閱讀：

• MySQL日志專項之redo log和undo log介紹
• 詳解MySQL中事務隔離級別的實現原理
• 淺析MySQL如何實現事務隔離
• MySQL 主從同步,事務回滾的實現原理
• MySQL事務(transaction)看這篇就足夠瞭