MySQL Bugs: #73214: redo log flushed during innodb trx prepare even innodb_support

Bug #73214	redo log flushed during innodb trx prepare even innodb_support_xa is turned off
Submitted:	6 Jul 2014 5:43	Modified:	23 Jul 2014 16:04
Reporter:	zhai weixiang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
quoted from docments:
http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_support_xa

"Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation."

Actually, innodb_support_xa doesn't affect the internally XA (binlog and innodb) and always write/sync redo log for transaction preparation.

For example, if we execute the SQL bellow(auto_commit = 1):
update sbtest1 set k = k +2 where id = 3;

step 1:
ha_innobase::index_read->row_search_for_mysql->trx_start_if_not_started->trx_start_if_not_started_low

start transaction and change stage from TRX_STATE_NOT_STARTED to TRX_STATE_ACTIVE. Innodb xa support was not checked.

step 2:
trx_start_if_not_started_xa_low was called later, and trx->xa_support is checked only in this function. but since trx->state is TRX_STATE_ACTIVE, quickly exit the function.

(backtrace for example: row_update_for_mysql->row_upd_step->trx_start_if_not_started_xa_low)

step 3:
so when the transaction commited, trx_flush_log_if_needed was called to write/sync redo log

Breakpoint 2, trx_flush_log_if_needed (trx=0x2b7cd7583658) at /u01/proj/mysql-5.7.4-m14/storage/innobase/trx/trx0trx.cc:1652
1652            trx->op_info = "";
(gdb) bt
#0  trx_flush_log_if_needed (trx=0x2b7cd7583658) at /u01/proj/mysql-5.7.4-m14/storage/innobase/trx/trx0trx.cc:1652
#1  trx_prepare (trx=0x2b7cd7583658) at /u01/proj/mysql-5.7.4-m14/storage/innobase/trx/trx0trx.cc:2649
#2  trx_prepare_for_mysql (trx=0x2b7cd7583658) at /u01/proj/mysql-5.7.4-m14/storage/innobase/trx/trx0trx.cc:2665
#3  0x00000000009e551e in innobase_xa_prepare (hton=<value optimized out>, thd=0x2b8150000b90, prepare_trx=false) at /u01/proj/mysql-5.7.4-m14/storage/innobase/handler/ha_innodb.cc:13383
#4  0x00000000006045d9 in ha_prepare_low (thd=0x2b8150000b90, all=false) at /u01/proj/mysql-5.7.4-m14/sql/handler.cc:1903
#5  0x000000000060554a in ha_commit_trans (thd=0x2b8150000b90, all=false, ignore_global_read_lock=<value optimized out>) at /u01/proj/mysql-5.7.4-m14/sql/handler.cc:1497
#6  0x000000000081be9b in trans_commit_stmt (thd=<value optimized out>) at /u01/proj/mysql-5.7.4-m14/sql/transaction.cc:357
#7  0x000000000077299c in mysql_execute_command (thd=0x2b8150000b90) at /u01/proj/mysql-5.7.4-m14/sql/sql_parse.cc:4707
#8  0x00000000007771d3 in mysql_parse (thd=0x2b8150000b90, parser_state=<value optimized out>) at /u01/proj/mysql-5.7.4-m14/sql/sql_parse.cc:5242
#9  0x000000000077899f in dispatch_command (command=<value optimized out>, thd=0x2b8150000b90, packet=0x2b814c07fae0 "", packet_length=40) at /u01/proj/mysql-5.7.4-m14/sql/sql_parse.cc:1247
#10 0x00000000008350bc in handle_connection (arg=<value optimized out>) at /u01/proj/mysql-5.7.4-m14/sql/conn_handler/connection_handler_per_thread.cc:288
#11 0x000000000098e7b7 in pfs_spawn_thread (arg=0x1ca2c060) at /u01/proj/mysql-5.7.4-m14/storage/perfschema/pfs.cc:2072
#12 0x0000003b416077f1 in start_thread () from /lib64/libpthread.so.0
#13 0x0000003b412e570d in clone () from /lib64/libc.so.6
(gdb) p thd_supports_xa(trx->mysql_thd)
$6 = 0
(gdb) 

 

How to repeat:
1.turn off innodb_support_xa
2.set break point at function trx_flush_log_if_needed
3.check the value of thd_supports_xa(trx->mysql_thd)

Suggested fix:
I don't know if this is the expected  behavior. 
If not ,please correct the code. If yes, please correct the document.

Thank you for the report.

There is a comment for function trx_flush_log_if_needed_low:

----<q>----
/**********************************************************************//**
If required, flushes the log to disk based on the value of
innodb_flush_log_at_trx_commit. */
----</q>----

So this is clear that variable innodb_flush_log_at_trx_commit affects if this function called or not. XA transactions have nothing to do with it.

Hi, Sveta.

So the description bellow  of innodb_support_xa is not correct :
"Enables InnoDB support for two-phase commit in XA transactions, causing an extra disk flush for transaction preparation."

redo write/sync is not affected by innodb_support_xa, but affected by innodb_flush_log_at_trx_commit.

zhai,

our manual does not say a word about that innodb_support_xa tells MySQL to flush log file *after* transaction is committed.