MySQL Bugs: #66587: Behavior of read-only transactions differs from documentation

Bug #66587	Behavior of read-only transactions differs from documentation
Submitted:	28 Aug 2012 23:04	Modified:	23 May 2013 16:55
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.7	OS:	Any
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
The manual (http://dev.mysql.com/doc/refman/5.6/en/commit.html) says:

<quote>
MySQL enables extra optimizations for queries on InnoDB tables when the transaction is known to be read-only. Specifying READ ONLY ensures these optimizations are applied in cases where the read-only status cannot be determined automatically. See Section 14.2.5.2.2, “Optimizations for Read-Only Transactions” for more information. 
</quote>

The section it points at (http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-ro-txn) says:

<quote>
 Currently, InnoDB detects the read-only nature of the transaction and applies this optimization when any of the following conditions are met:

* The transaction is started with the START TRANSACTION READ ONLY statement. 

<cut>

Note:

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

</quote>

Since the last note seems to be the most obvious way to check whether the optimizations are applied, I'm looking at SHOW ENGINE INNODB STATUS, and see that the transactions are there (see the test output below). 

It might be just a documentation issue, or maybe not an issue at all and I'm misreading it, but I will assume the worst and set the category to InnoDB for now.

Output of the test case provided in 'How to repeat' section:

SHOW VARIABLES LIKE '%version%';
Variable_name	Value
innodb_version	1.2.6
protocol_version	10
slave_type_conversions	
version	5.6.6-m9-log
version_comment	MySQL Community Server (GPL)
version_compile_machine	x86_64
version_compile_os	linux2.6
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
START TRANSACTION READ ONLY;
SELECT CONNECTION_ID();
CONNECTION_ID()
2
SELECT SLEEP(1) FROM t1;
connect  con1,localhost,root,,;
SHOW ENGINE INNODB STATUS;
Type	InnoDB
Name	
Status	
=====================================
120829  1:55:27 INNODB MONITOR OUTPUT
=====================================
<cut>
------------
TRANSACTIONS
------------
Trx id counter 1295
Purge done for trx's n:o < 0 undo n:o < 0 state: running
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7f8b8fa0e700, query id 25 localhost root init
SHOW ENGINE INNODB STATUS
---TRANSACTION 1294, ACTIVE 0 sec
mysql tables in use 1, locked 0
MySQL thread id 2, OS thread handle 0x7f8b8fa4f700, query id 24 localhost root User sleep
SELECT SLEEP(1) FROM t1
Trx read view will not see trx with id >= 1295, sees < 1295
--------
FILE I/O
--------
<cut>
----------------------------
END OF INNODB MONITOR OUTPUT
============================

How to repeat:
--enable_connect_log
SHOW VARIABLES LIKE '%version%';

CREATE TABLE t1 (i INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);

START TRANSACTION READ ONLY;
SELECT CONNECTION_ID();
--send SELECT SLEEP(1) FROM t1

--connect (con1,localhost,root,,)

--query_vertical SHOW ENGINE INNODB STATUS

# Cleanup
--connection default
--reap
COMMIT;
DROP TABLE t1;

Thank you for the problem report. Verified on Linux:

[openxs@chief mysql-test]$ ./mtr bug66587
Logging: ./mtr  bug66587
120829  9:14:58 [Note] Plugin 'FEDERATED' is disabled.
120829  9:14:58 [Note] Binlog end
120829  9:14:58 [Note] Shutting down plugin 'CSV'
120829  9:14:58 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.7
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/openxs/dbs/5.6/mysql-test/var'...
Installing system database...
Using server port 54031

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug66587                            [ fail ]
        Test ended at 2012-08-29 09:15:09

CURRENT_TEST: main.bug66587
--- /home/openxs/dbs/5.6/mysql-test/r/bug66587.result   2012-08-29 09:14:48.480171908 +0300
+++ /home/openxs/dbs/5.6/mysql-test/r/bug66587.reject   2012-08-29 09:15:08.911511438 +0300
@@ -0,0 +1,135 @@
+SHOW VARIABLES LIKE '%version%';
+Variable_name  Value
+innodb_version 1.2.7
+protocol_version       10
+slave_type_conversions
+version        5.6.7-debug-log
+version_comment        Source distribution
+version_compile_machine        x86_64
+version_compile_os     Linux
+CREATE TABLE t1 (i INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1);
+START TRANSACTION READ ONLY;
+SELECT CONNECTION_ID();
+CONNECTION_ID()
+2
+SELECT SLEEP(1) FROM t1;
+connect  con1,localhost,root,,;
+SHOW ENGINE INNODB STATUS;
+Type   InnoDB
+Name
+Status
+=====================================
+120829  9:15:07 INNODB MONITOR OUTPUT
+=====================================
+Per second averages calculated from the last 0 seconds
+-----------------
+BACKGROUND THREAD
+-----------------
+srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 0 srv_idle
+srv_master_thread log flush and writes: 0
+----------
+SEMAPHORES
+----------
+----------
+MUTEX INFO
+----------
+Locked mutex: addr 0x17418e0 thread 140555758065408 file /home/openxs/bzr/mysql-5.6-work/storage/innobase/handler/ha_innodb.cc line 12181
+Locked mutex: addr 0x1741860 thread 140555758065408 file /home/openxs/bzr/mysql-5.6-work/storage/innobase/srv/srv0srv.cc line 1081
+Total number of mutexes 695
+-------------
+RW-LATCH INFO
+-------------
+Total number of rw-locks 1133
+OS WAIT ARRAY INFO: reservation count 5
+OS WAIT ARRAY INFO: signal count 5
+Mutex spin waits 4, rounds 91, OS waits 2
+RW-shared spins 1, rounds 30, OS waits 1
+RW-excl spins 2, rounds 31, OS waits 1
+Spin rounds per wait: 22.75 mutex, 30.00 RW-shared, 15.50 RW-excl
+------------
+TRANSACTIONS
+------------
+Trx id counter 1294
+Purge done for trx's n:o < 0 undo n:o < 0 state: running
+History list length 0
+Total number of lock structs in row lock hash table 0
+LIST OF TRANSACTIONS FOR EACH SESSION:
+---TRANSACTION 0, not started
+MySQL thread id 3, OS thread handle 0x7fd5b007d700, query id 25 localhost root init
+SHOW ENGINE INNODB STATUS
+---TRANSACTION 1293, ACTIVE 0 sec
+mysql tables in use 1, locked 0
+MySQL thread id 2, OS thread handle 0x7fd593fff700, query id 24 localhost root User sleep
+SELECT SLEEP(1) FROM t1
+Trx read view will not see trx with id >= 1294, sees < 1294
...

Revised note in 5.6 and 5.7 reference manual as follows:

"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. These
transactions are only visible in the Information Schema."

Thank you for the bug report.