10.5.3 优化InnoDB只读事务
InnoDB
can avoid the overhead associated with setting up the transaction ID (TRX_ID
field) for transactions that are known to be read-only. A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE
. Eliminating unnecessary transaction IDs reduces the size of internal data structures that are consulted each time a query or data change statement constructs a read view.
InnoDB
detects read-only transactions when:
-
The transaction is started with the
START TRANSACTION READ ONLY
statement. In this case, attempting to make changes to the database (forInnoDB
,MyISAM
, or other types of tables) causes an error, and the transaction continues in read-only state:ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
You can still make changes to session-specific temporary tables in a read-only transaction, or issue locking queries for them, because those changes and locks are not visible to any other transaction.
-
The
autocommit
setting is turned on, so that the transaction is guaranteed to be a single statement, and the single statement making up the transaction is a “non-locking”SELECT
statement. That is, aSELECT
that does not use aFOR UPDATE
orLOCK IN SHARED MODE
clause. -
The transaction is started without the
READ ONLY
option, but no updates or statements that explicitly lock rows have been executed yet. Until updates or explicit locks are required, a transaction stays in read-only mode.
Thus, for a read-intensive application such as a report generator, you can tune a sequence of InnoDB
queries by grouping them inside START TRANSACTION READ ONLY
and COMMIT
, or by turning on the autocommit
setting before running the SELECT
statements, or simply by avoiding any data change statements interspersed with the queries.
For information about START TRANSACTION
and autocommit
, see Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.
Transactions that qualify as auto-commit, non-locking, and read-only (AC-NL-RO) are kept out of certain internal InnoDB
data structures and are therefore not listed in SHOW ENGINE INNODB STATUS
output.