innodb幻讀問題怎么解決

本篇內容主要講解“innodb幻讀問題怎么解決”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“innodb幻讀問題怎么解決”吧!

1.幻讀

CREATE TABLE `test_20` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`)) ENGINE=InnoDB;

insert into test_20 values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

begin;select * from t where d=5 for update;commit;

這個語句會命中 d=5 的這一行，對應的主鍵 id=5，因此在 select 語句執行完成后，id=5 這一行會加一個寫鎖，而且由于兩階段鎖協議，這個寫鎖會在執行 commit 語句的時候釋放。

如果只在 id=5 這一行加鎖，而其他行的不加鎖的話，會怎么樣？

• 在READ COMMITTED級別下可執行

• 在REPEATABLE READ級別下，session B會阻塞

• 1）Q1 只返回 id=5 這一行；

• 2）在 T2 時刻，session B 把 id=0 這一行的 d 值改成了 5，因此 T3 時刻 Q2 查出來的是id=0 和 id=5 這兩行；

• 3）在 T4 時刻，session C 又插入一行（1,1,5），因此 T5 時刻 Q3 查出來的是 id=0、id=1和 id=5 的這三行。

其中，Q3 讀到 id=1 這一行的現象，被稱為“幻讀”。也就是說，幻讀指的是一個事務在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒有看到的行。

說明：

• 1）在可重復讀隔離級別下，普通的查詢是快照讀，是不會看到別的事務插入的數據的。因此，幻讀在“當前讀”下才會出現。

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

2.幻讀的問題（更新與新增）

2.1 語義上

session A 在 T1 時刻就聲明了，“我要把所有 d=5 的行鎖住，不準別的事務進行讀寫操作”。而實際上，這個語義被破壞了。

2.2 數據一致性問題

鎖的設計是為了保證數據的一致性。而這個一致性，不止是數據庫內部數據狀態在此刻的一致性，還包含了數據和日志在邏輯上的一致性。

候 binlog 里面的內容：

• 1） T2 時刻，session B 事務提交，寫入了兩條語句；

• 2）T4 時刻，session C 事務提交，寫入了兩條語句；

• 3）T6 時刻，session A 事務提交，寫入了 update t set d=100 where d=5 這條語句。

放在一起如下：

update t set d=5 where id=0; /*(0,0,5)*/update t set c=5 where id=0; /*(0,5,5)*/insert into t values(1,1,5); /*(1,1,5)*/update t set c=5 where id=1; /*(1,5,5)*/update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/

這個語句序列，不論是拿到備庫去執行，還是以后用 binlog 來克隆一個庫，這三行的結果，都變成了 (0,5,100)、(1,5,100) 和 (5,5,100)。

原因是：

• 這是我們假設“select * from t where d=5 for update 這條語句只給d=5 這一行，也就是 id=5 的這一行加鎖”導致的。

進一步假設：

• 把掃描過程中碰到的行，也都加上寫鎖，再來看看執行效果

binlog 里面執行序列：

insert into t values(1,1,5); /*(1,1,5)*/update t set c=5 where id=1; /*(1,5,5)*/update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/update t set d=5 where id=0; /*(0,0,5)*/update t set c=5 where id=0; /*(0,5,5)*/

這里解決了session B的問題，但是session C新增的問題并沒有解決！

在 T3 時刻，我們給所有行加鎖的時候，id=1 這一行還不存在，不存在也就加不上鎖。也就是說，即使把所有的記錄都加上鎖，還是阻止不了新插入的記錄。

3.InnoDB 怎么解決幻讀的問題

產生幻讀的原因是，行鎖只能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問題，InnoDB 只好引入新的鎖，也就是間隙鎖 (GapLock)。

間隙鎖，鎖的就是兩個值之間的空隙。比如表 test_20，初始化插入了 6 個記錄，這就產生了 7 個間隙。

當你執行 select * from t where d=5 for update 的時候，就不止是給數據庫中已有的 6個記錄加上了行鎖，還同時加了 7 個間隙鎖。這樣就確保了無法再插入新的記錄。

數據行是可以加上鎖的實體，數據行之間的間隙，也是可以加上鎖的實體。但是間隙鎖跟我們之前碰到過的鎖都不太一樣。

• 跟行鎖有沖突關系的是“另外一個行鎖”。

  
  

  
  

• 

• 跟間隙鎖存在沖突關系的，是“往這個間隙中插入一個記錄”這個操作。間隙鎖之間都不存在沖突關系。
  如下：這里 session B 并不會被堵住。因為表 test_20里并沒有 c=7 這個記錄，因此 session A 加的是間隙鎖 (5,10)。而 session B 也是在這個間隙加的間隙鎖。它們有共同的目標，即：保護這個間隙，不允許插入值。但，它們之間是不沖突的。

  

• 間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開后閉區間。也就是說，表test_20初始化以后，如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25,+suprenum]。
  把間隙鎖記為開區間，把 next-keylock 記為前開后閉區間。

  
  

  
  

3.1 帶來的問題

• 1）session A 執行 select ... for update 語句，由于 id=9 這一行并不存在，因此會加上間隙鎖 (5,10);

• 2）session B 執行 select ... for update 語句，同樣會加上間隙鎖 (5,10)，間隙鎖之間不會沖突，因此這個語句可以執行成功；

• 3）session B 試圖插入一行 (9,9,9)，被 session A 的間隙鎖擋住了，只好進入等待；

• 4）session A 試圖插入一行 (9,9,9)，被 session B 的間隙鎖擋住了。

至此，兩個 session 進入互相等待狀態，形成死鎖。間隙鎖的引入，可能會導致同樣的語句鎖住更大的范圍，這其實是影響了并發度的。

解決方法：

• 間隙鎖是在可重復讀隔離級別下才會生效的。所以，你如果把隔離級別設置為讀提交的話，就沒有間隙鎖了。但同時，你要解決可能出現的數據和日志不一致問題，需要把 binlog 格式設置為 row。這，也是現在不少公司使用的配置組合。

到此，相信大家對“innodb幻讀問題怎么解決”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！