Bug #69275 InnoDB read-only transaction optimization for HANDLER READ

• Submitted: 18 May 2013 16:53
• Modified: 19 May 2013 5:59
• Reporter: Mark Callaghan
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.6.11
• OS: Any
• CPU Architecture: Any

[18 May 2013 16:53] Mark Callaghan

Description:
Per the docs, the read-only optimization is done for auto-commit select. Can it also be done for auto-commit HANDLER READ?
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-ro-txn

It isn't done today because this returns false when auto-commit HANDLER read is done --> thd_trx_is_read_only(trx->mysql_thd);

/****************************************************************//**
Starts a transaction. */
static
void
trx_start_low(
/*==========*/
        trx_t*  trx)            /*!< in: transaction */
{
        ut_ad(trx->rseg == NULL);

        ut_ad(!trx->is_recovered);
        ut_ad(trx_state_eq(trx, TRX_STATE_NOT_STARTED));
        ut_ad(UT_LIST_GET_LEN(trx->lock.trx_locks) == 0);

        /* Check whether it is an AUTOCOMMIT SELECT */
        trx->auto_commit = thd_trx_is_auto_commit(trx->mysql_thd);

        trx->read_only =
                !trx->ddl
                && thd_trx_is_read_only(trx->mysql_thd);

        if (!trx->auto_commit) {
                ++trx->will_lock;
        } else if (trx->will_lock == 0) {
                trx->read_only = TRUE;
        }

How to repeat:
read the source
run a read-only benchmark that uses HANDLER READ & compare with results for auto-commit select. There is much more mutex contention on transaction start with HANDLER READ.

Suggested fix:
don't know