在mysql中，针对幻读的问题，有个争论多的，链接如下： 这个说的是第一个事务 select，第二个事务插入一条记录提交，第一个事务update，然后在select，会出现幻读，没有实现一个事务中所有的select都是相同的纪录session 1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from ab; Empty set (0.00 sec) mysql> select * from ab; Empty set (0.00 sec) mysql> update ab set b=2 where a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from ab; +---+------+ | a | b | +---+------+ | 1 | 2 | +---+------+ 1 row in set (7 min 28.78 sec) session 2: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into ab values(1,1); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) 下面在源码中看下原因 第一个事务在开始select的时候，没有分配事务id ,所以m_creator_trx_id=0 然后在update的时候，会去更新这个m_creator_trx_id，这样在后续的select中，read_view就能看到这个update做的修改了 堆栈如下：
mysqld!ReadView::creator_trx_id(unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/include/read0types.h:295) mysqld!MVCC::set_view_creator_trx_id(ReadView*, unsigned long long) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/read/read0read.cc:788) mysqld!trx_set_rw_mode(trx_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/trx/trx0trx.cc:3244) mysqld!lock_table(unsigned long, dict_table_t*, lock_mode, que_thr_t*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:4043) 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:5054) 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!mysql_update(THD*, List<Item>&, List<Item>&, unsigned long long, enum_duplicates, unsigned long long*, unsigned long long*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:819) mysqld!Sql_cmd_update::try_single_table_update(THD*, bool*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:2906) mysqld!Sql_cmd_update::execute(THD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_update.cc:3037) mysqld!mysql_execute_command(THD*, bool) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/sql_parse.cc:3616) 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)
更新后，再次select 的堆栈
mysqld!ReadView::changes_visible(unsigned long long, table_name_t const&) const (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/include/read0types.h:176) mysqld!lock_clust_rec_cons_read_sees(unsigned char const*, dict_index_t*, unsigned long const*, ReadView*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/lock/lock0lock.cc:387) 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:5634) 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!ha_innobase::rnd_next(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/storage/innobase/handler/ha_innodb.cc:9269) mysqld!handler::ha_rnd_next(unsigned char*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/handler.cc:2962) mysqld!rr_sequential(READ_RECORD*) (/Users/xiaoyu.bai/Downloads/mysql-5.7.29/sql/records.cc:517) 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) 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)
在changes_visible中id==m_creator_trx_id 返回true，看到对应的更新记录
bool changes_visible(
trx_id_t id,
const table_name_t& name) const
MY_ATTRIBUTE((warn_unused_result))
{
ut_ad(id > 0);
if (id < m_up_limit_id || id == m_creator_trx_id) {
return(true);
}
有兴趣学习源码的加群一起学习啊 QQ： 700072075
