MySQL 5.7中锁的一个通用问题

创建表
mysql> create table tt(a int not null primary key) engine=innodb;
mysql> insert into tt values(10),(20),(30),(40),(50);

复现这个问题可以参考：
session1:
mysql> set session tx_isolation='repeatable-read';
mysql> begin;
mysql> select * from tt where a > 15 and a < 35 for update;
+----+
| a  |
+----+
| 20 |
| 30 |
+----+

session2:
mysql>  insert into tt select 1;
此时这个操作会被阻塞，如果你按照这个思路来看，总是会感觉不对劲。
怎么MySQL这么矫情了。
我带着疑问在新搭建的一套MySQL 5.7环境上做了测试，结果还真是。
接下来的任务就是如何说服我，然后我理解了来说服这个网友。

#session1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select *from tt;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec)

mysql> select * from tt where a =10 for update;
+----+
| a  |
+----+
| 10 |
+----+
1 row in set (0.00 sec)

#session2
mysql> insert into tt select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

mysql> select * from tt where a <30 and a>10 for update;
+----+
| a  |
+----+
| 20 |
+----+
1 row in set (0.00 sec)

session2:
mysql> insert into tt select 35;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tt select 31;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tt select 30;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into tt select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into tt select 10;
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql> insert into tt select 11;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

这里可以明显感觉到和当前环境的数据分布关系很微妙，范围只有一个20，但是似乎和0有着一定的联系，至少，我不能保证我的查询一定得按照这个精确的范围。

session1:
mysql> select *from tt;
+----+
| a  |
+----+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
+----+
5 rows in set (0.00 sec)
mysql> begin;select * from tt where a <35 and a>15 for update;
Query OK, 0 rows affected (0.00 sec)

+----+
| a  |
+----+
| 20 |
| 30 |
+----+
2 rows in set (0.00 sec)

session2:
mysql>  insert into tt select 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 9;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 10;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 35;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 36;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 40;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 50;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

session1
mysql> begin;select * from tt where a <30 and a>15 for update;
Query OK, 0 rows affected (0.00 sec)

+----+
| a  |
+----+
| 20 |
+----+
1 row in set (0.00 sec)

session2
mysql>  insert into tt select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>  insert into tt select 15;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 15;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 16;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 14;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 13;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 11;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 10;
ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'
mysql>  insert into tt select 9;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql>  insert into tt select 30;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>  insert into tt select 31;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings:

mysql> select * from INFORMATION_SCHEMA.innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 4081:36:3:2
lock_trx_id: 4081
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: PRIMARY
 lock_space: 36
  lock_page: 3
   lock_rec: 2
  lock_data: 10
*************************** 2. row ***************************
    lock_id: 4078:36:3:2
lock_trx_id: 4078
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`tt`
 lock_index: PRIMARY
 lock_space: 36
  lock_page: 3
   lock_rec: 2
  lock_data: 10
2 rows in set, 1 warning (0.00 sec)

Hi!
I have run your test case and got the same results as you have.
Upon further analysis, I concluded that this is a bug.  A small bug , but a bug.
Verified.
而有看点的是问题的提出者定位到了相关的代码，还是希望文档的部分能够把间隙锁的部分补充一下。
No locks are released in this case, but we do request X lock on the gap before the next, non-matching record when non-unique secondary index is used. Check code starting from this line (https://github.com/mysql/mysql-server/blob/71f48ab393bce80a59e5a2e498cd1f46f6b43f9a/storag...):
			/* Try to place a gap lock on the next index record
			to prevent phantoms in ORDER BY ... DESC queries */
			const rec_t*	next_rec = page_rec_get_next_const(rec);

			offsets = rec_get_offsets(next_rec, index, offsets,
						  ULINT_UNDEFINED, &heap);
			err = sel_set_rec_lock(pcur,
					       next_rec, index, offsets,
					       prebuilt->select_lock_type,
LOCK_GAP, thr, &mtr);

in row_search_mvcc(). See the (potential) reason to set this gap lock in the comment above.
Maybe there is another reason for the behavior we see. Then it should be also documented.