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
- 【MySQL】可重複讀下的幻讀MySql
- 髒讀！幻讀！不可重複讀！mysql併發事務引發的問題MySql
- MySQL：RR分析死鎖一列MySql
- MySQL鎖問題分析-全域性讀鎖MySql
- Innodb的RR到底有沒有解決幻讀？看不懂你打我！
- MySQL:RR模式下死鎖一列MySql模式
- Mysql 優化——分析表讀寫和sql效率問題MySql優化
- MySQL 死鎖問題分析MySql
- MySQL訪問受限的問題分析MySql
- 資料庫系列：RR和RC下，快照讀的區別資料庫
- MySQL 透過 Next-Key Locking 技術（行鎖+間隙鎖）避免幻讀問題MySql
- 【Mysql】資料庫事務，髒讀、幻讀、不可重複讀MySql資料庫
- MySQL:RR模式下insert也可能導致查詢慢MySql模式
- Mysql RC/RR隔離原理和區別 不可重複讀和可重複讀MySql
- MySQL的可重複讀級別能解決幻讀嗎MySql
- jboss下mysql的中文問題MySql
- Mysql加鎖過程詳解（3）-關於mysql 幻讀理解MySql
- Mysql加鎖過程詳解（2）-關於mysql 幻讀理解MySql
- MySQL 事務隔離實驗-認識：髒讀、不可重複讀、幻讀MySql
- MySQL鎖等待與死鎖問題分析MySql
- 讀mysql中文亂碼問題解決方法MySql
- MySQL5.5加主鍵鎖讀問題MySql
- 髒讀，幻讀，不可重複讀
- MySQL在RR隔離級別下的unique失效和死鎖模擬MySql
- RR與RC隔離級別下MySQL不同的加鎖解鎖方式MySql
- mac下mysql無法啟動問題MacMySql
- 面試官：MySQL的幻讀是怎麼被解決的？面試MySql
- MySQL：讀取my.cnf的順序問題MySql
- 解決JSP讀寫MYSQL亂碼問題JSMySql
- MySQL:關於RR模式下insert..selcet sending data狀態說明MySql模式
- 髒讀、不可重複讀、幻讀區別
- MySQL 問題MySql
- MySQL 主從切換延時高問題分析MySql
- MySQL RR隔離級別的更新衝突策略MySql
- MySQL下的安全問題–.mysql_history你注意到了嗎？MySql
- 解決Ubuntu下MySQL遠端登入問題UbuntuMySql