DDL/trx conflict cannot be detected

[Alex Yurchenko]

MySQL 5.1 has a problem detecting conflict between DDL and transaction issued from different sessions. For example:

S1> create table test.t1 (i int auto_increment primary key) engine=innodb;
S1> create table test.t2 (i int auto_increment primary key) engine=innodb;
S1> begin;
S1> insert into test.t1 values(default);

S2> drop table test.t1;
Query OK, 0 rows affected (0.01 sec)

S1> insert into test.t2 values(default);
S1> commit;
Query OK, 0 rows affected (0.01 sec)

Where S1 and S2 are different sessions. As a result, the above transaction generates and event that cannot be applied on slave since DROP TABLE event will be serialized before transaction and table test.t1 will be deleted by it.

MySQL 5.5 seems to add metadata locking that would enable to detect the conflict on master. However supposedly the conflict resolution is such that DDL waits for all transactions accessing the table in question to complete. That would cause deadlock in TO isolation since DDL will have to be executed in a predefined order. Instead of waiting for conflicting transactions to complete, DDL should abort them.

[Alex Yurchenko (ayurchen)]

This is confirmed in 5.1 and probably is not fixable - it is a MySQL bug