MySQL序列化隔離級別(間隙鎖實現)

序列化隔離級別怎麼解決幻讀問題？
先說下幻讀的含義，幻讀就是在事務中按照同樣的條件前後兩次查詢的結果資料量不同。

解決序列化的幻讀問題用間隙鎖（gap lock），間隙鎖是給不存在的記錄加鎖，要正確理解間隙，知道間隙的範圍。條件無非就是兩類：範圍查詢和等值查詢。再說下範圍查詢和等值查詢都是怎麼加間隙鎖的，分別從主鍵索引和輔助索引兩個場景來說。

一、間隙鎖的概念

我們把事務2 select的指定的條件分為2類：範圍查詢、等值查詢

record lock（記錄鎖，就是行鎖）
gap lock（間隙鎖）
next-key lock：record lock 和 gap lock

二、測試間隙鎖範圍加鎖

設定事務為手動提交，隔離級別設定成序列化

檢視表結構，id、age和name都有索引

場景1：用不可重複的主鍵id測試間隙鎖

做範圍查詢

事務2的select操作只給三行資料加了排它鎖，為什麼插入id=24的資料也不行？

這是因為在序列化隔離級別中，不僅僅是獲取了滿足條件的這3行的行鎖，而且把表資料後邊空洞的地方也上了間隙鎖。

圖中紅色線的地方都上了間隙鎖，上鎖範圍（左開右閉）為：( 11 , 12 ] ∪ ( 12 ， 22 ] ∪ ( 22 ， 23 ] ∪ ( 23 ， + ∞ ]

12,22,23是三個行記錄，因為過濾條件是用id帶有索引的，所以select獲取了12,22,23的共用行鎖（record-lock）， 還把間隙加了間隙鎖，其實就是給間隙加上共用鎖或者排他鎖，將間隙鎖和行鎖統稱next-key lock（record-lock和gap-lock），也就是說where id>11加了next-key lock。正是因為給空洞也加鎖了，所以事務1再想獲取間隙的排它鎖是不可以的，因為共用鎖和排它鎖是不能共存的。

由於事務2是select，所以是給間隙加上了共用鎖，如果事務1做select id>11還是可以的，不能update、insert、delete id>11的資料。

場景2：用可重複的age（有索引）測試間隙鎖

測試輔助索引樹上，間隙鎖的範圍

我們先檢視表結構、表資料，然後回滾。

根據表的內容建簡單的輔助索引

開啟事務進行測試

很明顯，由於age>20的區間都被事務1加上了間隙鎖（這裡加的是共用鎖），所以事務2插入age=22和age=21都失敗了

幻讀就是同一事務兩次用相同的條件查詢資料，下一次查出的資料量和上一次的資料量不一樣，就算事務1把age=20的資料插入表，事務2再用age>20查詢，得到的資料量也不會改變。

那事務1插入age=20的資料能否成功呢？

依然不能成功，這是因為我們插入的資料id是自增的，所以這條資料為(age=20，id=24)，位於輔助索引樹中(age=20，id=12)的右邊，由於(age=20，id=12)右邊都被上了鎖，(age=20，id=24)自然無法插入。

輔助索引值相等的話。主鍵按升序排列。

很顯然，事務1插入的age=18和age=19都不在事務2上鎖的範圍，所以可以插入

場景3：實際情況需要具體分析用的到底是行鎖還是表鎖

回滾，重新開啟事務

開始測試

我們發現事務1無論是插入age>18範圍內的資料，還是範圍外的資料，都無法成功

這時我們就要分析了，這應該沒有用到索引，因為我們用索引，過濾出的資料佔了整張表的一大半，MySQL server沒使用索引。

沒有加行鎖，只能加表鎖（這時加的是共用鎖），所以事務1無論插入什麼資料都不行

果然，沒有用到索引

age>20用到了索引，所以可以用行鎖

三、測試間隙鎖等值加鎖

檢視表結構和表資料

設定手動提交，設定序列化隔離級別，回滾然後啟動事務

1. 測試不能重複的主鍵索引

此時事務2做select操作，由於是等值查詢，所以給這條資料加了共用鎖。

事務2的主鍵或者唯一鍵進行等值查詢的時候，事務1插入一個新的資料是可以成功的，因為主鍵id不能重複，我們不能再插入主鍵id=9的資料。

在這種情況下，主鍵或者唯一鍵是不能重複的，事務2進行等值查詢時，事務1插入一個新的資料，不用擔心這條資料和查詢條件是一樣的，所以肯定能成功

2. 測試能重複的輔助索引

回滾並重啟事務

事務2等值查詢，給age=18這行資料加上了共用鎖（record-lock）

這是一個等值查詢，而且用的是輔助索引age，那麼在輔助索引age的輔助索引樹上葉子節點存的是age的輔助索引值和它所在行的主鍵值，

事務1插入age=18是不被允許的，否則事務2再查詢age=18就有兩條記錄了。

奇怪的是，我們插入age=17,16,15也被阻塞住了

這是因為，為了防止幻讀，除了age=18這條資料加了共用鎖，其兩側也被加了間隙鎖。

如果插入(age=15，id=1)就可以成功，根據輔助索引值相同，按照主鍵值升序排列，(age=15，id=1)應該放在(age=15,id=7)前面，不在間隙鎖範圍內

插入age=14,13都可以成功，不在間隙鎖範圍內。

間隙鎖是給不存在的資料記錄的範圍加鎖：

• 對於輔助索引，若值允許重複，在序列隔離級別中如果進行等值查詢，InnoDB會給資料加上行鎖和間隙鎖（防止別的事務插入索引值重複的資料，造成幻讀）
• 對於主鍵索引，或者唯一鍵索引，值不允許重複，那隻需要加行鎖就夠了（對於唯一鍵索引，不可能發生插入索引值重複的資料）

到此這篇關於MySQL序列化隔離級別(間隙鎖實現)的文章就介紹到這了,更多相關MySQL 間隙鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！