MySQL在預設事務下各SQL語句使用的鎖分析

標籤：儲存   索引   ODB   可重複   col   HERE   block   read   sql   

 資料庫使用鎖是為了支援更好的並發，提供資料的完整性和一致性。InnoDB是一個支援行鎖的儲存引擎，鎖的類型有：共用鎖定（S）、獨佔鎖定（X）、意圖共用（IS）、意向排他（IX）。為了提供更好的並發，InnoDB提供了非鎖定讀：不需要等待訪問行上的鎖釋放，讀取行的一個快照。該方法是通過InnoDB的一個特性：MVCC來實現的

InnoDB有三種行鎖的演算法：

1，Record Lock：單個行記錄上的鎖。

2，Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。

3，Next-Key Lock：1+2，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。

如果採用MySQL預設的交易隔離等級（可以重複讀：repeatable read）

可重複讀取（Repeatable Read）：防止（避免）不可重複讀取和髒讀，但是有時可能出現幻讀資料。這可以通過“共用讀鎖”和“排他寫鎖”實現。讀取資料的事務將會禁止寫事務（但允許讀事務），寫事務則禁止任何其他事務。

• 髒讀：一個事務還未提交，另外一個事務訪問此事務修改的資料，並使用，讀取了事務中間狀態資料。
• 幻讀：一個事務讀取2次，得到的記錄條數不一致，由於2次讀取之間另外一個事務對資料進行了增刪。
• 不可重複讀取：一個事務讀取同一條記錄2次，得到的結果不一致，由於在2次讀取之間另外一個事務對此行資料進行了修改。

1.SELECT ... FROM

  >是一個快照讀，通過讀取資料庫的一個快照，不會加任何鎖，除非將隔離等級設定成了 SERIALIZABLE 。

2.SELECT ... FROM ... LOCK IN SHARE MODE

>在所有索引掃描範圍的索引記錄上加上共用的next key鎖;如果是唯一索引，只需要在相應記錄上加index record lock。這些被共用lock住的行無法進行update/delete。
>允許其它事務對這些記錄再加SHARE鎖
>如果沒有使用到索引，則鎖住全表（表級的獨佔鎖定），無法進行insert/update/delete。

3.SELECT ... FROM ... FOR UPDATE

>在所有索引掃描範圍的索引記錄上加上排他的next key鎖。如果是唯一索引，只需要在相應記錄上加index record lock。
>如果沒有利用到索引將鎖住全表（表級的獨佔鎖定），其它事務無法進行insert/update/delete操作。

4. UPDATE ... WHERE ...

>在所有索引掃描範圍的索引記錄上加上排他的next key鎖。如果是唯一索引，只需要在相應記錄上加index record lock。
>如果沒有利用到索引將鎖住全表（表級的獨佔鎖定）,其它事務無法進行其他的insert/update/delete操作。；

5. DELETE FROM ... WHERE ...

>語句在所有索引掃描範圍的索引記錄上加上排他的next key鎖。如果是唯一索引，只需要在相應記錄上加index record lock。
>如果沒有利用到索引將鎖住全表（表級的獨佔鎖定）,其它事務無法進行其它的insert/update/delete操作。

6. INSERT

>在插入的記錄上加一把獨佔鎖定，這個鎖是一個index-record lock，並不是next-key 鎖，因此就沒有gap 鎖，他將不會阻止其他會話在該條記錄之前的gap插入記錄。

MySQL在預設事務下各SQL語句使用的鎖分析