Optimizing read-Only transactions for InnoDB

The InnoDB storage engine avoids the overhead of read-only transaction Write transaction ID (trx_id attribute). Transaction ID only in one transaction

A write operation or a write-lock transaction (such as a select ... for update) is required. Do not write transaction ID can greatly reduce the MySQL query or DML

Statement to establish the size of the snapshot's data structure.

At this stage, a read-only transaction is detected by InnoDB in the following cases:

• When you use Start transaction Read only to start a transaction. In this case, if you try to modify the data, the database (InnoDB,

  MyISAM or any other type) will cause an error. In this case, however, you can still modify the temporary tables associated with the session or lock the temporary tables
  , because these modifications or locks are not visible to other transactions.

  Mysql> start transaction Read only; Query OK, 0 rows Affected (0.00 sec) mysql> insert into InnoDB (name) value ("Read-only"); ERROR 1792 (25006): Cannot execute statement in a READ only transaction.

• When the autocommit configuration is opened. In this state the transaction is committed in a single sentence, and the individual statement that makes up the transaction should be a
  A SELECT statement that is not locked. This means that the SQL statement cannot contain a for update or LOCK in SHARE MODE
• If a transaction does not start with start transaction Read only, but the transaction has not yet executed the modification statement. Only when update
  The transaction ID is only established when the statement or statement explicitly requires a lock.

Therefore, for read-intensive applications (such as report generation), you can put a series of query statements between start transaction read only and commit. Or
Open the autocommit configuration. Or you can simply improve performance by not inserting DML statements in the middle of a query statement.