Bug #71215 WITH CONSISTENT SNAPSHOT doesn't isolate from some concurrent ALTER TABLEs
Submitted: 23 Dec 2013 10:57 Modified: 23 Dec 2013 12:51
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.6.16-bzr OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2013 10:57] Dmitry Lenev
Description:
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 are not properly isolated from those ALTER TABLE statements which update only meta-data. If such statement is executed concurrently with consistent snapshot transaction the transaction might observe inconsistent database state instead of getting ER_TABLE_DEF_CHANGED error (contrary to documentation).

This means than one can't fully rely on START TRANSACTION WITH CONSISTENT SNAPSHOT for backup purposes as concurrent metadata-only ALTER TABLE might cause such a transaction to return inconsistent snapshot which won't be consistent with binary log, for example.

How to repeat:
# Small MTR script 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 set

connect (con1, localhost, root,,);
insert into t2 values (1);
alter table t2 change column i j int;

connection default;
select * from t2;
# Returns empty set with column named 'j' !!!
# This means that transaction sees state of database which never existed in
# reality and doesn't correspond to binary log history.
[23 Dec 2013 12:51] MySQL Verification Team
Hello Dmitry,

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

Thanks,
Umesh