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) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[30 Aug 2018 10:25] Shaohua Wang
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);
@@ -489,6 +490,7 @@ trx_free(trx_t*& 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

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.