Gap lock： 对索引项之间的间隙、第一条记录之前的间隙或最后一条记录后的间隙加锁
如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁。实际效果跟表锁一样

https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_gap_lock

• gap

A place in an InnoDB index data structure where new values could be inserted. 

When you lock a set of rows with a statement such as SELECT ... FOR UPDATE, InnoDB can create locks that apply to the gaps as well as the actual values in the index. For example, if you select all values greater than 10 for update, a gap lock prevents another transaction from inserting a new value that is greater than 10. 

The supremum record and infimum record represent the gaps containing all values greater than or less than all the current index values.

• gap lock

A lock on a gap between index records, or a lock on the gap before the first or after the last index record. 

For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into the column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked. Contrast with record lock and next-key lock.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

• insert intention lock

A type of gap lock that is set by INSERT operations prior to row insertion. 

This type of 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. 

• next-key lock

A combination of a record lock on the index record and a gap lock on the gap before the index record.

• supremum record

A pseudo-record in an index, representing the gap above the largest value in that index. 

If a transaction has a statement such as SELECT ... FROM ... WHERE col > 10 FOR UPDATE;, and the largest value in the column is 20, it is a lock on the supremum record that prevents other transactions from inserting even larger values such as 50, 100, and so on.

• infimum record

A pseudo-record in an index, representing the gap below the smallest value in that index. 

If a transaction has a statement such as SELECT ... FROM ... WHERE col < 10 FOR UPDATE;, and the smallest value in the column is 5, it is a lock on the infimum record that prevents other transactions from inserting even smaller values such as 0, -10, and so on.

• 在不通过索引条件查询时，InnoDB会锁定表中的所有记录

mysql> CREATE TABLE table_no_index (id INT, name VARCHAR(10)) engine=innodb;
Query OK, 0 ROWS affected (1.90 sec)
 
mysql> INSERT INTO table_no_index VALUES (1, '1'), (2, '2'), (3, '3'), (4, '4');
Query OK, 4 ROWS affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM table_no_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
4 ROWS IN SET (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | VALUE           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 ROW IN SET (0.01 sec)
 
mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 ROW IN SET (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_no_index where id = 1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_no_index where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from table_no_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.05 sec)

mysql> select * from table_no_index where id = 2 for update;
    等待。。。
    

session1 执行了 select * from table_no_index where id = 1 for update; 之后，由于表没有索引，所以InnoDB会对所有记录加锁。

• 通过查看锁日志，看到如下内容 index GENCLUSTINDEX of table lock_test.table_no_index

---TRANSACTION 2257931, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 433603, OS thread handle 140481613293312, query id 9351394 10.10.133.115 root Sending data
select * from table_no_index where id = 2 for update
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7957 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`table_no_index` trx id 2257931 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000009458e; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed000001830110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 31; asc 1;;

------------------
TABLE LOCK table `lock_test`.`table_no_index` trx id 2257931 lock mode IX
RECORD LOCKS space id 7957 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`table_no_index` trx id 2257931 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000009458e; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed000001830110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 31; asc 1;;

---TRANSACTION 2257929, ACTIVE 88 sec
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 432541, OS thread handle 140481603176192, query id 9351395 10.10.133.115 root starting
show engine innodb status
Trx read view will not see trx with id >= 2257929, sees < 2257929
TABLE LOCK table `lock_test`.`table_no_index` trx id 2257929 lock mode IX
RECORD LOCKS space id 7957 page no 3 n bits 72 index GEN_CLUST_INDEX of table `lock_test`.`table_no_index` trx id 2257929 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000009458e; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed000001830110; asc        ;;
 3: len 4; hex 80000001; asc     ;;
 4: len 1; hex 31; asc 1;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 00000009458f; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed00000183011f; asc        ;;
 3: len 4; hex 80000002; asc     ;;
 4: len 1; hex 32; asc 2;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000094590; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed00000183012e; asc       .;;
 3: len 4; hex 80000003; asc     ;;
 4: len 1; hex 33; asc 3;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 6; hex 000000094591; asc     E ;;
 1: len 6; hex 000000227403; asc    "t ;;
 2: len 7; hex ed00000183013d; asc       =;;
 3: len 4; hex 80000004; asc     ;;
 4: len 1; hex 34; asc 4;;

mysql> CREATE TABLE table_with_index (id INT, name VARCHAR(10)) engine=innodb;
Query OK, 0 ROWS affected (1.90 sec)
 
mysql> ALTER TABLE table_with_index ADD INDEX id(id);
Query OK, 0 ROWS affected (1.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> INSERT INTO table_with_index VALUES (1, '1'), (2, '2'), (3, '3'), (4, '4');
Query OK, 4 ROWS affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM table_with_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
4 ROWS IN SET (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | VALUE           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 ROW IN SET (0.01 sec)
 
mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 ROW IN SET (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_with_index where id = 1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_with_index where id = 2;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

mysql> select * from table_with_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.05 sec)

mysql> select * from table_with_index where id = 2 for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.05 sec)
    

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果使用相同的索引键，是会出现锁冲突的。

mysql> SHOW CREATE TABLE table_with_index;
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE            | CREATE TABLE                                                                                                                                                |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_with_index | CREATE TABLE `table_with_index` (
  `id` INT(11) DEFAULT NULL,
  `name` VARCHAR(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 ROW IN SET (0.00 sec)
 
mysql> INSERT INTO table_with_index VALUES (1, '4');
Query OK, 1 ROW affected (0.01 sec)
 
mysql> SELECT * FROM table_with_index WHERE id = 1;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
2 ROWS IN SET (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | VALUE           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 ROW IN SET (0.01 sec)
 
mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 ROW IN SET (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_with_index where id = 1 and name = '1' for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+
1 row in set (0.05 sec)

虽然session2访问的是和session1不同的记录，但是因为使用了相同的索引，所以还是需要等待锁
mysql> select * from table_with_index where id = 1 and name = '4' for update;
等待...

mysql> ALTER TABLE table_with_index ADD INDEX name(name);
Query OK, 0 ROWS affected (1.16 sec)
 
mysql> SELECT * FROM table_with_index;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
|    2 | 2    |
|    3 | 3    |
|    4 | 4    |
+------+------+
4 ROWS IN SET (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | VALUE           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 ROW IN SET (0.01 sec)
 
mysql> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.16-log |
+------------+
1 ROW IN SET (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from table_with_index where id = 1 for update;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
|    1 | 4    |
+------+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- session2使用name索引访问记录，因为记录没有被加锁，所以可以获得锁
mysql> select * from table_with_index where name = '2' for update;
+------+------+
| id   | name |
+------+------+
|    2 | 2    |
+------+------+
1 row in set (0.00 sec)

-- 由于访问的记录已经被session1 锁定，所以需要等待获取锁
mysql> select * from table_with_index where name = '4' for update;
等待...

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，他就不会使用索引。这种情况下InnoDB也会对所有记录加锁。因此，在分析锁冲突的时候，需要检查SQL的执行计划，确保是否真的使用到了索引。