Based on the environment

Perform a Replace operation, unique index conflict, transaction held lock after SQL execution.

Insert General locking see this article: juejin.cn/post/694945…

doubt

There are a few interesting things about this:

  • Why would a GAP lock be placed on 4, 5 on the primary key?
  • Why does a unique index have a Next Key lock on (7,7)?
  • Why does a unique index have a Gap lock on (6,4)?

SQL execution flow – Lock related

Let’s take a look at what happens during SQL execution:

  1. The primary key was successfully inserted because there was no conflict on the primary key.
  2. Then insert the secondary index, which is unique index B. To index (6, 6) plus X | LOCK_ORDINARY lock, and then because the record (6, 6) DB_DUPLICATE_KEY error occurred.
  3. Server layer receive DB_DUPLICATE_KEY error, need to insert the first step into the clustering index rolled back off, at this time of need to give this record (4, 6) plus a LOCK_REC_NOT_GAP | LOCK_REC | LOCK_X (1024 + 32 + 3 = 1059) of the lock.
  4. Lock is completed, to optimistic (4, 6) record delete btr_cur_optimistic_delete, delete the result in (4, 6) have taken place in the lock on the inheritance, into LOCK_GAP | LOCK_REC | LOCK_X (512 + 32 + 3 = 547) of lock, fall on record (5, 5).
  5. Then according to the conflict of the index to locate (row_search_mvcc), the record (6, 6) plus LOCK_REC_NOT_GAP | LOCK_REC | LOCK_X (1024 + 32 + 3 = 1059) of lock, because before had X | LOCK_ORDINARY, So there is no need to add a new lock.
  6. According to the only index to locate the primary key of the record, the necessary lock, record on the primary key (6, 6) plus LOCK_REC_NOT_GAP | LOCK_REC | LOCK_X (1024 + 32 + 3 = 1059) of the lock.
  7. Because unique index b is the last unique index of the table, first set the delete mark on the record (6,6). (if there is a conflict of UK index is the last one only index, no foreign key references, and there is no delete trigger, use the UPDATE the ROW way to resolve the conflict mysql.taobao.org/monthly/201…
  8. Then insert (4, 6) the records, intent on lock (LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION = 2563) compatible with monitor, monitor.
  9. Perform the insert of the (4,6) record
  10. Insert (4,6), causing the GAP lock held on (5,5) to split. So at the (4, 6) add a LOCK_GAP | LOCK_REC | LOCK_X 32 + 3 = (512 + 547) the lock.
  11. Then lock (6,6) on unique index b LOCK_REC_NOT_GAP, since this transaction already holds LOCK_ORDINARY on that record, no need to re-lock.
  12. Delete mark (6,6) on unique index b.
  13. Unique index (6, 4) record insertion, because the only index (6, 6) records have been set to the delete mark, so will give (7, 7) plus X | LOCK_ORDINARY (3 + 0 = 3) lock
  14. Perform an insert of the unique index (6,4) record
  15. Unique index (6, 4) record insertion, lead to (7, 7) on the split LOCK_ORDINARY lock, (6, 4) record more LOCK_GAP | LOCK_REC | LOCK_X (512 + 32 + 3 = 547) of the lock.

The call stack

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
Copy the code

A little detail of secondary unique index inserts

/***************************************************************//** Scans a unique non-clustered index at a given index  entry to determine whether a uniqueness violation has occurred for the key value of the entry. Set shared locks on possible duplicate records. @return DB_SUCCESS, DB_DUPLICATE_KEY, or DB_LOCK_WAIT */ static MY_ATTRIBUTE((nonnull, warn_unused_result)) dberr_t row_ins_scan_sec_index_for_duplicate( /*=================================*/ ulint flags, / *! < in: undo logging and locking flags */ dict_index_t* index, /*! < in: non-clustered unique index */ dtuple_t* entry, /*! < in: index entry */ que_thr_t* thr, /*! < in: query thread */ bool s_latch,/*! < in: whether index->lock is being held */ mtr_t* mtr, /*! < in/out: mini-transaction */ mem_heap_t* offsets_heap) /*! < in/out: memory heap that can be emptied */ { ... /* Scan index records and check if there is a duplicate */ do { const rec_t* rec = btr_pcur_get_rec(&pcur); const buf_block_t* block = btr_pcur_get_block(&pcur); const ulint lock_type = LOCK_ORDINARY; if (page_rec_is_infimum(rec)) { continue; } offsets = rec_get_offsets(rec, index, offsets, ULINT_UNDEFINED, &offsets_heap); if (flags & BTR_NO_LOCKING_FLAG) { /* Set no locks when applying log in online table rebuild. */ } else if (allow_duplicates) { /* If the SQL-query will update or replace duplicate key we will take X-lock for duplicates ( REPLACE, LOAD DATAFILE REPLACE, INSERT ON DUPLICATE KEY UPDATE). */ err = row_ins_set_exclusive_rec_lock( lock_type, block, rec, index, offsets, thr); } else { err = row_ins_set_shared_rec_lock( lock_type, block, rec, index, offsets, thr); } switch (err) { case DB_SUCCESS_LOCKED_REC: err = DB_SUCCESS; case DB_SUCCESS: break; default: goto end_scan; } if (page_rec_is_supremum(rec)) { continue; } cmp = cmp_dtuple_rec(entry, rec, offsets); if (cmp == 0 && ! Index -> allow_DUPLicates) {/* Row_ins_DUPL_ERROR_with_rec The rec_get_deleted_flag command is used to determine whether the delete flag in this record is 1. DB_DUPLICATE_KEY if not, otherwise continue to lock the next record *. And that's why we can lock PI (7,7). / if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) { err = DB_DUPLICATE_KEY; thr_get_trx(thr)->error_index = index; /* If the duplicate is on hidden FTS_DOC_ID, state so in the error log */ if (index == index->table->fts_doc_id_index && DICT_TF2_FLAG_IS_SET( index->table, DICT_TF2_FTS_HAS_DOC_ID)) { ib::error() << "Duplicate FTS_DOC_ID" " value on table " << index->table->name; } goto end_scan; } } else { ut_a(cmp < 0 || index->allow_duplicates); goto end_scan; } } while (btr_pcur_move_to_next(&pcur, mtr)); end_scan: /* Restore old value */ dtuple_set_n_fields_cmp(entry, n_fields_cmp); DBUG_RETURN(err); }Copy the code

Reenact the delete Flag 1 scenario by stopping the Purge thread

Run FLUSH TABLE tbName FOR EXPORT on an unrelated TABLE.

www.whitewood.me/2017/08/06/…

Mysql.taobao.org/monthly/201…

Mysql.taobao.org/monthly/202…

Blog.csdn.net/longxibendi…

zhuanlan.zhihu.com/p/52098868