MySQL 锁bug?

来源：这里教程网时间：2026-03-01 16:48:34 作者：

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 后面又做了一个测试，参考