原理图解说
1 在innodb中，保存了一个全局事务链表，记录了活跃事务，即还未完成的事务
2 t2 时刻活跃的事务id为104～111，其中107已经完成
3 在t2时刻，读事务A查询数据，在查询范围内的事务id为100～114，需要判断事务id100～114那些是否可 见的
首先，先把当前活跃事务复制到自己空间，创建自己的readview,活跃的事务id有104～111，不包含107，107已经完成。
最后，读取数据，
读到事务id100～103，通过判断事务id<m_up_limit_id(104),则事务100～103可见
事务id107不在readview中，说明已经执行完成，则可见。
事务id104～106，108～111，在readview中，则不可见
在执行的过程中，新生成了112～114，大于m_low_limit_id（111），则不可见
4 在t3时刻，读事务B查询数，在查询范围内的事务id为100～114，需要判断事务id100～114那些是否可 见的
首先，先把当前活跃事务复制到自己空间，创建自己的readview,活跃的事务id有104～114，不包含107，112，107，112已经完成。
最后，读取数据，
读到事务id100～103，通过判断事务id<m_up_limit_id(104),则事务100～103可见
事务id107不在readview中，说明已经执行完成，则可见。
事务id104～106，108～111，113～114，在readview中，则不可见
其中在事务隔离级别 repeatable read，read committed 是通过readview来控制的
repeatable read 在一个事务中，仅复制一次活跃事务链表，即readview在一个事务中，是相同的
read committed 在一个事务中，每次查询都会复制一次活跃事务链表，即readview在一个事务中，在每次执行的sql语句中，都是不同的。
repeatable read 隔离级别，事务A，在t3时刻，readview不变，则判断就不bian
read committed 事务隔离级别，事务A，在t3时刻，重新复制活跃事务链表，readview 则变化，与事务B的readview 相同，读取的数据多了事务112
关键函数源码（mysql 5.7.32）
/*文件lock0lock.cc 判断一条记录是否可见的关键函数*/
360 bool
361 lock_clust_rec_cons_read_sees(
362 /*==========================*/
363 const rec_t* rec, /*!< in: user record which should be read or
364 passed over by a read cursor */
365 dict_index_t* index, /*!< in: clustered index */
366 const ulint* offsets,/*!< in: rec_get_offsets(rec, index) */
367 ReadView* view) /*!< in: consistent read view */
368 {
369 ut_ad(dict_index_is_clust(index));
370 ut_ad(page_rec_is_user_rec(rec));
371 ut_ad(rec_offs_validate(rec, index, offsets));
372
373 /* Temp-tables are not shared across connections and multiple
374 transactions from different connections cannot simultaneously
375 operate on same temp-table and so read of temp-table is
376 always consistent read. */
377 if (srv_read_only_mode || dict_table_is_temporary(index->table)) {
378 ut_ad(view == 0 || dict_table_is_temporary(index->table));
379 return(true);
380 }
381
382 /* NOTE that we call this function while holding the search
383 system latch. */
384 /*通过记录，查新当前记录的事务id*/
385 trx_id_t trx_id = row_get_rec_trx_id(rec, index, offsets);
386 /*readview 具体的判断方式*/
387 return(view->changes_visible(trx_id, index->table->name));
388 }
165 /** Check whether the changes by id are visible.
166 @param[in] id transaction id to check against the view
167 @param[in] name table name
168 @return whether the view sees the modifications of id. */
169 bool changes_visible(
170 trx_id_t id,
171 const table_name_t& name) const
172 MY_ATTRIBUTE((warn_unused_result))
173 {
174 ut_ad(id > 0);
175 /*如果当前事务id小于 readview在中最小的事务id或 自己的事务产生的数据则可见*/
176 if (id < m_up_limit_id || id == m_creator_trx_id) {
177
178 return(true);
179 }
180 /*校验事务id的有效性，在debug则中止，程序退出，如果release中，则写警告信息*/
181 check_trx_id_sanity(id, name);
182 /*如果当前事务id大于等于 readview在中最大的事务id 则不可见*/
183 if (id >= m_low_limit_id) {
184
185 return(false);
186 /*如果当前活跃事务链表为空 则可见，即没有活跃事务*/
187 } else if (m_ids.empty()) {
188
189 return(true);
190 }
191 /*获取当前readview中的内容*/
192 const ids_t::value_type* p = m_ids.data();
193 /*查询查询记录的事务id是否在readview中，查询到 返回true 可见，查询到 返回false不可见*/
194 return(!std::binary_search(p, p + m_ids.size(), id));
195 }
/*当前记录不可见时，通过此函数去判断是否历史版本可见查询此记录的undo日志*/
row_sel_build_prev_vers
本事务readview 的生成 ，当执行select 查询时，通过函数ReadView::copy_trx_ids 实现调用关系
#0 ReadView::copy_trx_ids (this=0x15ec3748, trx_ids=std::vector of length 1, capacity 2 = {...}) at /data/mysql-5.7.32/storage/innobase/read/read0read.cc:382
#1 0x00000000019aadbc in ReadView::prepare (this=0x15ec3748, id=0) at /data/mysql-5.7.32/storage/innobase/read/read0read.cc:462
#2 0x00000000019aa22f in MVCC::view_open (this=0x15e99278, view=@0x7fffd7bfeda8: 0x15ec3748, trx=0x7fffd7bfed08) at /data/mysql-5.7.32/storage/innobase/read/read0read.cc:599
#3 0x0000000001ac91bc in trx_assign_read_view (trx=0x7fffd7bfed08) at /data/mysql-5.7.32/storage/innobase/trx/trx0trx.cc:2277
#4 0x0000000001a38a23 in row_search_mvcc (buf=0x7ffdfc00fae8 "\377", mode=PAGE_CUR_G, prebuilt=0x7ffdfc0100e0, match_mode=0, direction=0) at /data/mysql-5.7.32/storage/innobase/row/row0sel.cc:5
117
#5 0x00000000018a890c in ha_innobase::index_read (this=0x7ffdfc00f7f0, buf=0x7ffdfc00fae8 "\377", key_ptr=0x7ffdfc018640 "\002", key_len=4, find_flag=HA_READ_AFTER_KEY) at /data/mysql-5.7.32/st
orage/innobase/handler/ha_innodb.cc:8769
#6 0x0000000000f1b60a in handler::index_read_map (this=0x7ffdfc00f7f0, buf=0x7ffdfc00fae8 "\377", key=0x7ffdfc018640 "\002", keypart_map=1, find_flag=HA_READ_AFTER_KEY) at /data/mysql-5.7.32/sq
l/handler.h:2824
#7 0x0000000000f0d1b7 in handler::ha_index_read_map (this=0x7ffdfc00f7f0, buf=0x7ffdfc00fae8 "\377", key=0x7ffdfc018640 "\002", keypart_map=1, find_flag=HA_READ_AFTER_KEY) at /data/mysql-5.7.32
/sql/handler.cc:3047
#8 0x0000000000f16ca3 in handler::read_range_first (this=0x7ffdfc00f7f0, start_key=0x7ffdfc00f8d8, end_key=0x0, eq_range_arg=false, sorted=false) at /data/mysql-5.7.32/sql/handler.cc:7415
#9 0x0000000000f14be5 in handler::multi_range_read_next (this=0x7ffdfc00f7f0, range_info=0x7ffe37873fc0) at /data/mysql-5.7.32/sql/handler.cc:6482
#10 0x0000000000f15aa0 in DsMrr_impl::dsmrr_next (this=0x7ffdfc00fa58, range_info=0x7ffe37873fc0) at /data/mysql-5.7.32/sql/handler.cc:6869
#11 0x00000000018ba912 in ha_innobase::multi_range_read_next (this=0x7ffdfc00f7f0, range_info=0x7ffe37873fc0) at /data/mysql-5.7.32/storage/innobase/handler/ha_innodb.cc:20585
#12 0x00000000016fe65a in QUICK_RANGE_SELECT::get_next (this=0x7ffdfc00e5c0) at /data/mysql-5.7.32/sql/opt_range.cc:11247
#13 0x0000000001438df5 in rr_quick (info=0x7ffdfc0169c0) at /data/mysql-5.7.32/sql/records.cc:405
#14 0x00000000014d394e in join_init_read_record (tab=0x7ffdfc016970) at /data/mysql-5.7.32/sql/sql_executor.cc:2504
#15 0x00000000014d0b25 in sub_select (join=0x7ffdfc007490, qep_tab=0x7ffdfc016970, end_of_records=false) at /data/mysql-5.7.32/sql/sql_executor.cc:1284
#16 0x00000000014d04b8 in do_select (join=0x7ffdfc007490) at /data/mysql-5.7.32/sql/sql_executor.cc:957
#17 0x00000000014ce41f in JOIN::exec (this=0x7ffdfc007490) at /data/mysql-5.7.32/sql/sql_executor.cc:206
#18 0x0000000001567ea7 in handle_query (thd=0x7ffdfc000bc0, lex=0x7ffdfc002ee0, result=0x7ffdfc007118, added_options=0, removed_options=0) at /data/mysql-5.7.32/sql/sql_select.cc:191
#19 0x000000000151d6e7 in execute_sqlcom_select (thd=0x7ffdfc000bc0, all_tables=0x7ffdfc006810) at /data/mysql-5.7.32/sql/sql_parse.cc:5155
#20 0x00000000015170f6 in mysql_execute_command (thd=0x7ffdfc000bc0, first_level=true) at /data/mysql-5.7.32/sql/sql_parse.cc:2826
#21 0x000000000151e6b1 in mysql_parse (thd=0x7ffdfc000bc0, parser_state=0x7ffe37875550) at /data/mysql-5.7.32/sql/sql_parse.cc:5584
#22 0x0000000001513f3b in dispatch_command (thd=0x7ffdfc000bc0, com_data=0x7ffe37875cb0, command=COM_QUERY) at /data/mysql-5.7.32/sql/sql_parse.cc:1491
#23 0x0000000001512da9 in do_command (thd=0x7ffdfc000bc0) at /data/mysql-5.7.32/sql/sql_parse.cc:1032
#24 0x00000000016451f8 in handle_connection (arg=0x15edb040) at /data/mysql-5.7.32/sql/conn_handler/connection_handler_per_thread.cc:313
#25 0x0000000001cd42de in pfs_spawn_thread (arg=0x15ff4830) at /data/mysql-5.7.32/storage/perfschema/pfs.cc:2197
#26 0x00007ffff7bc6dd5 in start_thread () from /lib64/libpthread.so.0
#27 0x00007ffff61b7ead in clone () from /lib64/libc.so.6
活跃事务复制函数（readview创建）
read0read.cc
376 /**
377 Copy the transaction ids from the source vector */
378 /*创建readview*/
379 void
380 ReadView::copy_trx_ids(const trx_ids_t& trx_ids)
381 {
/*得到当前全局事务链表的大小*/
382 ulint size = trx_ids.size();
383 /*如果当前事务id不为0，即已经有更新的sql语句执行，查询不分配事务id，如果把自己的删除掉*/
384 if (m_creator_trx_id > 0) {
385 ut_ad(size > 0);
386 --size;
387 }
388 /*如果全局事务链表为空，则不创建*/
389 if (size == 0) {
390 m_ids.clear();
391 return;
392 }
393 /*创建大小*/
394 m_ids.reserve(size);
395 m_ids.resize(size);
396
397 ids_t::value_type* p = m_ids.data();
398
399 /* Copy all the trx_ids except the creator trx id */
400 /*如果当前事务id已经分配，则需要把自己的事务id踢出*/
401 if (m_creator_trx_id > 0) {
402
403 /* Note: We go through all this trouble because it is
404 unclear whether std::vector::resize() will cause an
405 overhead or not. We should test this extensively and
406 if the vector to vector copy is fast enough then get
407 rid of this code and replace it with more readable
408 and obvious code. The code below does exactly one copy,
409 and filters out the creator's trx id. */
410 /*从trx_ids的begin位置到end-1位置二分查找第一个<=m_creator_trx_id的事务id*/
411 trx_ids_t::const_iterator it = std::lower_bound(
412 trx_ids.begin(), trx_ids.end(), m_creator_trx_id);
413
414 ut_ad(it != trx_ids.end() && *it == m_creator_trx_id);
415 /*得到小于当前事务id的活跃事务id的数量*/
416 ulint i = std::distance(trx_ids.begin(), it);
/*得到小于当前事务id的活跃事务id的总占用空间*/
417 ulint n = i * sizeof(trx_ids_t::value_type);
418 /*核心操作，小于当前事务id的活跃事务复制到当前事务空间m_ids中，形成自己的readview*/
419 ::memmove(p, &trx_ids[0], n);
420 /*再次查找看看是否还有大与当前事务的事务id*/
421 n = (trx_ids.size() - i - 1) * sizeof(trx_ids_t::value_type);
422
423 ut_ad(i + (n / sizeof(trx_ids_t::value_type)) == m_ids.size());
424 /*如果有，则添加到自己事务空间里，此两步操作主要时把自己的事务id踢出*/
425 if (n > 0) {
426 ::memmove(p + i, &trx_ids[i + 1], n);
427 }
428 } else {
429 ulint n = size * sizeof(trx_ids_t::value_type);
430 /*如果仅是只读事务，则把当前的全局事务id都复制到自己的空间m_ids*/
431 ::memmove(p, &trx_ids[0], n);
432 }
433 /*仅在debug模式中执行，校验事务的状态是否正确*/
434 #ifdef UNIV_DEBUG
435 /* Assert that all transaction ids in list are active. */
436 for (trx_ids_t::const_iterator it = trx_ids.begin();
437 it != trx_ids.end(); ++it) {
438
439 trx_t* trx = trx_get_rw_trx_by_id(*it);
440 ut_ad(trx != NULL);
441 ut_ad(trx->state == TRX_STATE_ACTIVE
442 || trx->state == TRX_STATE_PREPARED);
443 }
444 #endif /* UNIV_DEBUG */
445 }
/*查询语句执行完成后调用MVCC::view_close
ha_innobase::external_lock在函数 根据事务隔离级别， 是否调用 MVCC::view_close
*/
15994 int
15995 ha_innobase::external_lock(
15996 /*=======================*/
15997 THD* thd, /*!< in: handle to the user thread */
15998 int lock_type) /*!< in: lock type */
15999 {
.......
15822 if (trx->n_mysql_tables_in_use == 0) {
15823
15824 trx->mysql_n_tables_locked = 0;
15825 m_prebuilt->used_in_HANDLER = FALSE;
15826
15827 if (!thd_test_options(
15828 thd, OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) {
15829
15830 if (trx_is_started(trx)) {
15831
15832 innobase_commit(ht, thd, TRUE);
15833 } else {
15834 /* Since the trx state is TRX_NOT_STARTED,
15835 trx_commit() will not be called. Reset
15836 trx->is_dd_trx here */
15837 ut_d(trx->is_dd_trx = false);
15838 }
15839 /*如果隔离级别是Read uncommitted|Read committed 则判断成立，调用trx_sys->mvcc->view_close，清空trx->read_view
如果隔离级别是Repeatable read|Serializable 则不调用trx_sys->mvcc->view_close*/
15840 } else if (trx->isolation_level <= TRX_ISO_READ_COMMITTED
15841 && MVCC::is_view_active(trx->read_view)) {
15842
15843 mutex_enter(&trx_sys->mutex);
15844
15845 trx_sys->mvcc->view_close(trx->read_view, true);
15846
15847 mutex_exit(&trx_sys->mutex);
15848 }
15849 }
}
/*此函数根据trx->read_view（不同的隔离级别，不同）判断是否要调用trx_sys->mvcc->view_open，实现是否重新建立readview
隔离级别 Read uncommitted|Read committed trx->read_view 为NULL
隔离级别 Repeatable read|Serializable trx->read_view 不为NULL
*/
2264 ReadView*
2265 trx_assign_read_view(
2266 /*=================*/
2267 trx_t* trx) /*!< in/out: active transaction */
2268 {
2269 ut_ad(trx->state == TRX_STATE_ACTIVE);
2270
2271 if (srv_read_only_mode) {
2272
2273 ut_ad(trx->read_view == NULL);
2274 return(NULL);
2275
2276 } else if (!MVCC::is_view_active(trx->read_view)) {
/*如果本事务第二次执行，则事务隔离级别Read uncommitted|Read committed */
2277 trx_sys->mvcc->view_open(trx->read_view, trx);
2278 }
2279
2280 return(trx->read_view);
2281 }
#0 MVCC::view_close (this=0x15e99278, view=@0x7fffd7bff1f0: 0x15ec3428, own_mutex=true) at /data/mysql-5.7.32/storage/innobase/read/read0read.cc:748
#1 0x00000000018b54e4 in ha_innobase::external_lock (this=0x7ffdf000f940, thd=0x7ffdf0000b70, lock_type=2) at /data/mysql-5.7.32/storage/innobase/handler/ha_innodb.cc:15845
#2 0x0000000000f182bc in handler::ha_external_lock (this=0x7ffdf000f940, thd=0x7ffdf0000b70, lock_type=2) at /data/mysql-5.7.32/sql/handler.cc:8023
#3 0x00000000016d1a19 in unlock_external (thd=0x7ffdf0000b70, table=0x7ffdf000e9b8, count=1) at /data/mysql-5.7.32/sql/lock.cc:674
#4 0x00000000016d12ba in mysql_unlock_read_tables (thd=0x7ffdf0000b70, sql_lock=0x7ffdf000e9a0) at /data/mysql-5.7.32/sql/lock.cc:485
#5 0x000000000156de89 in JOIN::join_free (this=0x7ffdf0007440) at /data/mysql-5.7.32/sql/sql_select.cc:2581
#6 0x00000000014d0659 in do_select (join=0x7ffdf0007440) at /data/mysql-5.7.32/sql/sql_executor.cc:998
#7 0x00000000014ce41f in JOIN::exec (this=0x7ffdf0007440) at /data/mysql-5.7.32/sql/sql_executor.cc:206
#8 0x0000000001567ea7 in handle_query (thd=0x7ffdf0000b70, lex=0x7ffdf0002e90, result=0x7ffdf00070c8, added_options=0, removed_options=0) at /data/mysql-5.7.32/sql/sql_select.cc:191
#9 0x000000000151d6e7 in execute_sqlcom_select (thd=0x7ffdf0000b70, all_tables=0x7ffdf00067c0) at /data/mysql-5.7.32/sql/sql_parse.cc:5155
#10 0x00000000015170f6 in mysql_execute_command (thd=0x7ffdf0000b70, first_level=true) at /data/mysql-5.7.32/sql/sql_parse.cc:2826
#11 0x000000000151e6b1 in mysql_parse (thd=0x7ffdf0000b70, parser_state=0x7ffe37833550) at /data/mysql-5.7.32/sql/sql_parse.cc:5584
#12 0x0000000001513f3b in dispatch_command (thd=0x7ffdf0000b70, com_data=0x7ffe37833cb0, command=COM_QUERY) at /data/mysql-5.7.32/sql/sql_parse.cc:1491
#13 0x0000000001512da9 in do_command (thd=0x7ffdf0000b70) at /data/mysql-5.7.32/sql/sql_parse.cc:1032
#14 0x00000000016451f8 in handle_connection (arg=0x15edb040) at /data/mysql-5.7.32/sql/conn_handler/connection_handler_per_thread.cc:313
#15 0x0000000001cd42de in pfs_spawn_thread (arg=0x15ff4830) at /data/mysql-5.7.32/storage/perfschema/pfs.cc:2197
#16 0x00007ffff7bc6dd5 in start_thread () from /lib64/libpthread.so.0
#17 0x00007ffff61b7ead in clone () from /lib64/libc.so.6
2899
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
