WL#6047: Do not allocate trx id for read-only transactions

Status: Complete   —   Priority: Medium

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 
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.