Documentation Home
MySQL 9.1 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.4Mb
PDF (A4) - 40.5Mb
Man Pages (TGZ) - 259.5Kb
Man Pages (Zip) - 366.7Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.1 Reference Manual  / ...  / Optimization  / Optimizing for InnoDB Tables  /  Optimizing InnoDB Read-Only Transactions

10.5.3 Optimizing InnoDB Read-Only Transactions

InnoDB can avoid the overhead associated with setting up thetransaction ID (TRX_ID field) for transactions that are known to be read-only. A transaction ID is only needed for atransaction that might perform write operations orlocking reads such asSELECT ... 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 aread view.

InnoDB detects read-only transactions when:

  • The transaction is started with theSTART 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.

  • Theautocommit 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 theREAD 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 ofInnoDB queries by grouping them insideSTART TRANSACTION READ ONLY andCOMMIT, or by turning on theautocommit setting before running theSELECT statements, or simply by avoiding any data change statements interspersed with the queries.

For information aboutSTART TRANSACTION andautocommit, seeSection 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”.

Note

Transactions that qualify as auto-commit, non-locking, and read-only (AC-NL-RO) are kept out of certain internalInnoDB data structures and are therefore not listed inSHOW ENGINE INNODB STATUS output.