Search code examples
mysqltransactionsinnodb

Does MySQL InnoDB engine run any performance optimization for read-only transactions

According to the reference documentation the READ ONLY transaction flag may hint the storage engines for running some optimizations.

SET SESSION TRANSACTION READ ONLY;

If the transaction access mode is set to READ ONLY, changes to tables are prohibited. This may enable storage engines to make performance improvements that are possible when writes are not permitted.

Does the InnoDB engine run such optimizations for read-only transactions?

Solution

  • This syntax was important for MySQL 5.6, where creating a READ ONLY transaction saved InnoDB from setting up a number of structures internally.

    In MySQL 5.7, the optimization was extended so that all transactions would start out READ ONLY and then convert to READ_WRITE as required. This is described further in this blog post.