MySQL InnoDB如何保證事務(wù)特性示例詳解

前言

如果有人問(wèn)你“數(shù)據(jù)庫(kù)事務(wù)有哪些特性”？你可能會(huì)很快回答出原子性、一致性、隔離性、持久性即ACID特性。那么你知道InnoDB如何保證這些事務(wù)特性的嗎？如果知道的話這篇文章就可以直接跳過(guò)不看啦(#^.^#)

先說(shuō)結(jié)論：

• redo log重做日志用來(lái)保證事務(wù)的持久性
• undo log回滾日志保證事務(wù)的原子性
• undo log+redo log保證事務(wù)的一致性
• 鎖（共享、排他）用來(lái)保證事務(wù)的隔離性

重做日志 redo log

重做日志 redo log 分為兩部分：一部分是內(nèi)存中的重做日志緩沖（redo log buffer），是易丟失的；二部分是重做日志文件（redo log file），是持久的。InnoDB通過(guò)Force Log at Commit機(jī)制來(lái)實(shí)現(xiàn)持久性，當(dāng)commit時(shí)，必須先將事務(wù)的所有日志寫到重做日志文件進(jìn)行持久化，待commit操作完成才算完成。

InnoDB在下面情況下會(huì)將重做日志緩沖的內(nèi)容寫入重做日志文件：

• master thread 每一秒將重做日志緩沖刷新到重做日志文件;
• 每個(gè)事務(wù)提交時(shí)
• 當(dāng)重做日志緩沖池剩余空間小于1/2時(shí)

為了確保每次日志都寫入重做日志文件，在每次將日志緩沖寫入重做日志文件后，InnoDB存儲(chǔ)引擎都需要調(diào)用一次fsync（刷盤）操作。但這也不是絕對(duì)的。用戶可以通過(guò)修改innodb_flush_log_at_trx_commoit參數(shù)來(lái)控制重做日志刷新到磁盤的策略，這個(gè)可以作為大量事務(wù)提交時(shí)的優(yōu)化點(diǎn)。

• 1參數(shù)默認(rèn)值，表示事務(wù)提交時(shí)必須調(diào)用一次fsync操作。
• 0表示事務(wù)提交時(shí)，重做日志緩存并不立即寫入重做日志文件，而是隨著Master Thread的間隔進(jìn)行fsync操作。
• 2表示事務(wù)提交時(shí)將重做日志寫入重做日志文件，但僅寫入文件系統(tǒng)的緩存中，不進(jìn)行fsync操作。
  fsync的效率取決于磁盤的性能，因此磁盤的性能決定了事務(wù)提交的性能，也就是數(shù)據(jù)庫(kù)的性能。所以如果有人問(wèn)你如何優(yōu)化Mysql數(shù)據(jù)庫(kù)的時(shí)候別忘了有硬件這一條，讓他們提升硬盤配置，換SSD固態(tài)硬盤
  重做日志都是以512字節(jié)進(jìn)行存儲(chǔ)的，稱之為重做日志塊，與磁盤扇區(qū)大小一致，這意味著重做日志的寫入可以保證原子性，不需要doublewrite技術(shù)。它有以下3個(gè)特性：
• 重做日志是在InnoDB層產(chǎn)生的
• 重做日志是物理格式日志，記錄的是對(duì)每個(gè)頁(yè)的修改
• 重做日志在事務(wù)進(jìn)行中不斷被寫入，而且是順序?qū)懭?br />

回滾日志 undo log

為了滿足事務(wù)的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個(gè)地方（這個(gè)存儲(chǔ)數(shù)據(jù)備份的地方稱為Undo Log），然后進(jìn)行數(shù)據(jù)的修改。如果出現(xiàn)了錯(cuò)誤或者用戶執(zhí)行了 ROLLBACK語(yǔ)句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開(kāi)始之前的狀態(tài)。

undo log實(shí)現(xiàn)多版本并發(fā)控制（MVCC）來(lái)輔助保證事務(wù)的隔離性。

回滾日志不同于重做日志，它是邏輯日志，對(duì)數(shù)據(jù)庫(kù)的修改都邏輯的取消了。當(dāng)事務(wù)回滾時(shí)，它實(shí)際上做的是與先前相反的工作。對(duì)于每個(gè)INSERT，InnoDB存儲(chǔ)引擎都會(huì)完成一個(gè)DELETE；對(duì)于每個(gè)UPDATE，InnoDB存儲(chǔ)引擎都會(huì)執(zhí)行一個(gè)相反的UPDATE。

事務(wù)提交后并不能馬上刪除undo log，這是因?yàn)榭赡苓€有其他事務(wù)需要通過(guò)undo log 來(lái)得到行記錄之前的版本。故事務(wù)提交時(shí)將undo log 放入一個(gè)鏈表中，是否可以刪除undo log 根據(jù)操作不同分以下2種情況：

• Insert undo log： insert操作的記錄，只對(duì)事務(wù)本身可見(jiàn)，對(duì)其他事務(wù)不可見(jiàn)(這是事務(wù)隔離性的要求),故該undo log可以在事務(wù)提交后直接刪除。不需要進(jìn)行 purge操作。
• update undo log：記錄的是對(duì) delete和 update操作產(chǎn)生的 undo log。該undo log可能需要提供MVCC機(jī)制，因此不能在事務(wù)提交時(shí)就進(jìn)行刪除。提交時(shí)放入undo log鏈表,等待 purge線程進(jìn)行最后的刪除。

鎖

事務(wù)的隔離性的實(shí)現(xiàn)原理就是鎖，因而隔離性也可以稱為并發(fā)控制、鎖等。事務(wù)的隔離性要求每個(gè)讀寫事務(wù)的對(duì)象對(duì)其他事務(wù)的操作對(duì)象能互相分離。再者，比如操作緩沖池中的LRU列表，刪除，添加、移動(dòng)LRU列表中的元素，為了保證一致性那么就要鎖的介入。

鎖的類型

InnoDB主要有2種鎖：行級(jí)鎖，意向鎖

行級(jí)鎖：

• 共享鎖（讀鎖 S），允許事務(wù)讀一行數(shù)據(jù)。事務(wù)拿到某一行記錄的共享S鎖，才可以讀取這一行，并阻止別的事務(wù)對(duì)其添加X(jué)鎖。共享鎖的目的是提高讀讀并發(fā)。
• 排它鎖（寫鎖 X），允許事務(wù)刪除一行數(shù)據(jù)或者更新一行數(shù)據(jù)。事務(wù)拿到某一行記錄的排它X鎖，才可以修改或者刪除這一行。排他鎖的目的是為了保證數(shù)據(jù)的一致性。

行級(jí)鎖中，除了S和S兼容，其他都不兼容。

意向鎖：

• 意向共享鎖（讀鎖 IS ），事務(wù)想要獲取一張表的幾行數(shù)據(jù)的共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
• 意向排他鎖（寫鎖 IX），事務(wù)想要獲取一張表中幾行數(shù)據(jù)的排它鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

解釋一下意向鎖

The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

意向鎖的主要用途是為了表達(dá)某個(gè)事務(wù)正在鎖定一行或者將要鎖定一行數(shù)據(jù)。e.g:事務(wù)A要對(duì)一行記錄r進(jìn)行上X鎖，那么InnoDB會(huì)先申請(qǐng)表的IX鎖，再鎖定記錄r的X鎖。在事務(wù)A完成之前，事務(wù)B想要來(lái)個(gè)全表操作，此時(shí)直接在表級(jí)別的IX就告訴事務(wù)B需要等待而不需要在表上判斷每一行是否有鎖。意向排它鎖存在的價(jià)值在于節(jié)約InnoDB對(duì)于鎖的定位和處理性能。另外注意了，除了全表掃描以外意向鎖都不會(huì)阻塞。

鎖的算法

InnoDB有三種行鎖的算法：

• Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，而非記錄本身
• Next-Key Lock：結(jié)合Gap Lock和Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身。主要解決的問(wèn)題是REPEATABLE READ隔離級(jí)別下的幻讀?？梢詤⒖嘉恼铝私馐聞?wù)隔離級(jí)別的相關(guān)知識(shí)點(diǎn)。

這里主要講一下Next-Key Lock，利用Next-key Lock鎖定的不是單個(gè)值而是一個(gè)范圍，他的目的就是為了阻止多個(gè)事務(wù)將記錄插入到同一范圍內(nèi)從而導(dǎo)致幻讀。

注意了，如果走唯一索引，那么Next-Key Lock會(huì)降級(jí)為Record Lock，即僅鎖住索引本身，而不是范圍。也就是說(shuō)Next-Key Lock前置條件為事務(wù)隔離級(jí)別為RR且查詢的索引走的非唯一索引、主鍵索引。

下面我們用個(gè)例子詳細(xì)說(shuō)一下。

首先建立一張表：

CREATE TABLE T (id int ,f_id int,PRIMARY KEY (id), KEY(f_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into T SELECT 1,1;
insert into T SELECT 3,1;
insert into T SELECT 5,3;
insert into T SELECT 7,6;
insert into T SELECT 10,8;

事務(wù)A執(zhí)行如下語(yǔ)句：

SELECT * FROM T WHERE f_id = 3 FOR UPDATE

這時(shí)SQL語(yǔ)句走非唯一索引，因此使用Next-Key Locking加鎖，并且有2個(gè)索引，其需要分別進(jìn)行鎖定。

對(duì)于聚集索引，其僅對(duì)id等于5的索引加上Record Lock。而對(duì)于輔助索引，其加上Next-Key Lock，鎖定了范圍（1,3），特別需要注意的是，InnoDB存儲(chǔ)引擎還會(huì)對(duì)輔助索引下一個(gè)鍵值加上Gap Lock，即范圍（3.6）的鎖。

所以如果在新session中執(zhí)行如下語(yǔ)句都會(huì)報(bào)錯(cuò)[Err] 1205 - Lock wait timeout exceeded; try restarting transaction：

select * from T where id = 5 lock in share MODE -- 不能執(zhí)行，因?yàn)槭聞?wù)A已經(jīng)給id=5的值加上了X鎖，執(zhí)行會(huì)被阻塞
INSERT INTO T SELECT 4,2 -- 不能執(zhí)行，輔助索引的值為2，在（1,3）的范圍內(nèi)，執(zhí)行阻塞
INSERT INTO T SELECT 6,5 -- 不能執(zhí)行，gap鎖會(huì)鎖?。?,6）的范圍，執(zhí)行阻塞

此時(shí)想象一下，事務(wù)A鎖定了f_id =5 的記錄， 正常會(huì)有個(gè)gap lock，鎖?。?,6），那么如果沒(méi)有（5,6）的gap鎖，那么用戶可以插入索引 f_id 為5的記錄，這樣事務(wù)A再次查詢就會(huì)返回一個(gè)不同的記錄，也就導(dǎo)致了幻讀的產(chǎn)生。

同理，如果我們事務(wù)A執(zhí)行的是select * from T where f_id = 10 FOR UPDATE,在表里查不到數(shù)據(jù)，但是基于Next-Key Lock會(huì)鎖?。?，+∞），我們執(zhí)行INSERT INTO T SELECT 6,11是無(wú)法插入成功的，這就從根本上解決了幻讀問(wèn)題。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。