MySQL rr下幻讀問題分析
在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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25719946/viewspace-2907799/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 什麼是鎖？深入分析解讀MySQL鎖，解決幻讀問題！MySql
- MySQL是怎麼解決幻讀問題的？MySql
- Phantom Problem（幻讀）問題
- 髒讀！幻讀！不可重複讀！mysql併發事務引發的問題MySql
- MySQL鎖問題分析-全域性讀鎖MySql
- MySQL：RR分析死鎖一列MySql
- MySQL:RR模式下死鎖一列MySql模式
- Innodb的RR到底有沒有解決幻讀？看不懂你打我！
- MySQL 透過 Next-Key Locking 技術（行鎖+間隙鎖）避免幻讀問題MySql
- MySQL 死鎖問題分析MySql
- MySQL:RR模式下insert也可能導致查詢慢MySql模式
- 資料庫系列：RR和RC下，快照讀的區別資料庫
- Mysql RC/RR隔離原理和區別 不可重複讀和可重複讀MySql
- 【Mysql】資料庫事務，髒讀、幻讀、不可重複讀MySql資料庫
- MySQL的可重複讀級別能解決幻讀嗎MySql
- MySQL鎖等待與死鎖問題分析MySql
- MySQL:關於RR模式下insert..selcet sending data狀態說明MySql模式
- MySQL 事務隔離實驗-認識：髒讀、不可重複讀、幻讀MySql
- RR與RC隔離級別下MySQL不同的加鎖解鎖方式MySql
- MySQL：讀取my.cnf的順序問題MySql
- MySQL 問題MySql
- 髒讀，幻讀，不可重複讀
- MySQL 主從切換延時高問題分析MySql
- 髒讀、幻讀和不可重複讀
- 解決Ubuntu下MySQL遠端登入問題UbuntuMySql
- MySQL複製效能優化和常見問題分析MySql優化
- Mysql DDL出現長時間等待MDL問題分析MySql
- 雲吞鋪子：RDS for MySQL CPU效能問題分析3MySql
- 雲吞鋪子：RDS for MySQL CPU效能問題分析2MySql
- 手把手教你分析解決MySQL死鎖問題MySql
- Mysql(MyISAM)的讀寫互斥鎖問題的解決方法MySql
- 面試官：MySQL的幻讀是怎麼被解決的？面試MySql
- mysql 1130 問題MySql
- Chaos 測試下的若干 NebulaGraph Raft 問題分析Raft
- MySQL主從不同步問題分析與處理思路MySql
- 線上MySQL讀寫分離，出現寫完讀不到問題如何解決MySql
- 如何避免MYSQL主從延遲帶來的讀寫問題？MySql
- 記一次 rr 和硬體斷點解決記憶體踩踏問題斷點記憶體