Read view creation is expensive. Two optimisations have been made over the past year that mitigate the problem: 1. Special handling of auto-commit-non-locking-ro transactions 2. start transaction read only; explicitly However, for users to take advantage of these improvements, they have to use autocommit or explicitly make the changes to their code to tag the transactions as read-only. START TRANSACTION READ ONLY also has a network and parsing overhead. Compatibility changes: Because transactions are not assigned a transaction ID unless they are determined to be read-write transactions, the "SHOW ENGINE INNODB STATUS" will print an identifier that will be unique only within the context of that invocation.
Optimise InnoDB by reducing the MVCC overhead. Before and after this change, internal transactions will be on trx_sys->rw_trx_list but not trx_sys->mysql_trx_list. User transactions will be on trx_sys->mysql_trx_list and usually also on either ro_trx_list or rw_trx_list. Put auto-commit-non-locking-read-only-selects (ac-nl-ro) only on mysql_trx_list. Put DDL transactions or explicit read-write transactions on the read-write list (trx_sys->rw_trx_list). Put all other transactions initially on the read-only list (trx_sys->ro_trx_list). Ideally we would like the use case where users don't have to make any changes to their code and by default put all transactions on the read-only list unless the transaction is tagged as read-write or is determined to be an ac-nl-ro transaction. The former is put on the read-write transaction list and a rollback segment is assigned to it. For the latter we don't put the transaction on any list, as we do currently. However, for all other transactions, they are put on the read-only list but a rollback segment is not assigned to such transactions. They are also not flagged as read-only, because their intentions are unknown. A transaction is allocated a new ID iff: 1. Read only transaction writing to a TEMP table 2. When a transaction acquires an X or IX lock on a table 3. Explicitly tagged as a read-write transaction Otherwise all transactions by default are treated as read-only and an ID is not assigned to them, note: they are not tagged as read-only. Only transactions that are explicitly started with "START TRANSACTION READ ONLY" are set as read-only. When a transaction is determined to be a read-write transaction it will also be allocated a rollback segment to write its changes. Purge view changes The purge view creation algorithm needs to be modified. It has to check whether the view that it is cloning was created by a read-only transaction or a read- write transaction. If it is a read-write transaction then the creator_trx_id has to be put in the correct slot of the view's trx id array. If creator_trx_id was zero (read-only transaction) then it can be ignored. The last active read-write transaction has the smallest non-zero id. However it may be larger than the oldest view's up_limit_id because the oldest view's creator id can be larger and that will be in the trx_ids array.
Add assertions where transaction id cannot be zero. We should never write a transaction ID of zero to a page unless it is an empty B+Tree root page. Add an additional parameter to trx start if not start functions that tells the intention of the transaction, whether it is a read-only or read-write transaction. Add an os_atomic_fetch_and_increment() to increment the global transaction id. Internal transactions should be started using trx_start_internal() Deadlock checks are now down using the transaction instance instead of transaction id. The logic is however unchanged. Clean up the code that prints the information for SHOW ENGINE INNODB STATUS; Additionally, read-only and transaction's whose intentions are unknown (whether they will eventually do a WRITE) don't have an ID assigned to them. For such transactions we print the transaction instance pointer.