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);