mysql repeatable-read 一次利用間隙鎖解決幻讀案例

標籤：mysql   ica   err   檢索   資料集   層級   遇到   區別   條件   

repeatable-read是Mysql預設交易隔離等級！能解決髒讀以及不可重複讀取的問題，但可能出現幻讀的情況

 

不可重複讀取：在一個未提交的事務裡，二次查詢結果可能不相同，因為在這個事務執行過程中，外面的   事務可能對這個資料集進行了修改並提交！

幻讀：一個事務在操作過程中！有別的事務對此資料集進行了修改並提交，但這些操作第一個事務讀不到，等到這個事務提交的時候，便有可能引起明明插入的資料沒有查詢到，但卻出現插入重複的錯誤！

不可重複讀取與幻讀的區別：

不可重複讀取是能讀到其它事務已經提交的資料，幻讀是讀不到其它事務已提交的資料！

間隙鎖：間隙鎖主要用來防止幻讀，用在repeatable-read隔離等級下，指的是當對資料進行條件，範圍檢索時，對其範圍內也許並存在的值進行加鎖！當查詢的索引含有唯一屬性（唯一索引，主鍵索引）時，Innodb儲存引擎會對next-key lock進行最佳化，將其降為record lock,即僅鎖住索引本身，而不是範圍！若是普通輔助索引，則會使用傳統的next-key lock進行範圍鎖定！

 

 

/*

幻讀案例：有個表 (id 欄位為非唯一輔助索引)每次插入前需查詢這欄位的最大值，然後再取最大值+1插入！

事務1：                                                                                            事務2：

select max(id) from e;                                                                       insert into e values (11)

10                                                                                                       commit;

insert into e values (11)

commit;

ERROR 1062 (23000): Duplicate entry ‘11‘ for key ‘id‘

在上述事務1中明明查詢最大值為10，但插入最大值+1的時候卻報錯！

 

解決方案：利用mysql間隙鎖

事務1：                                                                              事務2：

select max(id) from e lock in share mode;  

（此時會對id為10以上的所有不存在的值加間隙鎖）                   

10                                                                                       insert into e values (11);

insert into e values (11)                                                   commit;  此時提交會一處於等待狀態，

commit;

 */   

                                                                                 

總結：

表a

id

3

5

6

9

 

在運用間隙鎖的過程中，（-00 +00為負正無窮大）

如果條件為where a=5這樣的條件，則間隙鎖鎖住的範圍為(-00,3),(3,5),(5,6),(6,9),(9,+00)  

如果條件為where a>5,則間隙鎖鎖住的範圍為（5,+00）

如果為select max(id),則鎖住的範圍為(max(id),+00)

另外在測試間隙鎖的過程中遇到了innodb鎖全表，全表所有間隙上鎖的情況！這篇博文裡的提到：

http://blog.itpub.net/29254281/viewspace-1401413/

 

原文地址：http://blog.51cto.com/fucheng/1619359

mysql repeatable-read 一次利用間隙鎖解決幻讀案例