如何理解MySQL层事务提交流程,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。
部分栈帧:
prepare栈帧
(gdb) bt #0 binlog_prepare (hton=0x346f260, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1578 #1 0x0000000000f64e80 in ha_prepare_low (thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2348 #2 0x0000000001856176 in MYSQL_BIN_LOG::prepare (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8165 #3 0x0000000000f63d1f in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1803 #4 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #5 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #6 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #7 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #8 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #9 0x00000000016e28f0 in handle_connection (arg=0x384a5d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #10 0x0000000001d7a514 in pfs_spawn_thread (arg=0x3848260) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #11 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #12 0x0000003f740e8bcd in clone () from /lib64/libc.so.6 (gdb) b innobase_trx_init Breakpoint 7 at 0x19a5fdb: file /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc, line 2753. (gdb) c Continuing.
trx_prepare_low
Breakpoint 7, innobase_trx_init (thd=0x7fff2c000b70, trx=0x7ffff2f2db00) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:2753 2753 DBUG_ENTER("innobase_trx_init"); (gdb) bt #0 innobase_trx_init (thd=0x7fff2c000b70, trx=0x7ffff2f2db00) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:2753 #1 0x00000000019a6291 in check_trx_exists (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:2816 #2 0x00000000019c2b90 in innobase_xa_prepare (hton=0x2e9cdd0, thd=0x7fff2c000b70, prepare_trx=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:17458 #3 0x0000000000f64e80 in ha_prepare_low (thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2348 #4 0x0000000001856176 in MYSQL_BIN_LOG::prepare (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8165 #5 0x0000000000f63d1f in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1803 #6 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #7 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #8 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #9 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #10 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #11 0x00000000016e28f0 in handle_connection (arg=0x384a5d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #12 0x0000000001d7a514 in pfs_spawn_thread (arg=0x3848260) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #13 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #14 0x0000003f740e8bcd in clone () from /lib64/libc.so.6 (gdb)
flush 之前写redo 栈帧
1145 bool write_header = new_data_offset == 0;
(gdb) bt #0 log_group_write_buf (group=0x33ef9f8, buf=0x7fffa5b38000 "\\200\\020\\300\\253\\002", len=1024, pad_len=0, start_lsn=562123776, new_data_offset=465) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145 #1 0x0000000001a50dd5 in log_write_up_to (lsn=562124558, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493 #2 0x0000000001a50ec7 in log_buffer_flush_to_disk (sync=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1524 #3 0x00000000019a9077 in innobase_flush_logs (hton=0x2e9cdd0, binlog_group_flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4407 #4 0x0000000000f65893 in flush_handlerton (thd=0x0, plugin=0x7ffff03588e8, arg=0x7ffff0358944) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2606 #5 0x00000000015d76ee in plugin_foreach_with_mask (thd=0x0, func=0xf65835 <flush_handlerton(THD*, plugin_ref, void*)>, type=1, state_mask=4294967287, arg=0x7ffff0358944) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_plugin.cc:2318 #6 0x0000000000f658ef in ha_flush_logs (db_type=0x0, binlog_group_flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:2617 #7 0x000000000185725d in MYSQL_BIN_LOG::process_flush_stage_queue (this=0x2dffe80, total_bytes_var=0x7ffff0358a88, rotate_var=0x7ffff0358a87, out_queue_var=0x7ffff0358a78) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8541 #8 0x00000000018588bf in MYSQL_BIN_LOG::ordered_commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9189 #9 0x0000000001856f2c in MYSQL_BIN_LOG::commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440 #10 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818 #11 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #12 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #13 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #14 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #15 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #16 0x00000000016e28f0 in handle_connection (arg=0x68e1060) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #17 0x0000000001d7a514 in pfs_spawn_thread (arg=0x3848660) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #18 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #19 0x0000003f740e8bcd in clone () from /lib64/libc.so.6
order commit innodb层commit栈帧
Breakpoint 2, trx_purge_add_update_undo_to_history (trx=0x7ffff2f2e068, undo_ptr=0x7ffff2f2e488, undo_page=0x7fffb0244000 "E\\373\\332r", update_rseg_history_len=true, n_added_logs=1, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0purge.cc:332 332 undo = undo_ptr->update_undo; (gdb) bt #0 trx_purge_add_update_undo_to_history (trx=0x7ffff2f2e068, undo_ptr=0x7ffff2f2e488, undo_page=0x7fffb0244000 "E\\373\\332r", update_rseg_history_len=true, n_added_logs=1, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0purge.cc:332 #1 0x0000000001bef543 in trx_undo_update_cleanup (trx=0x7ffff2f2e068, undo_ptr=0x7ffff2f2e488, undo_page=0x7fffb0244000 "E\\373\\332r", update_rseg_history_len=true, n_added_logs=1, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0undo.cc:1962 #2 0x0000000001bddf57 in trx_write_serialisation_history (trx=0x7ffff2f2e068, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:1698 #3 0x0000000001bdf45d in trx_commit_low (trx=0x7ffff2f2e068, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2204 #4 0x0000000001bdf5d2 in trx_commit (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2274 #5 0x0000000001bdff38 in trx_commit_for_mysql (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2550 #6 0x00000000019a9101 in innobase_commit_low (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4451 #7 0x00000000019a99e7 in innobase_commit (hton=0x2e9cdd0, thd=0x7fff2c000b70, commit_trx=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4740 #8 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c000b70, all=false, run_after_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923 #9 0x000000000185764b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2dffe80, thd=0x7fff2c000b70, first=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647 #10 0x0000000001858e7d in MYSQL_BIN_LOG::ordered_commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318 #11 0x0000000001856f2c in MYSQL_BIN_LOG::commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440 #12 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818 #13 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #14 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #15 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #16 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #17 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #18 0x00000000016e28f0 in handle_connection (arg=0x68e11d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #19 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38484d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #20 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #21 0x0000003f740e8bcd in clone () from /lib64/libc.so.6 (gdb) c Continuing. Breakpoint 8, trx_commit_in_memory (trx=0x7ffff2f2e068, mtr=0x7ffff03582f0, serialised=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:1972 1972 trx->must_flush_log_later = false; (gdb) bt #0 trx_commit_in_memory (trx=0x7ffff2f2e068, mtr=0x7ffff03582f0, serialised=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:1972 #1 0x0000000001bdf52a in trx_commit_low (trx=0x7ffff2f2e068, mtr=0x7ffff03582f0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2250 #2 0x0000000001bdf5d2 in trx_commit (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2274 #3 0x0000000001bdff38 in trx_commit_for_mysql (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2550 #4 0x00000000019a9101 in innobase_commit_low (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4451 #5 0x00000000019a99e7 in innobase_commit (hton=0x2e9cdd0, thd=0x7fff2c000b70, commit_trx=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4740 #6 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c000b70, all=false, run_after_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923 #7 0x000000000185764b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2dffe80, thd=0x7fff2c000b70, first=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647 #8 0x0000000001858e7d in MYSQL_BIN_LOG::ordered_commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318 #9 0x0000000001856f2c in MYSQL_BIN_LOG::commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440 #10 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818 #11 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #12 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #13 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #14 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #15 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #16 0x00000000016e28f0 in handle_connection (arg=0x68e11d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #17 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38484d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #18 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #19 0x0000003f740e8bcd in clone () from /lib64/libc.so.6 (gdb) c Continuing. Breakpoint 3, trx_commit_complete_for_mysql (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2570 2570 if (trx->id != 0 (gdb) bt #0 trx_commit_complete_for_mysql (trx=0x7ffff2f2e068) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/trx/trx0trx.cc:2570 #1 0x00000000019a9aab in innobase_commit (hton=0x2e9cdd0, thd=0x7fff2c000b70, commit_trx=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4762 #2 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c000b70, all=false, run_after_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923 #3 0x000000000185764b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2dffe80, thd=0x7fff2c000b70, first=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647 #4 0x0000000001858e7d in MYSQL_BIN_LOG::ordered_commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false, skip_commit=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318 #5 0x0000000001856f2c in MYSQL_BIN_LOG::commit (this=0x2dffe80, thd=0x7fff2c000b70, all=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440 #6 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c000b70, all=false, ignore_global_read_lock=false) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818 #7 0x00000000016b3d7d in trans_commit_stmt (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/transaction.cc:458 #8 0x00000000015ac64f in mysql_execute_command (thd=0x7fff2c000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5239 #9 0x00000000015adcae in mysql_parse (thd=0x7fff2c000b70, parser_state=0x7ffff035b600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836 #10 0x00000000015a1b6d in dispatch_command (thd=0x7fff2c000b70, com_data=0x7ffff035bd70, command=COM_QUERY) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447 #11 0x00000000015a099e in do_command (thd=0x7fff2c000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010 #12 0x00000000016e28f0 in handle_connection (arg=0x68e11d0) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312 #13 0x0000000001d7a514 in pfs_spawn_thread (arg=0x38484d0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188 #14 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0 #15 0x0000003f740e8bcd in clone () from /lib64/libc.so.6
看完上述内容,你们掌握如何理解MySQL层事务提交流程的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注云搜网行业资讯频道,感谢各位的阅读!