Bug #67759 ALTER TABLE breaks REPEATABLE READ when Query Cache is enabled
Submitted: 29 Nov 2012 15:10 Modified: 29 Jan 2013 6:28
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.29-bzr, 5.5.30, 5.0.97 OS:Any
Assigned to:
Triage: Needs Triage: D2 (Serious)

[29 Nov 2012 15:10] Dmitry Lenev
Description:
When transaction is executed in REPEATABLE READ isolation mode and some SELECT in this transaction is served from the Query Cache, concurrent ALTER TABLE on the table used in this SELECT might break isolation rules. I.e. later SELECTs in the same transaction on the same table can produce inconsistent results.
See How-to-repeat for details. 

How to repeat:
set GLOBAL query_cache_size=1355776;

create table t1 (i int) engine=InnoDB;
insert into t1 values (1), (2), (3);

connect(con1, localhost, root,,);

select @@tx_isolation;
# @@tx_isolation
# REPEATABLE-READ

begin;
select * from t1;
# Returns:
# i
# 1
# 2
# 3
commit;

begin;
select * from t1;
# Returns:
# i
# 1
# 2
# 3

connection default;
# In theory should be blocked since concurrent transaction uses t1,
# but it is not!
alter table t1 add column j int;

connection con1;
# Sees a new version of t1 with a) 2 columns, but b) which is empty
# The second issue is actually reproducible without query cache
# one just needs to query table different than t1 in transaction
# from con1 before doing ALTER TABLE.
select * from t1 where 1;
# Returns:
# i      j
#

Suggested fix:
Consider acquiring meta-data locks even for queries served from the Query Cache?
[29 Nov 2012 15:13] Dmitry Lenev
This is actually a re-incarnation of bug #11981 which might make it technically not a bug but a documented limitation...
[29 Nov 2012 16:26] Sveta Smirnova
Thank you for the report.

Verified as described.

Bug does not exist in version 5.7.1 In this version ALTER is blocked.
[29 Jan 2013 6:28] Dmitry Lenev
This issue has been fixed in 5.6.6 version of MySQL Server.

The fix is a side-effect of online/in-place index addition
changes for InnoDB which were done in this version. These
changes fixed bug both for case when ALTER TABLE is executed
using ALGORITHM=INPLACE and using ALGORITHM=COPY.