Replace into加锁的探究

本文对一个案例的有趣的地方进行分析，然后最后再综述下replace into的流程。
基础环境


image.png

进行一个Replace操作，唯一索引冲突，SQL执行完后事务持有的锁。


image.png

Insert常规的加锁参看这个文章：https://www.jianshu.com/p/d945753a4f15
这里面有几个有趣的地方：

  • 主键上为什么会在4、5上加GAP锁。
  • 为什么唯一索引会在(7,7)上加Next Key锁。
  • 为什么唯一索引会在(6,4)上有Gap锁。

先看看这个SQL执行期间都做了什么：
1.首先因为主键上没有冲突，主键上插入成功。
2.然后插入二级索引，既唯一索引b。先对索引(6,6)加X|LOCK_ORDINARY锁,然后因为(6,6)这条记录产生了DB_DUPLICATE_KEY错误。
3.Server层收到DB_DUPLICATE_KEY错误，需要把第一步插入的聚簇索引回滚掉，这个时候需要给(4,6)这个记录加上一个LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的锁。
4.加锁完成后，对(4,6)记录进行乐观删除btr_cur_optimistic_delete，删除导致(4,6)上的锁发生了继承，变为LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的锁，落在(5,5)记录上。
5.接下来再根据冲突的唯一索引去定位(row_search_mvcc)，对记录(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的锁，因为之前已经持有了X|LOCK_ORDINARY，所以不需加新锁。
6.根据唯一索引定位的主键记录，进行必要的加锁，对主键上的记录(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的锁。
7.因为唯一索引b是表的最后一个唯一索引(如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时，使用UPDATE ROW的方式来解决冲突http://mysql.taobao.org/monthly/2015/03/01/)。首先对(6,6)这条记录设置delete mark。
8.然后插入(4,6)这条记录,进行意向锁(LOCK_X|LOCK_GAP|LOCK_INSERT_INTENTION=2563)兼容监测,监测通过。
9.执行(4,6)记录的插入
10.插入(4,6),导致本事务在(5,5)上持有的GAP锁分裂。因此在(4,6)记录上加个LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)锁。
11.接下来对唯一索引b上的(6,6)进行加锁LOCK_REC_NOT_GAP，因为本事务已经在该记录上持有了LOCK_ORDINARY，所以不需要重新加锁。
12.对唯一索引b上的(6,6)进行delete mark。
13.进行唯一索引(6,4)记录的插入，因为唯一索引上(6,6)的记录已经被设置为delete mark，所以会给(7,7)加上X|LOCK_ORDINARY(3+0=3)的锁
14.执行唯一索引(6,4)记录的插入
15.唯一索引(6,4)记录的插入，导致(7,7)上的LOCK_ORDINARY的锁发生了分裂，(6,4)记录上多了LOCK_GAP|LOCK_REC|LOCK_X(512+32+3=547)的锁。
调用栈

1.首先因为主键上没有冲突，主键上插入成功。
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.然后插入二级索引，既唯一索引b
先对索引(6,6)加X|LOCK_ORDINARY锁,然后因为(6,6)这条记录产生了DB_DUPLICATE_KEY错误。
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错误，需要把第一步插入的聚簇索引回滚掉，这个时候需要给(4,6)这个记录加上一个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)记录进行乐观删除btr_cur_optimistic_delete，删除导致(4,6)上的锁发生了继承，变为LOCK_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.接下来再根据冲突的唯一索引去定位(row_search_mvcc)，对记录(6,6)加LOCK_REC_NOT_GAP|LOCK_REC|LOCK_X(1024+32+3=1059)的锁，因为之前已经持有了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.根据唯一索引定位的主键记录，进行必要的加锁，对主键上的记录(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.因为唯一索引b是表的最后一个唯一索引(如果发生uk冲突的索引是最后一个唯一索引、没有外键引用、且不存在delete trigger时，使用UPDATE ROW的方式来解决冲突http://mysql.taobao.org/monthly/2015/03/01/)。首先对(6,6)这条记录设置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)这条记录,进行意向锁(LOCK_X|LOCK_GAP|LOCK_INSERT_INTENTION=2563)兼容监测,监测通过。
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.执行(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),导致本事务在(5,5)上持有的GAP锁分裂。因此在(4,6)记录上加个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)进行加锁LOCK_REC_NOT_GAP，因为本事务已经在该记录上持有了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)进行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.进行唯一索引(6,4)记录的插入，因为唯一索引上(6,6)的记录已经被设置为delete mark，所以会给(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.执行唯一索引(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)记录的插入，导致(7,7)上的LOCK_ORDINARY的锁发生了分裂，(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/

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,456评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,370评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,337评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,583评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,596评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,572评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,936评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,595评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,850评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,371评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,951评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2赞 342

推荐阅读更多精彩内容