MySQL Bugs: #73454: Add information about how InnoDB multi-versioning affects indexes

Bug #73454	Add information about how InnoDB multi-versioning affects indexes
Submitted:	1 Aug 2014 17:54	Modified:	21 Aug 2015 14:22
Reporter:	Sveta Smirnova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.21	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any

Description:
Currently we have single page about InnoDB multi-versioning at http://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html This page perfectly explains how InnoDB handles multi-versioning of a row, but says nothing about index multi-versioning. This can confuse users who use secondary indexes and see that query resolved using index only. They can wonder if InnoDB will return updated or old rows.

How to repeat:
mysql> create table t1(id int not null primary key auto_increment, f2 int, index(f2));
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(null, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 select null, f1 from t1;
ERROR 1054 (42S22): Unknown column 'f1' in 'field list'
mysql> insert into t1 select null, id from t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 select null, id from t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1 select null, id from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1 select null, id from t1;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> insert into t1 select null, id from t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> explain select f2 from t1 where f2=1;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
|  1 | SIMPLE      | t1    | ref  | f2            | f2   | 5       | const |    6 | Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.01 sec)

Now imagine that you update this table in multiple transactions. While EXPLAIN says that MySQL uses index to return rows to the client, how will you be sure that you receive correct rows if you are not sure if indexes are multi-versioned as well as rows.

Suggested fix:
Add a chapter into user manual, describing how InnoDB provides multi-versioning of indexes.

another example http://blog.jcole.us/2014/04/16/a-little-fun-with-innodb-multi-versioning/

https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-transaction-management.html

"When a long-running transaction modifies a table, queries against that table from other transactions do not make use of the covering index technique. Queries that normally could retrieve all the result columns from a secondary index, instead look up the appropriate values from the table data.

If secondary index pages are found to have a PAGE_MAX_TRX_ID that is too new, or if records in the secondary index are delete-marked, InnoDB may need to look up records using a clustered index."

Posted by developer:
 
A section about MVCC and Secondary index has been added to:
https://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html

The changes should appear online within 24 hours.

Thank you for the bug report.