Description:
Since 5.7, RO transactions optimization is used not only for explicit START TRANSACTION READ ONLY and non-locking autocommit SELECTs, but also for any started transaction that hasn't done any lock or write yet.
For some reason, trx_is_read_only attribute of innodb_trx view is not set for this last type of RO transactions, which is misleading.
How to repeat:
mysql [localhost] {msandbox} (db1) > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 8.0.12 | MySQL Community Server - GPL |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+---------+-------+
| name | comment | status | count |
+---------------------------+--------------------------------------------------------------------+---------+-------+
| trx_rw_commits | Number of read-write transactions committed | enabled | 0 |
| trx_ro_commits | Number of read-only transactions committed | enabled | 0 |
| trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 |
+---------------------------+--------------------------------------------------------------------+---------+-------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > START TRANSACTION; SELECT count(*) FROM db1.t1;
Query OK, 0 rows affected (0.00 sec)
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 421988493944672
trx_state: RUNNING
trx_started: 2018-09-25 13:57:41
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 8
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > commit;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+---------+-------+
| name | comment | status | count |
+---------------------------+--------------------------------------------------------------------+---------+-------+
| trx_rw_commits | Number of read-write transactions committed | enabled | 0 |
| trx_ro_commits | Number of read-only transactions committed | enabled | 1 |
| trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 |
+---------------------------+--------------------------------------------------------------------+---------+-------+
4 rows in set (0.00 sec)
-- and explicit one sets the attribute as expected:
mysql [localhost] {msandbox} (db1) > START TRANSACTION READ ONLY; SELECT count(*) FROM db1.t1;
Query OK, 0 rows affected (0.00 sec)
+----------+
| count(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
trx_id: 421988493944672
trx_state: RUNNING
trx_started: 2018-09-25 13:59:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 0
trx_mysql_thread_id: 8
trx_query: select * from information_schema.innodb_trx
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 0
trx_lock_memory_bytes: 1136
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 1
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (db1) > commit;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+---------+-------+
| name | comment | status | count |
+---------------------------+--------------------------------------------------------------------+---------+-------+
| trx_rw_commits | Number of read-write transactions committed | enabled | 0 |
| trx_ro_commits | Number of read-only transactions committed | enabled | 2 |
| trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 |
+---------------------------+--------------------------------------------------------------------+---------+-------+
4 rows in set (0.00 sec)
Suggested fix:
The trx_is_read_only attribute should indicate the real state of transaction, regardless of how it was started.
Description: Since 5.7, RO transactions optimization is used not only for explicit START TRANSACTION READ ONLY and non-locking autocommit SELECTs, but also for any started transaction that hasn't done any lock or write yet. For some reason, trx_is_read_only attribute of innodb_trx view is not set for this last type of RO transactions, which is misleading. How to repeat: mysql [localhost] {msandbox} (db1) > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.12 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 0 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec) mysql [localhost] {msandbox} (db1) > START TRANSACTION; SELECT count(*) FROM db1.t1; Query OK, 0 rows affected (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 421988493944672 trx_state: RUNNING trx_started: 2018-09-25 13:57:41 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 8 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > commit; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 1 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec) -- and explicit one sets the attribute as expected: mysql [localhost] {msandbox} (db1) > START TRANSACTION READ ONLY; SELECT count(*) FROM db1.t1; Query OK, 0 rows affected (0.00 sec) +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > select * from information_schema.innodb_trx\G *************************** 1. row *************************** trx_id: 421988493944672 trx_state: RUNNING trx_started: 2018-09-25 13:59:12 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 0 trx_mysql_thread_id: 8 trx_query: select * from information_schema.innodb_trx trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 0 trx_lock_memory_bytes: 1136 trx_rows_locked: 0 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 1 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) mysql [localhost] {msandbox} (db1) > commit; Query OK, 0 rows affected (0.01 sec) mysql [localhost] {msandbox} (db1) > SELECT name, comment, status, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%'; +---------------------------+--------------------------------------------------------------------+---------+-------+ | name | comment | status | count | +---------------------------+--------------------------------------------------------------------+---------+-------+ | trx_rw_commits | Number of read-write transactions committed | enabled | 0 | | trx_ro_commits | Number of read-only transactions committed | enabled | 2 | | trx_nl_ro_commits | Number of non-locking auto-commit read-only transactions committed | enabled | 0 | | trx_commits_insert_update | Number of transactions committed with inserts and updates | enabled | 0 | +---------------------------+--------------------------------------------------------------------+---------+-------+ 4 rows in set (0.00 sec) Suggested fix: The trx_is_read_only attribute should indicate the real state of transaction, regardless of how it was started.