InnoDB’s gap locks

MySQL uses REPEATABLE READ as the default isolation level so it needs to lock the index records and the gaps to avoid phantom reads and to get a consistent Statement based replication. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED will help you to avoid all those extra locks. As a final advice, keep your transactions short

What is a gap lock?
A gap lock is a lock on the gap between index records. Thanks to this gap lock, when you run the same query twice, you get the same result, regardless other session modifications on that table. This makes reads consistent and therefore makes the replication between servers consistent. If you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock and the gaps between them with a shared gap lock.
This lock doesn’t only affect to SELECT … FOR UPDATE. This is an example with a DELETE statement:
transaction1 > SELECT * FROM t;
+------+
| age |
+------+
| 21 |
| 25 |
| 30 |
+------+
Start a transaction and delete the record 25:
transaction1 > START TRANSACTION;
transaction1 > DELETE FROM t WHERE age=25;
At this point we suppose that only the record 25 is locked. Then, we try to insert another value on the second session:
transaction2 > START TRANSACTION;
transaction2 > INSERT INTO t VALUES(26);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(29);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(23);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
transaction2 > INSERT INTO t VALUES(31);
Query OK, 1 row affected (0.00 sec)
After running the delete statement on the first session, not only the affected index record has been locked but also the gap before and after that record with a shared gap lock preventing the insertion of data to other sessions.
How to troubleshoot gap locks?
Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS:
---TRANSACTION 72C, ACTIVE 755 sec
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox
TABLE LOCK table test.t trx id 72C lock mode IX
RECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X
RECORD LOCKS space id 19 page no 3 n bits 80 index GEN_CLUST_INDEX of table test.ttrx id 72C lock_mode X locks rec but not gap
RECORD LOCKS space id 19 page no 4 n bits 80 index age of table test.t trx id 72C lock_mode X locks gap before rec
If you have lot of gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways:
1- Change the ISOLATION level to READ COMMITTED. In this isolation level, it is normal and expected that query results can change during a transaction, so there is no need to create locks to prevent that from happening.
2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking.
The most important difference between these two options is that the second one is a global variable that affects all sessions and needs a server restart to change its value. Both options cause phantom reads (non repeatable reads) so in order to prevent problems with the replication you should change the binary log format to “row”.
With READ COMMITTED InnoDB doesn’t take additional locks on the gap, improving the concurrence. You would avoid all “Lock wait timeout exceeded” messages in my example. So only row with value 25 would be locked, not everything between 21 and 30, including the gap. Compare this example with the following one using READ COMMITTED:

session1> delete from t where i=25;
Query OK, 1 row affected (0.00 sec)
session2> insert into t values(26);
Query OK, 1 row affected (0.00 sec)
session1> commit;
session2> commit;
session1> select * from t;
+——+
| i |
+——+
| 21 |
| 26 |
| 30 |
+——+

No locks on the gap, so no wait timeout and more concurrence.

以前不了解MYSQL中的索引锁的概念, 最近参照着mysql官方文档做了一下小实验,来了解索引锁的用途.
Gap lock (范围锁)
    This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. 摘自MySql白皮书

     范围锁属于索引锁,也是记录锁, 锁住的是索引中的记录以及记录相关的范围

     举例: 
     (1) 开启客户端A(又称事务A), 使用如下语句创建测试表格, 其中a为主键, b为索引:
     CREATE TABLE a (a int(11) NOT NULL DEFAULT '0',  b int(11) DEFAULT NULL,  PRIMARY KEY (a),  KEY indx_b (b)) ENGINE=InnoDB;
     (2)  事务A 中导入数据
                 insert into a(a,b) select 1,1;
                 insert into a(a,b) select 3,1;
                 insert into a(a,b) select 5,3;
                 insert into a(a,b) select 6,5;
                 insert into a(a,b) select 7,6;
                 insert into a(a,b) select 10,8;
      (3)  事务A 中设置自动提交false.  set autocommit=0;
      (4)  事务A 中删除数据但不提交. delete from a where b = 3;
      (5) 开启客户端B(又称事务B), 并设置自动提交为false: set autocommit=0;
      (6)  事务B 中查询a表数据为初始数据, 因为事务A为非自动提交,所以事务A中的delete操作还未影响到其他事务的查询.
mysql> select * from a;
+----+---+
| a  | b |
+----+---+
|  1 | 1 |
|  3 | 1 |
|  5 | 3 |
|  6 | 5 |
|  7 | 6 |
| 10 | 8 |
+----+---+
6 rows in set

     (7) 事务B中执行insert操作, 执行失败, 原因是出现了锁等待(lock wait), 说明被事务A阻塞. 可以通过INNODB_TRX表查询到被锁住的线程情况. 
-- 插入b=2的记录的SQL语句被阻塞, 说明事务A可能锁住了b∈(-∞, 3)开区间
mysql> insert into a(a,b) select 2,2;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

mysql> SELECT trx_id, trx_state, trx_mysql_thread_id
, trx_query, trx_operation_state, trx_isolation_level
, trx_adaptive_hash_timeout
FROM  information_schema.INNODB_TRX ;
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| trx_id | trx_state | trx_mysql_thread_id | trx_query                     | trx_operation_state | trx_isolation_level | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| 13257  | LOCK WAIT |                  32 | insert into a(a,b) select 2,2 | inserting           | REPEATABLE READ     |                     10000 |
| 13231  | RUNNING   |                  33 | NULL                          | NULL                | REPEATABLE READ     |                     10000 |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
2 rows in set
mysql> 

         -- 插入b=1的记录成功, 结合b=2的SQL可以看出事务A只锁住了b∈(1, 3)的开区间,而不是区间(-∞, 3)
mysql> insert into a(a,b) select 11,1;
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

-- 插入b=3的SQL语句被阻塞, 说明事务A锁住了b=3的记录
mysql> insert into a(a,b) select 2,3;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

mysql> SELECT trx_id, trx_state, trx_mysql_thread_id
, trx_query, trx_operation_state, trx_isolation_level
, trx_adaptive_hash_timeout
FROM  information_schema.INNODB_TRX ;
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| trx_id | trx_state | trx_mysql_thread_id | trx_query                     | trx_operation_state | trx_isolation_level | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| 13257  | LOCK WAIT |                  32 | insert into a(a,b) select 2,3 | inserting           | REPEATABLE READ     |                     10000 |
| 13231  | RUNNING   |                  33 | NULL                          | NULL                | REPEATABLE READ     |                     10000 |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
2 rows in set
mysql> 

          -- 插入b=4的记录的SQL语句被锁住, 说明事务A锁住了b∈(3, 5)开区间
          mysql> insert into a(a,b) select 2,4;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

mysql> SELECT trx_id, trx_state, trx_mysql_thread_id
, trx_query, trx_operation_state, trx_isolation_level
, trx_adaptive_hash_timeout
FROM  information_schema.INNODB_TRX ;
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| trx_id | trx_state | trx_mysql_thread_id | trx_query                     | trx_operation_state | trx_isolation_level | trx_adaptive_hash_timeout |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
| 13257  | LOCK WAIT |                  32 | insert into a(a,b) select 2,4 | inserting           | REPEATABLE READ     |                     10000 |
| 13231  | RUNNING   |                  33 | NULL                          | NULL                | REPEATABLE READ     |                     10000 |
+--------+-----------+---------------------+-------------------------------+---------------------+---------------------+---------------------------+
2 rows in set
mysql> 

小结: 以上的测试借助delete一条数据(delete from a where b = 3)模拟索引锁的问题, 范围锁锁住了记录本身以及紧邻记录前后的两个区间,即: (1, 3) ∪ {3} ∪ (3, 5)

以下使用SQL: delete from a where b > 3 and b <=6来测试删除区间数据的情况.
(8) 客户端A  B均执行回退并提交 : rollback; commit;
(9) 事务A中执行删除语句但不提交: delete from a where b > 3 and b <=6. 现象如下:
mysql> delete from a where b > 3 and b <= 6;
Query OK, 2 rows affected

mysql> select * from a;
+----+---+
| a  | b |
+----+---+
|  1 | 1 |
|  3 | 1 |
|  5 | 3 |
| 10 | 8 |
+----+---+
4 rows in set

mysql> 

(10) 事务B中执行以下语句现象如下:
mysql> select * from a;
+----+---+
| a  | b |
+----+---+
|  1 | 1 |
|  3 | 1 |
|  5 | 3 |
|  6 | 5 |
|  7 | 6 |
| 10 | 8 |
+----+---+
6 rows in set

mysql> delete from a where b = 3;
Query OK, 1 row affected

mysql> delete from a where b = 6;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> delete from a where b = 5;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> 

小结: 删除区间数据,索引锁锁住的区间就是所删除的区间, 即 (3, 6] 前开后闭区间.

以上删除的是普通的索引情况, 当删除唯一索引会是什么情况呢?
(11) 客户端A  B均执行回退并提交 : rollback; commit;
(12) 重复步骤(7)中的操作, 并将where中的字段b用字段a取代, 发现范围锁对于唯一索引失效,也就是说对于唯一索引所加记录锁被降级为记录锁.

小结: 当索引中含有唯一索引, 并且索引仅包含一列时, 如果SQL锁定的是一条记录,那么该SQL将不适用范围锁, 而是降级为记录锁. 
看来非唯一索引上的锁之所以是一个区间锁, 是怕插入了索引相同的记录,导致一致性查询失效.
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.) 

Insertion Intention gap lock(插入意向范围锁) 
-- gap lock 也是一种范围锁
-- 为了提高插入性能而设置的锁
This 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.
插入意向锁在多个并行事务同时向相同的索引范围内插入数据不会出现彼此等待的情况.所以提高了插入数据的性能

Record Lock (记录锁)
    This is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes 摘自MySql白皮书
    记录锁锁住的是索引中的单行记录. 在innoDB存储引擎中, 系统会为无索引的表创建一个隐式的索引

Next-key Lock 
    This is a combination of a record lock on the index record and a gap lock on the gap before the index record.InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters 摘自MySql白皮书
     该锁是为了解决幻读(phantom)问题而设置的锁, 是记录锁与范围锁的组合锁.  主要针对于"读"SQL而设计的锁, 对于DML无效

    小结: 插入意向锁(insertion intention gap lock)是针对于insert语句而设计的锁, 而next-key lock是针对于select语句设计的锁.
    Gap locks in InnoDB are “purely inhibitive”, which means they only stop other transactions from inserting to the gap. Thus, a gap X-lock has the same effect as a gap S-lock.