# 5.6版本
[root@yejr.run]> select version();
+------------+
| version()  |
+------------+
| 5.6.39-log |
+------------+

#隔离级别
[root@yejr.run]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+

#表数据
[root@yejr.run]> select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
|  0 |  0 |  0 |  0 |
|  1 |  1 |  1 |  0 |
|  3 |  3 |  3 |  0 |
|  4 |  2 |  2 |  0 |
+----+----+----+----+

#表结构&索引，c1是主键（有唯一属性），c2是辅助索引
[root@yejr.run]> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(10) unsigned NOT NULL DEFAULT '0',
  `c2` int(10) unsigned NOT NULL DEFAULT '0',
  `c3` int(10) unsigned NOT NULL DEFAULT '0',
  `c4` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB;

[root@yejr.run]> select * from INNODB_LOCKs\G

InnoDB: An unnecessary next key lock was taken when performing 
a SELECT...FOR [SHARE|UPDATE] query with a WHERE condition that 
specifies a range, causing one too many rows to be locked. The 
most common occurrences of this issue have been addressed so 
that only rows and gaps that intersect the searched range are 
locked. (Bug #29508068)

简言之：就是不再需要对不必要的数据上锁啦。

select ENGINE_LOCK_ID,ENGINE_TRANSACTION_ID,THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks;
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| ENGINE_LOCK_ID                    | ENGINE_TRANSACTION_ID | THREAD_ID | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+
| 4868124032:1127:140327172372248   |                 18983 |       351 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| 4868124032:44:4:4:140327176578584 |                 18983 |       351 | t1          | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
| 4868123176:1127:140327172370216   |                 18982 |       350 | t1          | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| 4868123176:44:4:2:140327176573976 |                 18982 |       350 | t1          | PRIMARY    | RECORD    | X             | GRANTED     | 0         |
| 4868123176:44:4:3:140327176573976 |                 18982 |       350 | t1          | PRIMARY    | RECORD    | X             | GRANTED     | 1         |
+-----------------------------------+-----------------------+-----------+-------------+------------+-----------+---------------+-------------+-----------+

commit d1b0afd75ee669f54b70794eb6dab6c121f1f179
Author: Jakub Łopuszański <jakub.lopuszanski@oracle.com>
Date:   Wed Jul 17 16:34:01 2019 +0200

    Bug #29508068       UNNECESSARY NEXT-KEY LOCK TAKEN

    When doing a SELECT...FOR [SHARE|UPDATE] with a WHERE condition specifying a range,
    we were locking "one row too much".
    This patch fixes locking behaviour in several (hopefuly) most common cases, so that
    we only lock rows and gaps which intersect the searched range.

    - Added MTR to demonstrate current locking policy for end of range
    - Got rid of goto
    - Extracted logic of determining relation between range and row to separate function
    - Extracted reoccuring patterns of modifications of search_tuple so it is easier to add same for stop_tuple
    - Added prebuilt->m_stop_tuple and made sure it is in sync with prebuilt->m_mysql_handler->end_range for during read_range_first() and read_range_next()
    - Added row_can_be_in_range field
    - Do not lock the row (just the gap) if the row is same length and after the stop_tuple
    - Do not lock the row (just the gap) if the row is same length and equal to stop_tuple and strict inequality was used for end of range
    - Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is after the stop_tuple
    - Do not lock the row (just the gap) if the row is longer than stop_tuple and its prefix is equal to stop_tuple and strict inequality was used for end of range
    - Do not lock the row nor gap if we already saw a row same length and equal to stop_tuple in previous iteration

    Reviewed-by: Pawel Olchawa <pawel.olchawa@oracle.com>
    RB:22293