Bug #92243 Void unnecessary trx id generation in InnoDB

• Submitted: 30 Aug 2018 10:25
• Modified: 30 Aug 2018 12:25
• Reporter: Shaohua Wang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[30 Aug 2018 10:25] Shaohua Wang

Description:
We're TXSQL(TENCENT MySQL) team, and we found InnoDB still generate new transaction id(call trx_sys_get_new_trx_id()) when doing read only select.

The root cause it trx->ddl is set to ture in DDL transactions, but we don't set the flag to false when returning the trx object to trx pool. so next time, when we use the trx object without calling trx_init(), we still generate new trx id.

in trx_start_low():
        if (!trx->read_only
            && (trx->mysql_thd == 0 || read_write || trx->ddl)) {

How to repeat:
run read only test in sysbench.

Suggested fix:
diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc
index 4123b2d..3f8f264 100644
--- a/storage/innobase/trx/trx0trx.cc
+++ b/storage/innobase/trx/trx0trx.cc
@@ -335,6 +335,7 @@ struct TrxFactory {
                ut_ad(trx->dict_operation == TRX_DICT_OP_NONE);
 
                ut_ad(trx->mysql_thd == 0);
+               ut_ad(!trx->ddl);
 
                ut_ad(!trx->in_rw_trx_list);
                ut_ad(!trx->in_mysql_trx_list);
@@ -489,6 +490,7 @@ trx_free(trx_t*& trx)
        assert_trx_is_free(trx);
 
        trx->mysql_thd = 0;
+       trx->ddl = false;
 
        // FIXME: We need to avoid this heap free/alloc for each commit.
        if (trx->autoinc_locks != NULL) {

[30 Aug 2018 12:25] MySQL Verification Team

HI,

Thank you for your bug report.

I find your code analysis quite correct and I think that your solution to the problem is right.

Verified as reported.