【MySQL】InnoDB鎖機制之二

一 前言
   之前的文章《InnoDB鎖機制之一》介紹了InnoDB鎖中的三種鎖:record lock, gap lock,next-key lock ，本文繼續介紹另外兩種鎖 Insert Intention Locks和AUTO-INC Locks
二 常見的鎖型別
2.1 根據鎖持有的時間粒度，分為
1. 記憶體級別：類似mutex，很快釋放
2. 語句級別：statement結束，釋放
3. 事務級別：transaction提交或者回滾才釋放
4. 會話級別：session級別，連線斷開才釋放

2.2  AUTO-INC lock
AUTO-INC lock是一個特殊的表級鎖，當一個事務向含有自增欄位的表插入資料時 ，該事務會獲取一個AUTO-INC lock，其他事務必須等待直到已經獲取鎖的insert 語句結束。因此,多個併發事務不能同時獲取同一個表上面的AUTO-INC lock,如果持有AUTO-INC鎖太長時間可能會影響到資料庫效能(比如INSERT INTO t1... SELECT ... FROM t2這類語句)或者死鎖.
鑑於AUTO-INC 鎖的特性,MySQL 5.1.22 透過新增引數 innodb_autoinc_lock_mode 來控制自增序列的演算法。該引數可以設定為0,1,2.
在學習innodb_autoinc_lock_mode之前，我們先了解insert語句的型別
1 Simple inserts
  能夠事先確定具體行數的insert語句，比如 insert into tab values()...(); replace 等等。 INSERT ... ON DUPLICATE KEY UPDATE和還有子查詢的insert 語句除外。
2 Bulk inserts
  和Simple inserts對立，事先不能確定插入行數的 insert/replace語句 ，insert ... select ;replace ...select; load data into table .. 這種情況下Innodb在執行具體的行的時候 會為每一行單獨分配一個auto_increment 值。
3  Mixed-mode inserts
  該情形是 Simple inserts 模式中，有些insert指定了 自增欄位的具體值，有些沒有指定。比如:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
INSERT ... ON DUPLICATE KEY UPDATE

接下來我們再看MySQL對auto_increment 的最佳化模式。
innodb_autoinc_lock_mode=0，是傳統的方式。InnoDB會在分配前給表加上AUTO_INC鎖，並在SQL結束時釋放掉。該模式保證了在STATEMENT複製模式下，備庫執行類似INSERT … SELECT這樣的語句時的一致性，因為這樣的語句在執行時無法確定到底有多少條記錄，只有在執行過程中不允許別的會話分配自增值，才能確保主備一致。
很顯然這種鎖模式非常影響併發插入的效能，但卻保證了一條SQL內自增值分配的連續性。
innodb_autoinc_lock_mode=1 ，這個是InnoDB的預設值。該模式下對於Simple inserts，InnoDB會先加一個 autoinc_mutex鎖，然後去判斷表上是否有別的執行緒加了LOCK_AUTO_INC鎖，如果有的話，釋放autoinc_mutex，並使用傳統的加鎖模式。否則，在預留本次插入需要的自增值之後，就快速的將autoinc_mutex釋放掉。很顯然，對於普通的併發INSERT操作，都是無需加LOCK_AUTO_INC鎖的。因此該模式提高了系統併發性；
innodb_autoinc_lock_mode=2，這種模式下只在分配時加個mutex即可，很快就釋放，不會像值為1那樣在某些場景下會退化到傳統模式。因此設為2不能保證批次插入的複製安全性。

2.3  Insert Intention Locks
     插入意向鎖是gap 鎖的一種，只是針對insert。當併發事務多條insert 插入同一個GAP,如果他們不是插入同一行記錄，會話之間並不會相互等待。例如索引記錄刪 有 12 ，17 兩個記錄，兩個會話同時插入記錄13，15，他們會分別為(12,17)加上GAP鎖，但相互之間並不衝突（因為插入的記錄不衝突）。
     當向某個資料頁中插入一條記錄時，總是會呼叫函式lock_rec_insert_check_and_lock進行鎖檢查（構建索引時的資料插入除外），會去檢查當前插入位置的下一條記錄上是否存在鎖物件，這裡的下一條記錄不是指的物理連續，而是按照邏輯順序的下一條記錄。
如果下一條記錄上不存在鎖物件：若記錄是二級索引上的，先更新二級索引頁上的最大事務ID為當前事務的ID；直接返回成功。
如果下一條記錄上存在鎖物件，就需要判斷該鎖物件是否鎖住了GAP。如果GAP被鎖住了，並判定和插入意向GAP鎖衝突，當前操作就需要等待，加的鎖型別為LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION，並進入等待狀態。但是插入意向鎖之間並不互斥。這意味著在同一個GAP裡可能有多個申請插入意向鎖的會話。

三 參考文章
[1] 官方文件 innodb-auto-increment-handling 
[2] MySQL · 引擎特性 · InnoDB 事務鎖系統簡介  --強烈推薦
[3] MySQL auto_increment實現