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.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.