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

[18 May 2013 16:53] Mark Callaghan
Per the docs, the read-only optimization is done for auto-commit select. Can it also be done for auto-commit HANDLER READ?

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. */
        trx_t*  trx)            /*!< in: transaction */
        ut_ad(trx->rseg == NULL);

        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 =
                && thd_trx_is_read_only(trx->mysql_thd);

        if (!trx->auto_commit) {
        } 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