WL#7828: InnoDB: attachable transaction support

Affects: Server-5.7   —   Status: Complete   —   Priority: Medium

Attachable transactions are required for new Data Dictionary.

This WL describes how InnoDB supports attachable transaction.

This attachable transaction is always a AC-RO-RC-NL transaction(
AutoCommit-ReadOnly-ReadCommitted-NonLocking)
There are the following main requirements:

  1. Req: read-only SELECT in the attachable transaction must not take extra
  locks.
  
    Test sequence:

    - Start a SERIALIZABLE transaction by read-only SELECT from table t1;

    - In another connection: start another SERIALIZABLE transaction and
      execute SELECT from t1 in the attachable transaction;

    - Make sure the SELECT in the attachable transaction does not block;

  2. Req: the attachable transaction must have different visibility scope than
  the outer transaction;
  
    Test sequence:

    - Start a REPEATABLE READ, READ ONLY transaction by SELECT from t1;

    - In another connection: do an UPDATE to t1;

    - Make sure SELECT in the first transaction does not see the changes
      made by the UPDATE;

    - Make sure SELECT in the attachable transaction (to the first one) does see
      the changes made by the UPDATE.

  3. Req: commit in the attachable transaction must not reset savepoints set by
  the outer transaction;
  
    Test sequence:

    - Start a REPEATABLE READ transaction by SELECT from t1;

    - Set a savepoint;

    - Do some changes to the table t1;

    - Do any SELECT in the attachable transaction (anything in the inner
      transaction ends up with a COMMIT);

    - Rollback to the savepoint;

    - Make sure the changes done to the table t1 are rolled back (that
      ensures that a COMMIT in the attachable transaction does not affect
      savepoints made by the outer transaction).
InnoDB internals
================

InnoDB gets a trx from its internal transaction pool and return to the pool
after use.

When server wants to create this special 'attachable transaction', it sets a 
flag in thd. InnoDB reads this flag and identifies the transaction to be created
as an 'attachable' transaction.

Once the trx is identified as attachable transaction, InnoDB should verify the
properties of the transaction, i.e AC-RO-RC-NL (Auto Commit, Read Only, Read
Committed, Non Locking).

Relation with thd:
------------------
On some handler calls to InnoDB (ideally InnoDB would like to do this checking
only at start_stmt() or external_lock() but since they are skipped sometimes,
the checking is done at many handler apis), it does the below checks

1. InnoDB checks if the thd associated with the connection has trx object(trx_t)
   using thd_ha_data(thd, innodb_hton_ptr).
2. If it is NULL, InnoDB allocates a trx from trx_pool
3. If server indicates, the trx created is attachable (from thd), we set a flag
   in trx_t object.
3. Updates & saves trx_t object in thd ha_data
4. InnoDB will retrieve trx_t from thd_ha_data and use it for handler apis

On close connection handlerton api call, InnoDB returns the trx object to
trx_pool


Usage scenarios
================

In order to start a new attachable transaction the following actions should be
taken:

  - Save the THD::m_ha_data array (to be restored afterwards);

  - Reset the THD::m_ha_data array (to actually indicate the start of
    a nested transaction);

  - Since the attachable transaction is AUTOCOMMIT, explicit commit() or
    rollback() is not necessary.

  - Close the tables opened in the attachable transaction:

    close_thread_tables(thd);

  - Call close_connection() method of InnoDB handlerton:

    ht->close_connection(ht, thd);

  - Restore the THD::m_ha_data array.