mysql的隔離性和鎖

INNODB的隔離性質

INNODB的事務支援4種隔離機制，分別是 READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. 其中預設的為REPEATABLE READ.

下面詳細分析這4種隔離性的聯絡和區別。

 

REPEATABLE READ

在不用鎖的查詢語句中，此隔離等級保證了每次事務讀取到的資料是一致的。

在用鎖的查詢中（update等），如果查詢是指定索引查詢，那麼只會在鎖住索引中的某個值，如果是指定索引的範圍查詢，那麼會在範圍的間隙加鎖，也就是間隙鎖。

 

此隔離等級會有重複讀取的問題。

當你在一個事務中查詢兩次，可能返回的結果不一樣，這是由於在第一次查詢結束後，此隔離等級只會鎖住對應的值和值的間隙，另一個事務對錶進行了插入了原來不存在的值（也就是沒有被鎖住的值），那麼就會導致兩次查詢的結果不一致。

 

// 重複讀問題的解釋

dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

 

READ COMMITTED

比REPEATABLE READ弱一級，在指定索引查詢時，如果是範圍查詢那麼不會加間隙鎖，只會鎖住某個值，這就會導致幻讀的問題。

 

舉例而言：

當你有一個索引列，資料為1,3,5，當你查詢這一列>1的資料的時候，由於沒有間隙鎖的存在，mysql只會鎖住3和5，而這個時候有另一個事務插入了4，之後又復原了，那麼之前的查詢就會查詢4，而之後的查詢查不到4，這就是幻讀的問題。

 

幻讀的意思是，當一個事務執行兩次，一次沒有讀取到的行，第二次卻讀取到了，那一行就稱為魔幻的一行..

// 幻讀的解釋

dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

 

READ UNCOMMITTED

比READ COMMITTED弱一級，沒有鎖的隔離，會導致髒讀。這是由於，當某些行沒有被鎖住的導致其他事務可以進行更新操作，從而引發讀取到髒資料。

 

髒讀的意思是，讀取到其他事務已經修改但是還沒有提交的資料。

 

SERIALIZABLE

在REPEATABLE READ的基礎上，自動的加入了 'lock in share' 語句。也就是說，在select的過程中，不允許其他需要獨佔鎖的操作發生（update等），只允許需要共用鎖定的操作。

 

此隔離等級可以保證資料讀取完整的一致性，但是級會導致所有需要獨佔鎖的操作都變成了串列執行，效能和表鎖已經沒有區別了。

 

參考

mysql術語大全

dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_dirty_read

 

mysql官網對隔離性的介紹

dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

 

 

表級鎖和行級鎖表級鎖

在INNODB還沒出現之前，MyISAM資料庫引擎只支援表級鎖，包括表級共用鎖定和表級獨佔鎖。

共用鎖定支援多個操作同時執行，也就是並發執行，在一個操作擁有共用鎖定的情況下，其他動作是無法擷取到獨佔鎖的。

獨佔鎖只能串列執行。

 

 

行級鎖

INNODB支援行鎖，將鎖定對象的粒度細化，可以增加並發效能。

需要注意的是行級鎖是建立在索引之上的，也就是說在查詢沒有索引的欄位的情況下，還是會使用表鎖。

 

參考

對MYISAM引擎和INNODB引擎很詳細的對比分析

baijiahao.baidu.com/s?id=1610581108528334819&wfr=spider&for=pc

 

關於在非阻塞的情況下讀取一致性的細節

在INNODB的儲存引擎情境下，每次查詢，mysql都會給出當前表的快照，也就是說在該查詢之前其他提交的事務都會被感知，而查詢開始之後，其他提交的事務就不會被感知。

舉例而言

當你select count(*) from table的時候，其實統計的是當前表的快照，不會擷取任何鎖，如果與此同時，有另一個插入操作，那麼這兩個操作是完全可以同時進行的。當然，這個結果可能不是你業務上希望看到的，可能你想要強一致性，也就是在統計資料量的時候不允許其他插入或者刪除操作，那麼你可以先lock table; select count(*) from table; 最後unlock table;通過表鎖的方式保證資料的強一致性。