MySQL Bugs: #15491: ALTER TABLE violates REPEATABLE READ

Bug #15491	ALTER TABLE violates REPEATABLE READ
Submitted:	5 Dec 2005 15:22	Modified:	12 May 2009 10:39
Reporter:	Kolbe Kegel	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The REPEATABLE READ isolation level is not fully supported by InnoDB, as an ALTER TABLE executed in another thread can interfere with an existing transaction. This problem also occurs when using the SERIALIZABLE transaction isolation level.

An identical SELECT statement executed twice in a transaction with no intervening statements must not return different results.

How to repeat:

-- thread #1
drop table if exists t1,t2;
create table t1 (id int, v char(3)) engine=innodb;
insert into t1 values (1,'a'),(2,'b'),(3,'c');
begin;
select * from t1;

-- thread #2
alter table t1 rename t2;

-- thread #1
select * from t1;
-- ERROR 1146 (42S02): Table 'test.t1' doesn't exist

-----

Also interesting....

-- thread #1
drop table if exists t1,t2;
create table t1 (id int, v char(3)) engine=innodb;
insert into t1 values (1,'a'),(2,'b'),(3,'c');
begin;
select * from t1;
update t1 set v='aaa' where id=1;
select * from t1;

-- thread #2
alter table t1 rename t2;
select * from t2; -- sees original version of table

-- thread #1
select * from t1; -- ERROR 1146 (42S02): Table 'test.t1' doesn't exist
select * from t2; -- sees *modified* version of table
rollback;
select * from t2; -- rollback is successful, even though the table name is different!

Suggested fix:
ALTER TABLE should be blocked until there are no row-level locks on a transactional table.

The same problem has consequences also for the binary log as can be
seen in the example below.

Even though it is not a common scenario that one modifies the same
table twice in the same transaction using different names, we also
have trouble (that the master transaction is serialized *after* the
master1 transaction) without the "insert into t2" statement.

--- TEST SCRIPT ---

source include/master-slave.inc;

connection master;
create table t1 (a int) engine=innodb;
begin;
insert into t1 values (1);

# This statement should not execute, since there is a row-level lock on t1.
connection master1;
alter table t1 rename t2;

connection master;
insert into t2 values (2);
commit;

# In the binlog, it is impossible to serialize the two transactions.
# Which one should be first?
show binlog events;

--- RESULT FILE RBR ---

+ create table t1 (a int) engine=innodb;
+ begin;
+ insert into t1 values (1);
+ alter table t1 rename t2;
+ insert into t2 values (2);
+ commit;
+ show binlog events;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     4       Format_desc     1       102     Server ver: 5.1.2-alpha-debug-log, Binlog ver: 4
+ master-bin.000001     102     Query   1       202     use `test`; create table t1 (a int) engine=innodb
+ master-bin.000001     202     Query   1       289     use `test`; alter table t1 rename t2
+ master-bin.000001     289     Query   1       357     use `test`; BEGIN
+ master-bin.000001     357     Table_map       1       37      test.t1
+ master-bin.000001     394     Write_rows      1       69
+ master-bin.000001     426     Table_map       1       106     test.t2
+ master-bin.000001     463     Write_rows      1       138
+ master-bin.000001     495     Xid     1       522     COMMIT /* xid=10 */

--- RESULT FILE SBR ---

+ create table t1 (a int) engine=innodb;
+ begin;
+ insert into t1 values (1);
+ alter table t1 rename t2;
+ insert into t2 values (2);
+ commit;
+ show binlog events;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     4       Format_desc     1       102     Server ver: 5.1.2-alpha-debug-log, Binlog ver: 4
+ master-bin.000001     102     Query   1       202     use `test`; create table t1 (a int) engine=innodb
+ master-bin.000001     202     Query   1       289     use `test`; alter table t1 rename t2
+ master-bin.000001     289     Query   1       357     use `test`; BEGIN
+ master-bin.000001     357     Query   1       88      use `test`; insert into t1 values (1)
+ master-bin.000001     445     Query   1       176     use `test`; insert into t2 values (2)
+ master-bin.000001     533     Xid     1       560     COMMIT /* xid=9 */

Kolbe,

ALTER TABLE, like DROP TABLE, is not a transactional command. Since the old version of the table is dropped, we cannot return the old value for the SELECT query.

I am changing the status to 'Won't fix', because we will probably never fix this.

Regards,

Heikki

If this will not be fixed before a long time, we should document it if not already done. So I set it to "Documentating". Information for the documentation team:
the problem is that if an InnoDB table is involved in a transaction, for example in a consistent read, or in a not-yet-committed update, another connection can do a DROP TABLE or ALTER TABLE on that table, and that DDL will have immediate effect: the table becomes inaccessible in the first transaction. A long-term solution would be to say that DROP implies DELETE (as Peter Gulutzan was writing once), thus making DROP wait on row locks set by the first transaction (and same for ALTER). About what should happen to the consistent read, we may return an error in the consistent read, or wait until the consistent read is committed (we could check what the standard says and what other RDBMS do).

It'll be fixed on MySQL layer (not in the storage engine) - MySQL needs to track what tables are used in a transaction, and won't allow ALTER or DROP for them.

But it's a big change, it cannot be done in 5.0 :(

Is the problem noted in this report covered by the
last paragraph of this section?

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

Paul, I'm satisfied with the existing documentation (though language for the last couple sentences could be clarified).

Sergei's comments make it sound like this will be fixed at some point, so I am setting this bug to To be fixed later.

This is a yet another manifestation of Bug#989.
Davi, please add a test case for this bug along with your fix.

Closing as a duplicate of Bug#989. A similar test case has been added to the test suite.