Bug #71214 WITH CONSISTENT SNAPSHOT doesn't isolate from concurrent RENAME TABLE
Submitted: 23 Dec 2013 10:47 Modified: 23 Dec 2013 12:48
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2013 10:47] Dmitry Lenev
A consistent snapshot transactions is supposed to see only changes from transactions which were committed by the time when transaction was started and itself.

It is also documented that consistent snapshot doesn't work over concurrent DROP TABLE and ALTER TABLE statements. In cases when such a transaction tries to access which were dropped or changed by concurrent DROP/ALTER TABLE after its start the error is returned.

See: http://dev.mysql.com/doc/refman/5.6/en/innodb-consistent-read.html

Unfortunately, consistent snapshot transactions also are not properly isolated from concurrent RENAME TABLE statements. Moreover in case of concurrent RENAME TABLE which happens after transaction start access to table under new name doesn't generate an error. Instead transaction can observe invalid state of database which doesn't correspond to any its real state.

This means than one can't fully rely on START TRANSACTION WITH CONSISTENT SNAPSHOT for backup purposes as concurrent RENAME TABLE might cause such transaction to return inconsistent snapshot. For example, such snapshot will be inconsistent with binary log.

How to repeat:
# Here is short script for MTR which shows the problem:

create table t1 (i int) engine=innodb;
create table t2 (i int) engine=innodb;

start transaction with consistent snapshot;
select * from t1;
# Returns empty result

connect (con1, localhost, root,,);
insert into t2 values (1);
rename table t2 to t3;

connection default;
select * from t3
# Returns empty result as well.
# But this is inconsistent - as we observe new name of 't2' - 't3' and its old
# contents (i.e. being empty) at the same time!
[23 Dec 2013 12:48] MySQL Verification Team
Hello Dmitry,

Thank you for the bug report and test case.
Verified as described.