mysql> select * from child;
+-----+
| id  |
+-----+
|   2 |
| 102 |
+-----+
2 rows in set (4.00 sec)
mysql> show create table child;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

執行sql1

select * from child where id >=2 and id <=102 for update;

的時候，發現2之前的範圍都加上了鎖

執行sql2

select * from child where id >2 and id <=102 for update;

這個不會加

分析下程式碼,下面是sql1的堆疊

mysqld!row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0sel.cc:5185)
mysqld!ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:8753)
mysqld!ha_innobase::index_first(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:9171)
mysqld!handler::ha_index_first(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:3200)
mysqld!join_read_first(QEP_TAB*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:2662)
mysqld!sub_select(JOIN*, QEP_TAB*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:1284)
mysqld!do_select(JOIN*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:957)
mysqld!JOIN::exec() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:206)
mysqld!handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_select.cc:191)
mysqld!execute_sqlcom_select(THD*, TABLE_LIST*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5155)
mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:2826)
mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584)
mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491)
mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032)
mysqld!::handle_connection(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/conn_handler/connection_handler_per_thread.cc:313)
mysqld!::pfs_spawn_thread(void *) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/perfschema/pfs.cc:2197)
libsystem_pthread.dylib!_pthread_start (Unknown Source:0)
libsystem_pthread.dylib!thread_start (Unknown Source:0)

下面是sql2的堆疊

mysqld!row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/row/row0sel.cc:5185)
mysqld!ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:8753)
mysqld!handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.h:2818)
mysqld!handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:3046)
mysqld!handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:7411)
mysqld!handler::multi_range_read_next(char**) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:6476)
mysqld!DsMrr_impl::dsmrr_next(char**) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:6868)
mysqld!ha_innobase::multi_range_read_next(char**) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:20587)
mysqld!QUICK_RANGE_SELECT::get_next() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/opt_range.cc:11247)
mysqld!rr_quick(READ_RECORD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/records.cc:405)
mysqld!join_init_read_record(QEP_TAB*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:2504)
mysqld!sub_select(JOIN*, QEP_TAB*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:1284)
mysqld!do_select(JOIN*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:957)
mysqld!JOIN::exec() (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_executor.cc:206)
mysqld!handle_query(THD*, LEX*, Query_result*, unsigned long long, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_select.cc:191)
mysqld!execute_sqlcom_select(THD*, TABLE_LIST*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5155)
mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:2826)
mysqld!mysql_parse(THD*, Parser_state*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:5584)
mysqld!dispatch_command(THD*, COM_DATA const*, enum_server_command) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1491)
mysqld!do_command(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:1032)

可以看到2個sql走了不同的執行計劃，導致執行了不同的函式，在index_first函式中，

寫死了導致了掃描方式是大於的方式，定位的第一條記錄是無窮小的偽紀錄，

進而導致了2之前的範圍加上了範圍鎖。sql2定位的第一條記錄是2，所以2之前沒有加上範圍鎖，

這個不知道是不是bug。正常的理解上看sql1記錄2之前是不應該有範圍鎖的。

QUICK_RANGE_SELECT 中設定了範圍的最小，最大值，設定的最小key就是2

後面又做了一個測試，參考

MySQL 唯一索引範圍查詢鎖下一個記錄的理解