Bug #111068 Lock failure

Description:

connection A
mysql> create table t1(a int primary key, b int unique, c int) engine=InnoDB;
Query OK, 0 rows affected (0.75 sec)

mysql> insert into t1 values(1,1,1),(3,3,3),(5,5,5);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 secondary_engine = test;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where a > 2 for update;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 3 |    3 |    3 |
| 5 |    5 |    5 |
+---+------+------+
2 rows in set (0.00 sec)

connection B
mysql> start transaction;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(9,9,9);
Query OK, 1 row affected (0.00 sec)

Connection B can insert data, should fail

How to repeat:
connection A
create table t1(a int primary key, b int unique, c int) engine=InnoDB;
insert into t1 values(1,1,1),(3,3,3),(5,5,5);
start transaction;
alter table t1 secondary_engine = test;
select * from t1 where a > 2 for update;

connection B
start transaction;
insert into t1 values(9,9,9);

Hello linfeng chen,

Thank you for the report and test case.

regards,
Umesh

well, after you execute DDL in connection A, the current transaction has been automatically committed. so Connection B can insert data.