Replace into加鎖的探究

本文對一個(gè)案例的有趣的地方進(jìn)行分析狰挡，然后最后再綜述下replace into的流程裙椭。
基礎(chǔ)環(huán)境

image.png

進(jìn)行一個(gè)Replace操作驴娃，唯一索引沖突削饵，SQL執(zhí)行完后事務(wù)持有的鎖岩瘦。

image.png

Insert常規(guī)的加鎖參看這個(gè)文章：http://www.reibang.com/p/d945753a4f15
這里面有幾個(gè)有趣的地方：

主鍵上為什么會(huì)在4、5上加GAP鎖窿撬。
為什么唯一索引會(huì)在(7,7)上加Next Key鎖启昧。
為什么唯一索引會(huì)在(6,4)上有Gap鎖。

先看看這個(gè)SQL執(zhí)行期間都做了什么：
1.首先因?yàn)橹麈I上沒有沖突劈伴，主鍵上插入成功密末。
2.然后插入二級(jí)索引，既唯一索引b宰啦。先對索引(6,6)加X|LOCK_ORDINARY鎖,然后因?yàn)?6,6)這條記錄產(chǎn)生了DB_DUPLICATE_KEY錯(cuò)誤苏遥。
3.Server層收到DB_DUPLICATE_KEY錯(cuò)誤饼拍，需要把第一步插入的聚簇索引回滾掉赡模，這個(gè)時(shí)候需要給(4,6)這個(gè)記錄加上一個(gè)LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖。
4.加鎖完成后师抄，對(4,6)記錄進(jìn)行樂觀刪除btr_cur_optimistic_delete漓柑，刪除導(dǎo)致(4,6)上的鎖發(fā)生了繼承，變?yōu)長OCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的鎖叨吮，落在(5,5)記錄上辆布。
5.接下來再根據(jù)沖突的唯一索引去定位(row_search_mvcc)，對記錄(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖茶鉴，因?yàn)橹耙呀?jīng)持有了X|LOCK_ORDINARY锋玲，所以不需加新鎖。
6.根據(jù)唯一索引定位的主鍵記錄涵叮，進(jìn)行必要的加鎖惭蹂，對主鍵上的記錄(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖。
7.因?yàn)槲ㄒ凰饕齜是表的最后一個(gè)唯一索引(如果發(fā)生uk沖突的索引是最后一個(gè)唯一索引割粮、沒有外鍵引用盾碗、且不存在delete trigger時(shí)，使用UPDATE ROW的方式來解決沖突http://mysql.taobao.org/monthly/2015/03/01/)舀瓢。首先對(6,6)這條記錄設(shè)置delete mark廷雅。
8.然后插入(4,6)這條記錄,進(jìn)行意向鎖(LOCK_X|LOCK_GAP|LOCK_INSERT_INTENTION=2563)兼容監(jiān)測,監(jiān)測通過。
9.執(zhí)行(4,6)記錄的插入
10.插入(4,6),導(dǎo)致本事務(wù)在(5,5)上持有的GAP鎖分裂。因此在(4,6)記錄上加個(gè)LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)鎖航缀。
11.接下來對唯一索引b上的(6,6)進(jìn)行加鎖LOCK_REC_NOT_GAP商架，因?yàn)楸臼聞?wù)已經(jīng)在該記錄上持有了LOCK_ORDINARY，所以不需要重新加鎖谬盐。
12.對唯一索引b上的(6,6)進(jìn)行delete mark甸私。
13.進(jìn)行唯一索引(6,4)記錄的插入，因?yàn)槲ㄒ凰饕?6,6)的記錄已經(jīng)被設(shè)置為delete mark飞傀，所以會(huì)給(7,7)加上X|LOCK_ORDINARY(3+0=3)的鎖
14.執(zhí)行唯一索引(6,4)記錄的插入
15.唯一索引(6,4)記錄的插入皇型，導(dǎo)致(7,7)上的LOCK_ORDINARY的鎖發(fā)生了分裂，(6,4)記錄上多了LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的鎖砸烦。
調(diào)用棧

1.首先因?yàn)橹麈I上沒有沖突弃鸦，主鍵上插入成功。
row_ins_index_entry(dict_index_t*, dtuple_t*, que_thr_t*) row0ins.cc:3437
row_ins_index_entry_step(ins_node_t*, que_thr_t*) row0ins.cc:3587
row_ins(ins_node_t*, que_thr_t*) row0ins.cc:3725
row_ins_step(que_thr_t*) row0ins.cc:3861
row_insert_for_mysql_using_ins_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1746
row_insert_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1866
ha_innobase::write_row(unsigned char*) ha_innodb.cc:7612
handler::ha_write_row(unsigned char*) handler.cc:8093
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1538
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
2.然后插入二級(jí)索引幢痘，既唯一索引b
先對索引(6,6)加X|LOCK_ORDINARY鎖,然后因?yàn)?6,6)這條記錄產(chǎn)生了DB_DUPLICATE_KEY錯(cuò)誤唬格。
row_ins_scan_sec_index_for_duplicate(unsigned long, dict_index_t*, dtuple_t*, que_thr_t*, bool, mtr_t*, mem_block_info_t*) row0ins.cc:2152
row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t*, mem_block_info_t*, mem_block_info_t*, dtuple_t*, unsigned long long, que_thr_t*, bool) row0ins.cc:3040
row_ins_sec_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool) row0ins.cc:3388
row_ins_index_entry(dict_index_t*, dtuple_t*, que_thr_t*) row0ins.cc:3439
row_ins_index_entry_step(ins_node_t*, que_thr_t*) row0ins.cc:3587
row_ins(ins_node_t*, que_thr_t*) row0ins.cc:3725
row_ins_step(que_thr_t*) row0ins.cc:3861
row_insert_for_mysql_using_ins_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1746
row_insert_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1866
ha_innobase::write_row(unsigned char*) ha_innodb.cc:7612
handler::ha_write_row(unsigned char*) handler.cc:8093
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1538
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
3.Server層收到DB_DUPLICATE_KEY錯(cuò)誤，需要把第一步插入的聚簇索引回滾掉颜说，這個(gè)時(shí)候需要給(4,6)這個(gè)記錄加上一個(gè)LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖购岗。
參看row_convert_impl_to_expl_if_needed的注釋:
    /* In case of partial rollback implicit lock on the
    record is released in the middle of transaction, which
    can break the serializability of IODKU and REPLACE
    statements. Normal rollback is not affected by this
    becasue we release the locks after the rollback. So
    to prevent any other transaction modifying the record
    in between the partial rollback we convert the implicit
    lock on the record to explict. When the record is actually
    deleted this lock be inherited by the next record.  */
row_convert_impl_to_expl_if_needed(btr_cur_t*, undo_node_t*) row0undo.cc:392
row_undo_ins_remove_clust_rec(undo_node_t*) row0uins.cc:147
row_undo_ins(undo_node_t*, que_thr_t*) row0uins.cc:519
row_undo(undo_node_t*, que_thr_t*) row0undo.cc:335
row_undo_step(que_thr_t*) row0undo.cc:420
que_thr_step(que_thr_t*) que0que.cc:1055
que_run_threads_low(que_thr_t*) que0que.cc:1119
que_run_threads(que_thr_t*) que0que.cc:1159
trx_rollback_to_savepoint_low(trx_t*, trx_savept_t*) trx0roll.cc:126
trx_rollback_to_savepoint(trx_t*, trx_savept_t*) trx0roll.cc:167
row_mysql_handle_errors(dberr_t*, trx_t*, que_thr_t*, trx_savept_t*) row0mysql.cc:775
row_insert_for_mysql_using_ins_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1759
row_insert_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1866
ha_innobase::write_row(unsigned char*) ha_innodb.cc:7612
handler::ha_write_row(unsigned char*) handler.cc:8093
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1538
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
4.加鎖完成后，對(4,6)記錄進(jìn)行樂觀刪除btr_cur_optimistic_delete门粪，刪除導(dǎo)致(4,6)上的鎖發(fā)生了繼承喊积，變?yōu)長OCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的鎖，落在(5,5)記錄上玄妈。
lock_rec_add_to_queue(unsigned long, buf_block_t const*, unsigned long, dict_index_t*, trx_t*, bool) lock0lock.cc:1849
lock_rec_inherit_to_gap(buf_block_t const*, buf_block_t const*, unsigned long, unsigned long) lock0lock.cc:2695
lock_update_delete(buf_block_t const*, unsigned char const*) lock0lock.cc:3603
btr_cur_optimistic_delete_func(btr_cur_t*, unsigned long, mtr_t*) btr0cur.cc:5136
row_undo_ins_remove_clust_rec(undo_node_t*) row0uins.cc:149
row_undo_ins(undo_node_t*, que_thr_t*) row0uins.cc:519
row_undo(undo_node_t*, que_thr_t*) row0undo.cc:335
row_undo_step(que_thr_t*) row0undo.cc:420
que_thr_step(que_thr_t*) que0que.cc:1055
que_run_threads_low(que_thr_t*) que0que.cc:1119
que_run_threads(que_thr_t*) que0que.cc:1159
trx_rollback_to_savepoint_low(trx_t*, trx_savept_t*) trx0roll.cc:126
trx_rollback_to_savepoint(trx_t*, trx_savept_t*) trx0roll.cc:167
row_mysql_handle_errors(dberr_t*, trx_t*, que_thr_t*, trx_savept_t*) row0mysql.cc:775
row_insert_for_mysql_using_ins_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1759
row_insert_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:1866
ha_innobase::write_row(unsigned char*) ha_innodb.cc:7612
handler::ha_write_row(unsigned char*) handler.cc:8093
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1538
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
5.接下來再根據(jù)沖突的唯一索引去定位(row_search_mvcc)乾吻，對記錄(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖，因?yàn)橹耙呀?jīng)持有了X|LOCK_ORDINARY拟蜻，所以不需加新鎖绎签。
lock_rec_lock_slow(unsigned long, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:1985
lock_rec_lock(bool, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:2069
lock_sec_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, lock_mode, unsigned long, que_thr_t*) lock0lock.cc:6349
sel_set_rec_lock(btr_pcur_t*, unsigned char const*, dict_index_t*, unsigned long const*, unsigned long, unsigned long, que_thr_t*, mtr_t*) row0sel.cc:1275
row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) row0sel.cc:5529
ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) ha_innodb.cc:8753
handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) handler.h:2818
handler::index_read_idx_map(unsigned char*, unsigned int, unsigned char const*, unsigned long, ha_rkey_function) handler.cc:7621
handler::ha_index_read_idx_map(unsigned char*, unsigned int, unsigned char const*, unsigned long, ha_rkey_function) handler.cc:3098
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1642
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
6.根據(jù)唯一索引定位的主鍵記錄，進(jìn)行必要的加鎖酝锅，對主鍵上的記錄(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的鎖诡必。
lock_rec_add_to_queue(unsigned long, buf_block_t const*, unsigned long, dict_index_t*, trx_t*, bool) lock0lock.cc:1817
lock_rec_lock_slow(unsigned long, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:2008
lock_rec_lock(bool, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:2069
lock_clust_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, lock_mode, unsigned long, que_thr_t*) lock0lock.cc:6422
row_sel_get_clust_rec_for_mysql(row_prebuilt_t*, dict_index_t*, unsigned char const*, que_thr_t*, unsigned char const**, unsigned long**, mem_block_info_t**, dtuple_t const**, mtr_t*) row0sel.cc:3652
row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long) row0sel.cc:5777
ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) ha_innodb.cc:8753
handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function) handler.h:2818
handler::index_read_idx_map(unsigned char*, unsigned int, unsigned char const*, unsigned long, ha_rkey_function) handler.cc:7621
handler::ha_index_read_idx_map(unsigned char*, unsigned int, unsigned char const*, unsigned long, ha_rkey_function) handler.cc:3098
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1642
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
7.因?yàn)槲ㄒ凰饕齜是表的最后一個(gè)唯一索引(如果發(fā)生uk沖突的索引是最后一個(gè)唯一索引、沒有外鍵引用搔扁、且不存在delete trigger時(shí)爸舒，使用UPDATE ROW的方式來解決沖突http://mysql.taobao.org/monthly/2015/03/01/)。首先對(6,6)這條記錄設(shè)置delete mark阁谆。
rec_set_deleted_flag_new(unsigned char*, page_zip_des_t*, unsigned long) rem0rec.ic:765
btr_rec_set_deleted_flag(unsigned char*, page_zip_des_t*, unsigned long) btr0cur.ic:236
btr_cur_del_mark_set_clust_rec(unsigned long, buf_block_t*, unsigned char*, dict_index_t*, unsigned long const*, que_thr_t*, dtuple_t const*, mtr_t*) btr0cur.cc:4844
row_upd_clust_rec_by_insert(unsigned long, upd_node_t*, dict_index_t*, que_thr_t*, unsigned long, mtr_t*) row0upd.cc:2546
row_upd_clust_step(upd_node_t*, que_thr_t*) row0upd.cc:2979
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3054
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
8.然后插入(4,6)這條記錄,進(jìn)行意向鎖(LOCK_X|LOCK_GAP|LOCK_INSERT_INTENTION=2563)兼容監(jiān)測,監(jiān)測通過碳抄。
lock_rec_insert_check_and_lock(unsigned long, unsigned char const*, buf_block_t*, dict_index_t*, que_thr_t*, mtr_t*, unsigned long*) lock0lock.cc:5991
btr_cur_ins_lock_and_undo(unsigned long, btr_cur_t*, dtuple_t*, que_thr_t*, mtr_t*, unsigned long*) btr0cur.cc:2977
btr_cur_optimistic_insert(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:3217
row_ins_clust_index_entry_low(unsigned long, unsigned long, dict_index_t*, unsigned long, dtuple_t*, unsigned long, que_thr_t*, bool) row0ins.cc:2612
row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, unsigned long, bool) row0ins.cc:3299
row_upd_clust_rec_by_insert(unsigned long, upd_node_t*, dict_index_t*, que_thr_t*, unsigned long, mtr_t*) row0upd.cc:2590
row_upd_clust_step(upd_node_t*, que_thr_t*) row0upd.cc:2979
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3054
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
9.執(zhí)行(4,6)記錄的插入
page_cur_insert_rec_low(unsigned char*, dict_index_t*, unsigned char const*, unsigned long*, mtr_t*) page0cur.cc:1354
page_cur_tuple_insert(page_cur_t*, dtuple_t const*, dict_index_t*, unsigned long**, mem_block_info_t**, unsigned long, mtr_t*, bool) page0cur.ic:287
btr_cur_optimistic_insert(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:3224
row_ins_clust_index_entry_low(unsigned long, unsigned long, dict_index_t*, unsigned long, dtuple_t*, unsigned long, que_thr_t*, bool) row0ins.cc:2612
row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, unsigned long, bool) row0ins.cc:3299
row_upd_clust_rec_by_insert(unsigned long, upd_node_t*, dict_index_t*, que_thr_t*, unsigned long, mtr_t*) row0upd.cc:2590
row_upd_clust_step(upd_node_t*, que_thr_t*) row0upd.cc:2979
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3054
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
10.插入(4,6),導(dǎo)致本事務(wù)在(5,5)上持有的GAP鎖分裂。因此在(4,6)記錄上加個(gè)LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)鎖场绿。
lock_rec_add_to_queue(unsigned long, buf_block_t const*, unsigned long, dict_index_t*, trx_t*, bool) lock0lock.cc:1843
lock_rec_inherit_to_gap_if_gap_lock(buf_block_t const*, unsigned long, unsigned long) lock0lock.cc:2737
lock_update_insert(buf_block_t const*, unsigned char const*) lock0lock.cc:3569
btr_cur_optimistic_insert(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:3287
row_ins_clust_index_entry_low(unsigned long, unsigned long, dict_index_t*, unsigned long, dtuple_t*, unsigned long, que_thr_t*, bool) row0ins.cc:2612
row_ins_clust_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, unsigned long, bool) row0ins.cc:3299
row_upd_clust_rec_by_insert(unsigned long, upd_node_t*, dict_index_t*, que_thr_t*, unsigned long, mtr_t*) row0upd.cc:2590
row_upd_clust_step(upd_node_t*, que_thr_t*) row0upd.cc:2979
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3054
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
11.接下來對唯一索引b上的(6,6)進(jìn)行加鎖LOCK_REC_NOT_GAP剖效，因?yàn)楸臼聞?wù)已經(jīng)在該記錄上持有了LOCK_ORDINARY，所以不需要重新加鎖。
lock_rec_lock_slow(unsigned long, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:1985
lock_rec_lock(bool, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:2069
lock_sec_rec_modify_check_and_lock(unsigned long, buf_block_t*, unsigned char const*, dict_index_t*, que_thr_t*, mtr_t*) lock0lock.cc:6246
btr_cur_del_mark_set_sec_rec(unsigned long, btr_cur_t*, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:4973
row_upd_sec_index_entry(upd_node_t*, que_thr_t*) row0upd.cc:2305
row_upd_sec_step(upd_node_t*, que_thr_t*) row0upd.cc:2374
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3083
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
12.對唯一索引b上的(6,6)進(jìn)行delete mark璧尸。
btr_rec_set_deleted_flag(unsigned char*, page_zip_des_t*, unsigned long) btr0cur.ic:235
btr_cur_del_mark_set_sec_rec(unsigned long, btr_cur_t*, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:4995
row_upd_sec_index_entry(upd_node_t*, que_thr_t*) row0upd.cc:2305
row_upd_sec_step(upd_node_t*, que_thr_t*) row0upd.cc:2374
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3083
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
13.進(jìn)行唯一索引(6,4)記錄的插入咒林，因?yàn)槲ㄒ凰饕?6,6)的記錄已經(jīng)被設(shè)置為delete mark，所以會(huì)給(7,7)加上X|LOCK_ORDINARY(3+0=3)的鎖
lock_rec_lock(bool, unsigned long, buf_block_t const*, unsigned long, dict_index_t*, que_thr_t*) lock0lock.cc:2048
lock_sec_rec_read_check_and_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, lock_mode, unsigned long, que_thr_t*) lock0lock.cc:6349
row_ins_set_exclusive_rec_lock(unsigned long, buf_block_t const*, unsigned char const*, dict_index_t*, unsigned long const*, que_thr_t*) row0ins.cc:1525
row_ins_scan_sec_index_for_duplicate(unsigned long, dict_index_t*, dtuple_t*, que_thr_t*, bool, mtr_t*, mem_block_info_t*) row0ins.cc:2123
row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t*, mem_block_info_t*, mem_block_info_t*, dtuple_t*, unsigned long long, que_thr_t*, bool) row0ins.cc:3040
row_ins_sec_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool) row0ins.cc:3388
row_upd_sec_index_entry(upd_node_t*, que_thr_t*) row0upd.cc:2347
row_upd_sec_step(upd_node_t*, que_thr_t*) row0upd.cc:2374
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3083
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
14.執(zhí)行唯一索引(6,4)記錄的插入
page_cur_tuple_insert(page_cur_t*, dtuple_t const*, dict_index_t*, unsigned long**, mem_block_info_t**, unsigned long, mtr_t*, bool) page0cur.ic:269
btr_cur_optimistic_insert(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:3224
row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t*, mem_block_info_t*, mem_block_info_t*, dtuple_t*, unsigned long long, que_thr_t*, bool) row0ins.cc:3132
row_ins_sec_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool) row0ins.cc:3388
row_upd_sec_index_entry(upd_node_t*, que_thr_t*) row0upd.cc:2347
row_upd_sec_step(upd_node_t*, que_thr_t*) row0upd.cc:2374
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3083
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b
15.唯一索引(6,4)記錄的插入爷光，導(dǎo)致(7,7)上的LOCK_ORDINARY的鎖發(fā)生了分裂垫竞，(6,4)記錄上多了LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的鎖。
lock_rec_add_to_queue(unsigned long, buf_block_t const*, unsigned long, dict_index_t*, trx_t*, bool) lock0lock.cc:1849
lock_rec_inherit_to_gap_if_gap_lock(buf_block_t const*, unsigned long, unsigned long) lock0lock.cc:2737
lock_update_insert(buf_block_t const*, unsigned char const*) lock0lock.cc:3569
btr_cur_optimistic_insert(unsigned long, btr_cur_t*, unsigned long**, mem_block_info_t**, dtuple_t*, unsigned char**, big_rec_t**, unsigned long, que_thr_t*, mtr_t*) btr0cur.cc:3287
row_ins_sec_index_entry_low(unsigned long, unsigned long, dict_index_t*, mem_block_info_t*, mem_block_info_t*, dtuple_t*, unsigned long long, que_thr_t*, bool) row0ins.cc:3132
row_ins_sec_index_entry(dict_index_t*, dtuple_t*, que_thr_t*, bool) row0ins.cc:3388
row_upd_sec_index_entry(upd_node_t*, que_thr_t*) row0upd.cc:2347
row_upd_sec_step(upd_node_t*, que_thr_t*) row0upd.cc:2374
row_upd(upd_node_t*, que_thr_t*) row0upd.cc:3083
row_upd_step(que_thr_t*) row0upd.cc:3200
row_update_for_mysql_using_upd_graph(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2582
row_update_for_mysql(unsigned char const*, row_prebuilt_t*) row0mysql.cc:2672
ha_innobase::update_row(unsigned char const*, unsigned char*) ha_innodb.cc:8257
handler::ha_update_row(unsigned char const*, unsigned char*) handler.cc:8134
write_record(THD*, TABLE*, COPY_INFO*, COPY_INFO*) sql_insert.cc:1831
Sql_cmd_insert::mysql_insert(THD*, TABLE_LIST*) sql_insert.cc:776
Sql_cmd_insert::execute(THD*) sql_insert.cc:3134
mysql_execute_command(THD*, bool) sql_parse.cc:3606
mysql_parse(THD*, Parser_state*) sql_parse.cc:5584
dispatch_command(THD*, COM_DATA const*, enum_server_command) sql_parse.cc:1491
do_command(THD*) sql_parse.cc:1032
::handle_connection(void *) connection_handler_per_thread.cc:313
::pfs_spawn_thread(void *) pfs.cc:2197
_pthread_start 0x00007fff6d3d3109
thread_start 0x00007fff6d3ceb8b

https://www.whitewood.me/2017/08/06/%E5%B9%B6%E5%8F%91replace%20into%E5%AF%BC%E8%87%B4MySQL%E6%AD%BB%E9%94%81/
http://mysql.taobao.org/monthly/2015/03/01/
http://mysql.taobao.org/monthly/2020/09/06/

最后編輯于：2021.04.05 14:40:55

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末蛀序，一起剝皮案震驚了整個(gè)濱河市欢瞪，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌徐裸，老刑警劉巖遣鼓，帶你破解...
沈念sama閱讀 212,383評論 6贊 493
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異重贺，居然都是意外死亡骑祟，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,522評論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門气笙，熙熙樓的掌柜王于貴愁眉苦臉地迎上來次企，“玉大人，你說我怎么就攤上這事潜圃「卓茫” “怎么了？”我有些...
開封第一講書人閱讀 157,852評論 0贊 348
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵秉犹，是天一觀的道長蛉谜。經(jīng)常有香客問我稚晚，道長崇堵，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 56,621評論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任客燕，我火速辦了婚禮鸳劳，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘也搓。我一直安慰自己赏廓，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 65,741評論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布傍妒。她就那樣靜靜地躺著幔摸，像睡著了一般。火紅的嫁衣襯著肌膚如雪颤练。梳的紋絲不亂的頭發(fā)上既忆，一...
開封第一講書人閱讀 49,929評論 1贊 290
城市分裂傳說
那天，我揣著相機(jī)與錄音，去河邊找鬼患雇。笑死跃脊，一個(gè)胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的苛吱。我是一名探鬼主播酪术，決...
沈念sama閱讀 39,076評論 3贊 410
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼翠储！你這毒婦竟也來了绘雁？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,803評論 0贊 268
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤援所，失蹤者是張志新（化名）和其女友劉穎咧七，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體任斋，經(jīng)...
沈念sama閱讀 44,265評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡继阻，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,582評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了废酷。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片瘟檩。...
茶點(diǎn)故事閱讀 38,716評論 1贊 341
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖澈蟆，靈堂內(nèi)的尸體忽然破棺而出墨辛，到底是詐尸還是另有隱情，我是刑警寧澤趴俘，帶...
沈念sama閱讀 34,395評論 4贊 333
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布睹簇，位于F島的核電站，受9級(jí)特大地震影響寥闪，放射性物質(zhì)發(fā)生泄漏太惠。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 40,039評論 3贊 316
男人毒藥：我在死后第九天來索命
文/蒙蒙一疲憋、第九天我趴在偏房一處隱蔽的房頂上張望凿渊。院中可真熱鬧，春花似錦缚柳、人聲如沸埃脏。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,798評論 0贊 21
一樁弒父案秋忙，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽彩掐。三九已至，卻和暖如春灰追，著一層夾襖步出監(jiān)牢的瞬間堵幽，已是汗流浹背旁壮。一陣腳步聲響...
開封第一講書人閱讀 32,027評論 1贊 266
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留谐檀，地道東北人抡谐。一個(gè)月前我還...
沈念sama閱讀 46,488評論 2贊 361
代替公主和親
正文我出身青樓，卻偏偏與公主長得像桐猬，于是被迫代替她去往敵國和親麦撵。傳聞我的和親對象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,612評論 2贊 350

Replace into加鎖的探究

推薦閱讀更多精彩內(nèi)容