之前的實驗,提到過一個場景

當前讀(鎖定讀)加鎖情況.

	讀提交	可重複讀
主鍵索引	鎖定主鍵索引	鎖定主鍵索引
唯一索引	鎖定唯一索引的值和主鍵索引的值	鎖定唯一索引的值和主鍵索引的值
普通索引	鎖定普通索引的值和主鍵索引的值	鎖定普通索引的值和主鍵索引的值,普通索引增加間隙鎖
無索引	鎖定所有的主鍵索引,在伺服器層對不符合條件的記錄解鎖	鎖定所有的主鍵索引和主鍵索引的間隙

但是如何驗證讀提交隔離級別,會鎖定所有主鍵主鍵,而在伺服器層解鎖呢?
http://blog.itpub.net/29254281/viewspace-1398273/

實驗資料如下:
create table t
(
  a int primary key,
  b int,
  c int
) engine=innodb;

insert into t
values
(1,10,10),
(3,10,20),
(5,20,30),
(7,20,40),
(9,20,50);
commit;

首先,可重複讀隔離級別.
終端一:
select * from t where b=20 and c=30 for update;

終端二:
delete from t where b=20 and c=50;
阻塞

檢視鎖情況
select * from information_schema.innodb_locks;

可以看到鎖定的資料是主鍵索引 1
這是因為終端一已經鎖定了所有的主鍵索引和主鍵索引間隙.
終端二獲取第一個主鍵索引就被阻塞.

同樣的過程,改為讀提交隔離級別.
終端一
select * from t where b=20 and c=30 for update;

終端二
delete from t where b=20 and c=50;
阻塞

檢視鎖情況
select * from information_schema.innodb_locks;

可以看到鎖定了主鍵索引 5

這驗證了何老師的說法,
就是在讀提交隔離級別,對所有的主鍵索引上鎖,在伺服器層釋放不符合條件的鎖,
也就是說終端一鎖定了所有的主鍵索引,在伺服器層釋放了其他主鍵索引的鎖,只保留了a=5的鎖.
終端二開始加鎖,加到a=5的主鍵索引不能獲取,所以阻塞

從現象看,如果終端一的語句不走二級索引或者主鍵索引,終端二的語句都會阻塞,
但是不同的是,可重複讀鎖定所有主鍵索引和主鍵索引間隙,
而讀提交最終只會鎖定相關記錄的主鍵索引.

那麼在讀提交的隔離級別下,下面的語句不會阻塞
select * from t where a=1 for update;

而在可重複讀隔離級別下,上面的語句就會阻塞.

MySQL選用可重複讀之前一定要想到的事情(無索引加鎖驗證)

相關文章