Optimizations for Read-Only Transactions

When a transaction is known to be read-only, InnoDB can avoid the overhead associated with setting up the isolation level for a transaction that might perform arbitrary sequences of read and write operations. Currently, InnoDB detects this condition and applies the optimization automatically when the following conditions are met:

• The autocommit setting is turned on, so that the transaction is guaranteed to be a single statement.
• The single statement making up the transaction is a "non-locking" SELECT statement, that is, one that does not use the FOR UPDATE or LOCK IN SHARED MODE clause.

Thus, for a read-intensive application such as a report generator, you can improve performance for InnoDB queries by turning on the autocommit setting before running a sequence of SELECT queries. If other sessions are making changes concurrently in the database, each query sees the very latest committed data, similar to the READ COMMITTED transaction isolation level.Note

Because these optimized transactions are kept out of certain internal InnoDB data structures, they are not listed in SHOW ENGINE INNODB STATUS output.

Retornar