MySQL鎖和事務（一）：InnoDB鎖（MySQL 官方文檔粗翻）

標籤：聯合   引入   prim   語句   操作   asc   匹配   結果   style   

　　// 寫在前面，實際上，資料庫加鎖的類型和範圍受到多種因素的影響，例如資料庫隔離等級，SQL語句，是否使用主鍵、索引等等。可以查看博文: http://www.cnblogs.com/zhaoyl/p/4121010.html 瞭解

　　這一章節講述了InnoDB使用的鎖類型。

• 共用鎖定（S）和獨佔鎖（X）
• 意圖鎖定
• 行鎖（record lock，不知道叫 記錄鎖是不是更好，百度了一下有人叫行鎖）
• 間隙鎖（gap lock）
• Next-Key鎖
• 插入意圖鎖定
• AUTO-INC(自增長)鎖
• 空間鎖（Predicate Locks for Spatial Indexes）

　共用鎖定和獨佔鎖

　　InnoDB實現了兩種標準的行級鎖，一種是共用鎖定（S鎖），一種是獨佔鎖（X鎖）。

• 共用鎖定允許事務讀取一條記錄。
• 獨佔鎖允許事務更新或者刪除一條記錄的鎖。

　　如果一個事務 T1 擁有記錄 r 的共用鎖定，另外一個事務 T2 想要對行 r 加鎖的請求會得到這樣的處理：

• 如果 T2 想要加一個 S 鎖，那麼他能馬上獲得這個鎖。結果就是，T1 和 T2 同時擁有 r 的共用鎖定。
• T2 想要擷取的是 X 鎖，那麼這麼請求會被阻塞。

　　如果一個事務 T1 擁有行 r 的 X 鎖，另外一個事務 無論想擷取 r 的什麼鎖都會被阻塞。事務 T2 必須要先等待 T1釋放 r 上面的S鎖。

  意圖鎖定

　　InnoDB 支援多粒度的鎖，允許行鎖和表鎖共存。為了支援多粒度的鎖，InnoDB引入了意圖鎖定。意圖鎖定是一個表層級的鎖，表明一個事務在之後（requires later）要擷取表中某些行的S鎖或X鎖。InnoDB中使用了兩種意圖鎖定（假設事務 T 已經向表 t 請求擷取對應的意圖鎖定）

• 意圖共用鎖（IS）：事務 T 想要對錶 t 中的一些行加上S鎖。
• 意向獨佔鎖定（IX）：事務 T 想要對這些行加上X鎖。

　　例如

SELECT ... LOCK IN SHARE MODE  

　　設定了 IS 鎖，而

SELECT ... FOR UPDATE 

　　設定了 IX 鎖。

　　意圖鎖定協議（The intention locking protocol）如下所示：

• 在一個事務擷取表 t 中某一行的 S鎖之前，他必須先擷取 IS鎖或者一個更重量級的鎖（X等）
• 在一個事務擷取一行的 X鎖之前，他必須先擷取表 t的 IX鎖。

　　這些規則可以總結為下面的圖表(橫向表示一個事務已經擷取了對應的鎖，縱向表示另外一個鎖想要擷取對應的鎖--這段話我自己加的)：

	X	IX	S	IS
X	衝突	衝突	衝突	衝突
IX	衝突	不衝突	衝突	不衝突
S	衝突	衝突	不衝突	不衝突
IS	衝突	不衝突	不衝突	不衝突

 

　　當行上的鎖與已經存在的鎖不衝突（相容）時，可以被事務請求擷取，如果衝突話，就不行。事務必須等待衝突的鎖被釋放（才能擷取想要的鎖）。如果一個加鎖請求與已存在的鎖相互衝突，又一直得不到鎖，可能是發生了死結，或者出現了錯誤。

　　因此，意圖鎖定不會阻塞除全表請求（例如，LOCK TABLES ...WAITE）之外其他請求。IX 和 IS鎖的主要目的是表明 某個請求（someone）已經鎖定了一行，或者將要鎖定一行記錄。

　　意圖鎖定加鎖過程中記錄的交易資料類似於下面使用 SHOW ENGINE INNODB STATUS語句的 InnoDB 監控輸出：

　　

TABLE LOCK TABLE ‘test.‘t‘ trx id 10080 lock mode IX

　　

  行鎖

　　行鎖是一個加在索引記錄上的鎖，例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE；會阻止其他任何事務插入，更新或者刪除那些 t.c1 = 10的記錄。

　　行鎖總是在索引記錄上面加鎖，即使一張表沒有任何索引，在這種情況下，InnoDB會建立一個隱藏的叢集索引，然後使用這個索引來加上行鎖。

　　行鎖加鎖過程中記錄的交易資料類似於下面使用 SHOW ENGINE INNODB STATUS語句的 InnoDB 監控輸出：

RECORDS LOCKS space id 58 page no 3 n bits 72 index ‘PRIMARY‘ of table ‘test‘.‘t‘trx id 10078 lock_mode X locks rec but not gapRecord lock ,heap on 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 8000000a; asc  ;;1: len 6; hex 00000000274f; asc  ‘O;;2: len 7; hex b60000019d0110; asc  ;;

 

  間隙鎖

　　間隙鎖是在索引記錄的間隙上加的鎖，或者在第一條索引記錄之前、最後一條索引記錄之後的區間上加的鎖。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE; 阻止所有其他的事務 插入一條 t.c1 = 15的記錄，無論是否表中已經有一列的值是15（或者其他10-20之間的值），因為在這個範圍內的間隙都已經被加上了鎖。

　　間隙可能只包括一個索引值，也可能包括多個索引值，甚至不包含任何索引值。

　　使用唯一索引尋找唯一一條記錄的語句是不會使用到間隙鎖的（這不包括查詢條件僅包含一個唯一索引的一部分的語句）。例如，假如 id 列有一個唯一索引，下面的語句只會使用行鎖加到 id = 100的行上面，而不會關心別的會話（session）是否在上述的間隙中插入資料。

SELECT * FROM child WHERE id = 100 ;  (感覺應該加一個 FOR UPDATE 或者 IN SHARE MODE 或者 資料庫隔離等級為 serializable)

 

　　如果 id 列沒有索引或者不是唯一索引，這個語句會在上述的間隙上加鎖。

　　值得提醒的是，不同的事務可以在間隙上擁有相互衝突的鎖。例如，事務 A 可以某一個間隙上的共用鎖定（gap S-lock）,同時另外一個事務可以在同一個間隙上擁有獨佔鎖（gap  X-lock）。原因在於如果一條索引記錄被刪除，其他事務擁有的對應間隙鎖必須進行歸併（強行翻譯，不太明白，原文：The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.）

　　間隙鎖是 ”完全禁止性的“（強行翻譯：purely inhibitive，看下文應該很好理解），意味著他們只會禁止其他的事務插入資料到間隙之中，而不會禁止別的事務擷取同一區間的間隙鎖。因此，間隙 X鎖和間隙 S鎖起到的效果是一樣的。

　　間隙鎖可以被明確的禁止。你可以將事務隔離等級設定為 READ COMMITTED 或者啟用 innodb_locks_unsafe_for_binlog 系統變數（現在不建議使用 deprecated）。在這些情況下，間隙鎖在查詢和索引掃描中被禁用，只會在外鍵約束檢查和重複索引檢查時才會使用。

　　使用READ COMMITTED 隔離等級和 啟用innodb_locks_unsafe_for_binlog系統變數也會造成一些額外的影響。在MySQL分析完 WHERE 條件之後，不匹配的行上面的行鎖會被釋放。對於 UPDATE 語句，InnoDB 進行一個”半一致性“讀，它會返回最新提交的版本（的行記錄）給MySQL,然後MySQL

　　確定哪些行匹配了 UPDATE 的 WHERE 條件。

　　

  Next-Key Locks

　　next-key 鎖是行鎖和在行鎖之前的間隙上的間隙鎖的聯合。

　　InnoDB以這種形式實現行級鎖，當他尋找或掃描表索引的時候，遇到匹配的的索引記錄，在上面加上對應的共用鎖定或者獨佔鎖。因此，行級鎖實際上是索引記錄鎖。next-key鎖同是會影響索引記錄之前的間隙。就是說，next-key lock就是一個索引記錄鎖加上一個在索引記錄之前的間隙上的間隙鎖。如果一個會話擁有記錄 R 的索引上面的一個共用鎖定或獨佔鎖，另外的會話無法立即在 R記錄索引順序之前的間隙上插入一條新的記錄。

　　假設有一個索引包含值10，11，13和20。下列的間隔上都可能加上一個next-key lock，（符號表示不包含端點，]符號表示包含端點。

(negative infinity ,10](10,11](11,13](13,20](20,positive infinity)

 

　　在最後一個區間中，next-key lock鎖定了索引中的最大值到一個“上確界”（一個虛假的索引記錄，他的值比所有索引記錄中所有值都要大）。上確界不是一個真實存在的索引記錄，所以，事實上，這個nex-key lock只是鎖定了最大索引值之後的區間。

　　預設情況下，InnoDB啟用 REPEATABLE READ 事務隔離等級。在這種情況下，InnoDB在尋找和掃描索引時會使用 nex-key lock，能避免 幻（影）行的出現。

　　next-key lock 加鎖過程中記錄的交易資料類似於下面使用 SHOW ENGINE INNODB STATUS語句的 InnoDB 監控輸出：

　　

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`trx id 10080 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 00: len 8; hex 73757072656d756d; asc supremum;;Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 00: len 4; hex 8000000a; asc ;;1: len 6; hex 00000000274f; asc ‘O;;2: len 7; hex b60000019d0110; asc ;;

 

 

  插入意圖鎖定

　　插入意圖鎖定

　

  AUTO-INC Locks（自增長鎖）

　　自增長鎖是特殊的表級鎖，被那些插入帶有AUTO_INCREMENT行的記錄到表中時佔用。一個最簡單的例子，當一個事務在插入資料到表中時，其他任何的事務都等待，因此，第一個事務插入的行能夠擁有連續的主索引值。

　　innodb_autoinc_lock_mode配置選項控制了自增長鎖使用的演算法。他允許你在可預測的自增長值和最大化並發插入操作之間進行權衡。

 

  空間索引的斷言鎖

　　InnoDB支援空間列上面的空間索引。

　　在處理包括空間索引的相關鎖定操作時，next-key locking 在支援 REPEATABLE READ 和 SERIALIZABLE 事務隔離等級 上工作的不太好。在多維的資料上，沒有一個絕對的順序，因此，“下一個”鍵在哪裡並不是很清楚。

　　為了支援帶有 空間索引的隔離等級，InnoDB使用了斷言鎖。空間索引包含了 最小外接矩陣（minimum bounding rectangle = MBR）值，所以InnoDB 強制對索引一致性讀的時候對查詢語句中使用的MBR值上設定一個斷言索引。其他的事務不能插入或者修改匹配這個查詢條件的行。

 

　　

 

MySQL鎖和事務（一）：InnoDB鎖（MySQL 官方文檔粗翻）