Bug #71216 WITH CONSISTENT SNAPSHOT doesn't isolate from concurrent DDL on stored programs
Submitted: 23 Dec 2013 11:22 Modified: 23 Dec 2013 12:56
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.6.16-bzr OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2013 11:22] 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 DDL statements on stored programs (such as PROCEDURE, FUNCTION or TRIGGER). In case when such concurrent DDL happens after transaction start, transaction is able to observe invalid state of database which doesn't correspond to any its real state and won't get any error.

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

How to repeat:
# Test case for MTR

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

start transaction with consistent snapshot;
select * from t1;

connect (con1, localhost, root,,);
insert into t2 values (1);
create trigger t2_bi before insert on t2 for each row set @a:=1;

connection default;
select * from t2;
# Returns empty set, so result of insert is not visible in this transaction

show create trigger t2_bi;
# Shows the trigger as present!!!

# This means that transaction can see the CREATE TRIGGER statement
# but not an earlier INSERT from the same connection.
# This means that transaction snapshot will be inconsistent with binary log
# history (since binary log history is serial for each individual thread).
[23 Dec 2013 12:56] MySQL Verification Team
Hello Dmitry,

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