Mysql鎖機制和交易隔離等級

標籤：範圍查詢   ref   val   man   異常   show   write   pos   幻讀   

MyISAM鎖機制：MyISAM只有表鎖，分為X和S鎖兩種(或者叫read lock,write lock)。讀加S鎖，寫加X鎖。互相阻塞，因此並發DML效能並不好。 InnoDB鎖機制：

lock table/tables tab_name read/write; --手動加鎖語句：（同樣適用於Myisam）unlock tables;  --解鎖語句set @@global.innodb_status_output_locks=on; --這樣show engine innodb status\G可以顯示額外的鎖資訊，標準情況下只顯示鎖數目。

一、InnoDB鎖模式（行鎖）以下提到的鎖都是在用到行鎖時才會出現的，如果不使用索引，那麼就InnoDB會用表鎖而非行鎖。 1. 兩種行鎖：共用鎖定（S）：    允許一個事務去讀一行，阻止其他事務獲得相同的資料集的獨佔鎖定。獨佔鎖定（X）：    允許獲得獨佔鎖定的事務更新資料，但是阻止其他事務獲得相同資料集的共用鎖定和獨佔鎖定。 2. 兩種意向表鎖：在加行鎖之前，首先要添加表級意圖鎖定：意圖共用鎖（IS）：    表示事務準備給資料行加入共用鎖定，也就是說一個資料行加共用鎖定前必須先取得該表的IS鎖。意向獨佔鎖定（IX）：    類似上面，表示事務準備給資料行加入獨佔鎖定，說明事務在一個資料行加獨佔鎖定前必須先取得該表的IX鎖。 3.行鎖的範圍（ 可以是S也可以是X鎖）：

• Record Lock：行鎖，鎖定單個行記錄。
  
• Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身，GAP鎖的目的，是為了防止幻讀。
  
• Next-Key Lock：即行鎖+間隙鎖。

以上談了InnoDB提供的行鎖相互關聯類型，說到加鎖模式就必須在交易隔離等級的概念下進行，否則毫無意義。預設的事物隔離等級下，InnoDB只有在DML語句和select(for update/lock in share mode)語句用到索引時才會使用行鎖，否則使用表鎖。 在預設的事物隔離等級下，行鎖的使用分2種情況：

1. 對普通索引的訪問使用next-key鎖，即鎖定行和索引索引值上下範圍內的間隙，不允許間隙插入，防止幻讀。
   
2. 對於有唯一屬性的索引的單行訪問，使用行鎖，只鎖定要訪問的行。例外是如果訪問的是不存在的值也會產生gap行鎖，範圍查詢也是使用next-key行鎖。
   

而在READ COMMITTED隔離等級下，delete/update或select(for update/lock in share mode)操作不加gap鎖，只會加record鎖，因此會有幻讀產生。  二、各交易隔離等級下InnoDB的加鎖模式關於Myql交易隔離等級參見：https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL| REPEATABLE READ| READ COMMITTED| READ UNCOMMITTED| SERIALIZABLE

1. SERIALIZABLE這種隔離等級類似REPEATABLE READ，如果autocommit設為disable，那麼InnoDB隱式的把所有select語句轉化為select lock in share mode，但如果autocommit設為enable，那麼select自成事務，其加鎖模式也同REPEATABLE READ下的一致。delete/update操作也與REPEATABLE READ下的加鎖模式一致。 2. REPEATABLE READ（預設）這種隔離等級下資料庫採用事務級MVCC快照機制，select操作讀取事務級的快照，不加行鎖及表鎖，同時也避免了髒讀、不可重複讀取和幻讀。對於delete/update或select(for update/lock in share mode)操作如果使用索引，那麼如上所說分兩種情況添加行鎖。此種隔離等級類似於SQL Server的snapshot隔離等級。ISO標準定義中此隔離等級下是無法避免幻讀的，但是各資料庫廠商通過MVCC機制直接使select語句不對資源加鎖，從而避免了幻讀。 3. READ COMMITTED這種隔離等級下資料庫採用語句級的MVCC快照機制，來避免髒讀和不可重複讀取，但是不可避免幻讀。select語句對資料表只加中繼資料鎖，不加行鎖及表鎖。delete/update或select(for update/lock in share mode)操作如果使用索引，那麼不加gap鎖，只會加record鎖，因此會有幻讀產生。Ps:注意在此隔離等級下binlog_format必須使用row模式！ 4. READ UNCOMMITTED這種隔離等級下資料庫不採用MVCC快照機制，會髒讀，select語句不加鎖。對於delete/update或select(for update/lock in share mode)操作，加鎖機制與READ COMMITTED一致。
更詳細的各類SQL語句加鎖說明參見：https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html關於mysql事務級一致性讀和語句級一致性讀的說明參考：http://www.cnblogs.com/digdeep/p/4947694.html  三、總結：可以看到Innodb通過mvcc機制在REPEATABLE READ和 READ COMMITTED隔離等級下實現了select不阻塞寫。但是在REPEATABLE READ隔離等級下由於實現了事務級的一致性讀，因此也順帶避免了不可重複讀取和幻讀。這一點其實是與其他資料庫的隔離等級與髒讀、不可重複讀取、幻讀的關係表有差異的地方。例如SQL Server中隔離等級與異常讀的對應關係如下：    四、Insert語句以上所有的鎖模式和交易隔離等級的講解都忽略了insert語句，那麼insert語句在四大隔離等級下的加鎖形式是什麼樣的呢？官網提供的相關頁面如下：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not 
prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that 
multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 
4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

首先我們要明確一個概念，即InnoDB表必定有主鍵，無論是顯式的還是隱式的系統自訂主鍵，這意味著insert必定用到主鍵索引，然後再來看以上內容：這段的核心意思就是說insert語句只會對加一個index record lock，不會加gap鎖，因此insert不同的主鍵不會互相阻塞。由於insert語句是DML語句，因此可以認為在所有隔離等級下其加鎖模式都是一樣的，即：先加表級IX鎖，再加針對具體插入行的X模式的record lock。

Mysql鎖機制和交易隔離等級