一、鎖的3種算法

InnoDB引擎有3種行鎖算法，分別是：

? • Record Lock ： 單個記錄上鎖；
? • Gap Lock : 間隙鎖，鎖定一個范圍，但不包含記錄本身；
? • Next-key Lock : Gap Lock+Record Lock，鎖定一個范圍并包含記錄本身。
Record Lock總是會鎖定索引記錄，如果建表是沒有設置索引，將使用隱式的主鍵來鎖定。InnoDB對行的查詢都是采用Next-key Lock算法。如果一個索引有10，11，13，20的值，那么可能鎖定的區間為
（-無窮大，10],(10,11],(11,13],(13,20],(20,+無窮大）
使用Next-key Lock是為了解決幻讀（Phantom Read）的問題。若事務T1已經鎖定了(10,11],(11,13],如果插入記錄12時，則鎖定的范圍會變為(10,11],(11,12],(12,13],當查詢的索引有唯一屬性時，會對Next-key Lock算法優化，降級為Record Lock，即鎖住索引本身，而不是范圍。

示例：
創建以下表：
CREATE TABLE t5(a INT PRIMARY KEY);
INSERT INTO t5 SELECT 1;
INSERT INTO t5 SELECT 2;
INSERT INTO t5 SELECT 5;
然后在會話A中執行以下SQL：
BEGIN;
SELECT * FROM t5 WHERE a=5 FOR UPDATE;
在會話B中執行以下SQL
BEGIN;
INSERT INTO t5 SELECT 4;
COMMIT;
可以看到順利地插入了，因為a中主鍵唯一索引，鎖的只是a=5這條記錄；最后把會話A commit;
再看看輔助索引的情況：
執行以下SQL:
CREATE TABLE t6(a INT,b INT ,PRIMARY KEY(a),KEY(b));
INSERT INTO t6 SELECT 1,1;
INSERT INTO t6 SELECT 3,1;
INSERT INTO t6 SELECT 5,3;
INSERT INTO t6 SELECT 7,6;
INSERT INTO t6 SELECT 10,8;
在會話A中執行以下SQL：
BEGIN;
SELECT * FROM t6 WHERE a=3 FOR UPDATE;
此時有兩個索引，需要分別鎖定，對于聚集索引，用Record Lock鎖定了列a=5的索引，對于輔助索引，加的是Next-key Lock，鎖定的是范圍(1,3)，注意的是InnoDB還會對輔助索引的下一個鍵值加Gap Lock，即范圍(3,6)也會被鎖定，因此以下的SQL將會阻塞：
SELECT * FROM t6 WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO t6 SELECT 4,2;
INSERT INTO t6 SELECT 6,5;
因為For Update加的是X鎖；執行以下SQL是正常的插入，沒有阻塞：
SELECT INTO t6 SELECT 8,6;
SELECT INTO t6 SELECT 2,0;
SELECT INTO t6 SELECT 6,7;

Gap Lock的作用是為防止并發時多個事務將記錄插入到同一范圍內，這樣會導致幻像問題，如會話A中已經鎖定了b=3的記錄，如果沒有Gap Lock鎖定（3，6），那么用戶可以插入索引為3的記錄，這樣會導致會話A的用戶再次執行時，會返回不同的記錄，導致幻像問題。
注意的是
可以通過以下的方法關閉Gap Lock
• 把事務隔離級別設置為READ COMMITED;
• 將參數innodb_locks_unsafe_for_binlog設置為1；

二、解決Phantom Problem

Phantom Problem是指在同一個事務中，連續執行同一條SQL得到不同的結果。

對于SELECT * FROM t WHERE a > 2 FOR UPDATE的SQL，鎖住的不僅是5這個值，而是對（2，+無窮大）范圍值因此對這個范圍內的插入都是不允許的，從而避免了Phantom Problem。

另外，用戶可以利用Next-Key Lock的機制在應用層實現唯一性的檢查。

SELECT * FROM table WHERE col_name=xxx LOCK IN SHARE MODE;
If not found any row:
#unique for insert value
INSERT INTO table VALUES(….);
如果用戶通過索引查詢一個值，并加了S鎖，即使查詢的值不存在，鎖定的也是一個范圍，因此若沒有任何返回行，那么新插入的值一定是唯一的。