Mysql InnoDB存儲引擎併發控制與事務

在上一篇文章《mysql基礎-索引》我們對mysql索引中的一些基礎知識進行了學習，本篇文章我們一起來看看InnoDB中鎖與事務。

InnoDB如何進行併發控制

首先需要明確的是為什麼需要併發控制？

多個事務在同一時刻操作同一個臨界資源，如果不進行有效的併發安全處理，就會導致數據產生不一致問題。

併發控制的常見思路

• 鎖：數據操作前鎖住，不允許其他併發任務操作，操作完成後釋放鎖
• 數據多版本： 寫任務時將數據克隆一份，以版本號區分，寫任務操作新克隆數據直至提交，讀任務可以併發讀取舊版本，不至於阻塞

InnoDB存儲引擎中這兩種併發控制的方法都有實現，對於鎖InnoDB實現了兩種鎖模式：

• 共享鎖（S鎖）：S鎖能夠實現讀讀併發，讀取數據時加S鎖
• 排他鎖（X鎖）：X鎖實現讀寫互斥，寫寫互斥，修改數據時加X鎖

InnoDB使用鎖進行併發控制能夠實現讀讀並行，讀寫與寫寫均無法並行。一旦寫數據任務還沒有完成，數據是無法被其他事務讀取，這對事務併發有較大影響。

數據多版本：

InnoDB利用數據多版本實現讀寫任務的併發，大大提高innodb的併發度。

如圖所示：T1時刻來了一個寫任務，複製了一個新的版本data(v1)，寫任務操作data(v1)；同時T2、T3時刻來的讀任務還可以對data(v0)的數據執行讀操作。

InnoDB是如何實現數據多版本呢

• redo日誌

數據庫事務提交後必須將數據刷到磁盤，以保證ACID特徵。但磁盤讀寫性能較差，每次刷新磁盤會極大影響數據庫性能。因此InnoDB會將修改行為先寫到redo日誌裡（此時變成了順序寫），然後定期異步刷新到磁盤。

• undo日誌

數據庫事務未提交時將事務修改前的數據存放到undo日誌中，當事務回滾時，可以利用undo日誌中的數據對事務進行回滾。

對於insert 操作，undo日誌中存的是primary key，回滾時直接刪除; 對於delete和update，undo記錄舊數據的row，回滾時直接恢復。

例如下面一張表tb_user,主鍵是id：

先插入一條數據

insert into tb_user(id, name, password) values(4,'along','123456');

執行一條更新：

update tb_user(name,password) values('tom','666') where id=2;

再執行一條刪除：

delete from tb_user where id=3;

那麼InnoDB的undo日誌中就會增加三條日誌：

存放undo日誌的地方被稱為回滾段，在事務回滾時，InnoDB會根據undo日誌中的內容對將事務進行回滾，例如insert操作的回滾會刪除id=4的數據；update和delete操作回滾時則會恢復舊版本的數據。

存放undo日誌的地方被稱為回滾段，回滾段作為InnoDB的數據舊版本為InnoDB提供多版本併發控制支持（Multi Version Concurrency Control, MVCC）

我們對InnoDB的多版本併發控制（MVCC）進行簡單總結：

• 舊版本存在回滾段裡（存放undo日誌的地方）
• InnoDB利用undo日誌實現MVCC，提高併發
• 數據多版本實現讀寫並行
• InnoDB併發度較其他引擎更高，快照讀不加鎖
• InnoDB所有的普通select都是快照讀

InnoDB實現的鎖類型

在上面的內容中我們簡單提到了InnoDB中的鎖，其實InnoDB鎖實現鎖機制遠比上文中提到的要複雜，在這一小節中我們就係統的看看InnoDB實現的哪些鎖。

我們有一張表tb_user，id是表的主鍵，本節中的列子都基於這張表展開：

共享鎖和排他鎖（Shared and Exclusive Locks）

• 共享鎖（S鎖）：實現讀讀併發
• 排他鎖（X鎖）：實現讀寫互斥，寫寫互斥，事務只有拿到排他鎖才能刪除或修改這一行

select * from tb_user where id=1 in share mode; //加S鎖

select * from tb_user where id=1 for update; //加X鎖

記錄鎖（Record Locks）

記錄鎖加到索引記錄上，用於鎖定一個索引記錄

注意記錄鎖是加到索引上，而非數據行，InnoDB行鎖都是基於索引，而非數據行。

我們做一個實驗來驗證一下記錄鎖，將數據庫事務改為手動提交，

先執行：

start transaction;

update tb_user set address='beijing' where id=9;

事務暫不提交

再執行:

start transaction:

update tb_user set password='123' where id=9;

事務暫不提交

可以看到第二個事務獲取鎖超時，執行失敗。就是由於第一個事物拿到了id=9索引的記錄鎖，並且事務沒有提交，鎖也就不會被釋放，導致第二個事務等待鎖超時。

既然記錄鎖是在索引上加的，而非在數據行上。那麼如果我們在name字段上建一個輔助索引(Secondary Index)，在第一個事務還沒提交的情況下執行：

start transaction:

update tb_user set password='666' where name='xiaohong';

那麼本事務能執行成功嗎？

留給大家思考。

間隙鎖（gap locks）

間隙鎖可以封鎖索引記錄的間隔

舉例來說，表裡有以上這些字段，id上存在主鍵索引，索引記錄的間隔就是(-oo,1)、（1,6）、（6，9）、（9，11）、（11，+oo)。那麼獲取間隙鎖的事務就會鎖住索引記錄之間的間隔，其他事務就無法插入id在這些間隔的記錄。

我們做下面這個實驗：

——————————————————————

開啟一個事務A

start transaction:

select * from tb_user where id between 6 and 9 for update;

事務不提交

——————————————————————

開啟一個事務B

start transaction:

insert into tb_user values(7,'xiaohua','beijing','123',null);

——————————————————————

可以看到事務B獲取鎖超時，是由於事務A獲取臨鍵鎖鎖住了區間（6，9），因此事務B無法插入id為7的記錄。

臨鍵鎖 （Nexted-Key Locks）

記錄鎖和間隙鎖的組合，它封鎖的範圍既包含索引記錄，又包含索引區間。

舉例來說，表裡還是以上這些字段，臨鍵鎖封鎖的區間是(-oo,1)、[1,6）、[6，9）、[9，11）、[11，+oo)。

針對臨鍵鎖我們做下面這個實驗：

——————————————————————

開啟一個事務A

start transaction:

select * from tb_user where id between 6 and 9 for update;

事務不提交

——————————————————————

開啟一個事務B

start transaction:

insert into tb_user values(6,'xiaohua','beijing','123',null);

——————————————————————

因為事務A獲取的臨鍵鎖會鎖住區間[6,9]，事務B同樣是獲取鎖超時。

插入意向鎖

專門針對insert操作，多個事務同一個範圍內插入記錄，如果位置不衝突，不會阻塞彼此。（非自增主鍵）

插入意向鎖很好理解，就是針對id是非自增的情況下有效。我們在這篇文章中給出的表就是非自增主鍵，分別執行下面兩個插入SQL:

insert into tb_user values(7,'xiaohua','beijing','123',null);

insert into tb_user values(8,'xiaoqing','shanghai','123456',null);

由於他們插入id不同，所以這兩個事務不會阻塞彼此。

自增鎖

是一種表級別鎖，專門針對事務插入自增主鍵的列。如果有一個事務正在往表中插入記錄，其他插入事務必須等待。

InnoDB存儲引擎主要支持的鎖類型就介紹完了，這裡我們做個小結：

• 共享鎖和排他鎖是行級鎖，實現讀讀併發，讀寫互斥，寫寫互斥;
• 記錄鎖鎖定索引記錄，而不是鎖定數據行
• 間隙鎖鎖定間隔，防止間隔中被其他事務插入
• 臨鍵鎖鎖定索引記錄+加間隔
• 插入意向鎖是間隙鎖的一種，針對非自增主鍵插入，如果新增主鍵不衝突，則不會彼此阻塞
• 自增鎖是一種表級別鎖，針對自增主鍵插入，如果有事務正在插入，其他插入型事務必須等待

InnoDB事務

併發事務中存在的問題

我們在學數據庫時應該都學過髒讀、不可重複讀、幻讀，但好像總是鬧不清它們三個的區別，今天我們一起來看看它們仨到底啥區別。

• 髒讀：一個事務讀取到另一個事務未提交的數據
• 不可重複讀：一個事務兩次讀取一條或一批記錄結果不一致，期間另一個併發事務對數據進行了修改
• 幻讀：一個事務兩次讀取一條或一批記錄結果不一致，期間有另一個併發事務新增了一條數據
• 對於髒讀比較容易區分，對於不可重複讀和幻讀好像這哥倆比較像，其實我們只要抓住一點“不可重複讀的重點在於修改，幻讀的重點在於新增或刪除”，就能很容易的將不可重複讀和幻讀區別開來。

事務的隔離級別

• 讀未提交(Read Uncommitted)
• 讀提交(Read Committed, RC)
• 可重複讀（Repeated Read, RR, InnoDB默認隔離級別）
• 串行化 (Serializable）

這幾個事務隔離級別從上到下併發性逐漸減弱，一致性逐漸增強。下面這個表格很多同學都見過：

在實際中由於讀未提交的一致性太差、串行化的併發性太差，這兩個隔離級別很少用到。不同的隔離級別下其實是不同的SQL加鎖會存在差異，因此我們主要來看看RC和RR在InnoDB中究竟差別在哪兒。

可重複讀隔離（RR）級別下加什麼鎖

普通快照讀（select … from tb_user where id=10），是一種不加鎖的一致性讀（Consistent Nonlocking Read），使用MVCC實現.

加鎖讀（select … in share mode/ for update），update, delete則與查詢條件有關

• 唯一索引（索引字段值唯一，如id）上使用唯一查詢條件，使用記錄鎖，不會封鎖記錄間隔。如：update tb_user set address=‘beijing’ where id=10。
• 非唯一索引上（索引字段值不唯一，如name）使用唯一查詢條件，則會使用間隙鎖。如update tb_user set address='beijing' where name='jack'。其實這是一種特殊間隙鎖，所有以name='jack'為查詢條件的事務均無法執行，包括新增和刪除，以避免不可重複讀和幻讀。
• 範圍查詢，會使用臨鍵鎖，鎖住索引記錄以及索引之間的範圍，避免範圍內插入記錄和修改，可避免不可重複讀和產生幻影記錄。如：delete from tb_user where id between 3 and 9;

對於InnoDB的RR事務隔離級別，大家是否有這樣的困惑：之前說說RR只能避免不可重複讀不能避免幻讀，而這裡又說RR可以避免幻讀，這是要搞事情啊！

我剛開始也有這個困惑，我們平時看到的說RR只能避免不可重複讀不能避免幻讀是標準的隔離級別，InnoDB在這塊實現上確實沒有遵循標準來。因為InnoDB在實現時使用了臨鍵鎖，避免併發事務再已經加鎖的區間進行插入或刪除，因此是可以避免幻讀的，官網上也有說明。

讀提交（RC）加什麼鎖

• 普通讀是快照度
• 加鎖select, update , delete會使用記錄鎖，間隙鎖和臨鍵鎖在RC下不起作用。由於臨建鎖和間隙鎖在RC下不起作用，因此RC無法避免不可重複讀和幻讀。

Innodb事務總結

• 不可重複讀和幻讀的根本區別在於修改or新增（刪除）
• 讀提交（RC）：普通select快照度，鎖select/update/delete會使用記錄鎖，可能出現不可重複讀和幻讀
• 可重複讀(RR)：普通的快照度不加鎖，鎖select/update/delete根據查詢條件innodb會使用記錄鎖/間隙鎖/臨鍵鎖，以防止讀到幻影記錄

通過本節的學習，我們應該知道了如果有人問單憑一條SQL來判斷加該SQL加了什麼鎖，那我們是無法判斷的。還要確定是什麼隔離級別下，什麼索引（唯一索引or非唯一索引）。