插入意向鎖(Insert Intention Lock)

插入意向鎖本質(zhì)上可以看成是一個(gè)Gap Lock

• 普通的Gap Lock 不允許 在 （上一條記錄，本記錄） 范圍內(nèi)插入數(shù)據(jù)
• 插入意向鎖Gap Lock 允許 在 （上一條記錄，本記錄） 范圍內(nèi)插入數(shù)據(jù)

插入意向鎖的作用是為了提高并發(fā)插入的性能， 多個(gè)事務(wù) 同時(shí)寫入 不同數(shù)據(jù) 至同一索引范圍（區(qū)間）內(nèi)，并不需要等待其他事務(wù)完成，不會(huì)發(fā)生鎖等待。

插入的過程

假設(shè)現(xiàn)在有記錄 10， 30， 50， 70 ；且為主鍵 ，需要插入記錄 25 。

1. 找到 小于等于25的記錄 ，這里是 10
2. 找到 記錄10的下一條記錄 ，這里是 30
3. 判斷 下一條記錄30 上是否有鎖
   3.1 判斷 30 上面如果 沒有鎖 ，則可以插入
   3.2 判斷 30 上面如果有Record Lock，則可以插入
   3.3 判斷 30 上面如果有Gap Lock/Next-Key Lock，則無法插入，因?yàn)殒i的范圍是 (10, 30) /（10, 30] ；在30上增加insert intention lock（ 此時(shí)處于waiting狀態(tài)），當(dāng) Gap Lock / Next-Key Lock 釋放時(shí)，等待的事物（ transaction）將被 喚醒 ，此時(shí) 記錄30 上才能獲得 insert intention lock ，然后再插入 記錄25

注意：一個(gè)事物 insert 25 且沒有提交，另一個(gè)事物 delete 25 時(shí)，記錄25上會(huì)有 Record Lock

插入意向鎖演示

數(shù)據(jù)準(zhǔn)備

mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from a;
+----+
| b  |
+----+
| 10 |
| 11 |
| 13 |
| 20 |
+----+
4 rows in set (0.00 sec)

開啟兩個(gè)會(huì)話，兩個(gè)會(huì)話事務(wù)的隔離級(jí)別都設(shè)置為REPEATABLE-READ

此時(shí)執(zhí)行show engine innodb status\G語句會(huì)看到以下結(jié)果

---TRANSACTION 4424, ACTIVE 7 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140018685810432, query id 240 localhost root update
--等待插入的SQL
insert into a values(12)
------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:
--插入記錄12的事物等待中（被終端會(huì)話A中的事物阻塞了），等待獲得插入意向鎖(lock_mode X locks gap before rec insert intention waiting)
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

------------------
TABLE LOCK table `test`.`a` trx id 4424 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4424 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

---TRANSACTION 4423, ACTIVE 55 sec
2 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 2, OS thread handle 140018686076672, query id 241 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`a` trx id 4423 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4423 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000113f; asc      ?;;
 2: len 7; hex b3000001270110; asc     '  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b4000001280110; asc     (  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 000000001145; asc      E;;
 2: len 7; hex b70000012b0110; asc     +  ;;

此時(shí)事務(wù)B插入成功但是還未commit，再執(zhí)行show engine innodb status\G語句，會(huì)有以下輸出：

---TRANSACTION 4425, ACTIVE 26 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140018685810432, query id 247 localhost root
TABLE LOCK table `test`.`a` trx id 4425 lock mode IX
RECORD LOCKS space id 37 page no 3 n bits 72 index PRIMARY of table `test`.`a` trx id 4425 lock_mode X locks gap before rec insert intention
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000d; asc     ;;
 1: len 6; hex 000000001140; asc      @;;
 2: len 7; hex b400000128011c; asc     (  ;;

從上面的輸出可以看到在記錄13上面加了一把插入意圖鎖（lock_mode X locks gap before rec insert intention）。
獲得插入意圖鎖之后，我們就可以在11-13之間并發(fā)插入記錄，而不需要一個(gè)事物等待另一事物，當(dāng)所有相關(guān)的插入的事物都提交后， 13上的插入意向鎖 便會(huì)釋放。

自增鎖(AUTO-INC Locks)

在InnoDB中，每個(gè)含有自增列的表都有一個(gè)自增長(zhǎng)計(jì)數(shù)器。當(dāng)對(duì)含有自增長(zhǎng)計(jì)數(shù)器的表進(jìn)行插入時(shí)，首先會(huì)執(zhí)行select max(auto_inc_col) from t for update來得到計(jì)數(shù)器的值，然后再將這個(gè)值加1賦予自增長(zhǎng)列。我們將這種方式稱之為AUTO_INC Lock。

AUTO_INC Lock是一種特殊的表鎖，它在完成對(duì)自增長(zhǎng)值插入的SQL語句后立即釋放，所以性能會(huì)比事務(wù)完成后釋放鎖要高。由于是表級(jí)別的鎖，所以在并發(fā)環(huán)境下其依然存在性能問題。

從MySQL 5.1.22開始，InnoDB中提供了一種輕量級(jí)互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制，同時(shí)InnoDB存儲(chǔ)引擎提供了一個(gè)參數(shù)innodb_autoinc_lock_mode來控制自增長(zhǎng)的模式，進(jìn)而提高自增長(zhǎng)值插入的性能。innodb_autoinc_lock_mode和插入類型有關(guān)，在介紹它之前，我們先來看看都有哪些插入類型

• “INSERT-like” statements

  泛指所有的插入語句, 它包括 “simple-inserts”, “bulk-inserts”, 和 “mixed-mode inserts”.

• “Simple inserts”

  插入的記錄行數(shù)是確定的：比如：insert into values，replace
  但是不包括： INSERT ... ON DUPLICATE KEY UPDATE.

• “Bulk inserts”

  插入的記錄行數(shù)不能馬上確定的，比如： INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA

• “Mixed-mode inserts”

  這些都是simple-insert，但是部分auto increment值給定或者不給定. 例子如下(where c1 is an AUTO_INCREMENT column of table t1):

  INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  

  另外一種 “mixed-mode insert” 就是 INSERT ... ON DUPLICATE KEY UPDATE

介紹完插入類型之后，我們?cè)賮砜纯存i模式，即innodb_autoinc_lock_mode。