Bug #111656 Lock failure

• Submitted: 4 Jul 2023 12:32
• Modified: 4 Jul 2023 13:04
• Reporter: linfeng chen
• Status: Unsupported
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 8.0.25
• OS: Any
• CPU Architecture: Any

[4 Jul 2023 12:32] linfeng chen

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

[4 Jul 2023 13:04] MySQL Verification Team

Hi Mr. chen,

Thank you for your bug report.

However, this is not a bug.

Secondary engine is supported only for the RAPID storage (secondary) engine. At this moment, no other secondary engine is supported.

You could specify any other name for the secondary_engine, but none are.  supported except for RAPID. , Right now, RAPID is the only engine that is it intended for use. Usage for engine of any other name  is not supported nor is it documented. Hence, if you use it , it is at your own peril.

Unsupported.

[5 Jul 2023 12:22] MySQL Verification Team

Hi,

One more comment.

If you read our Ref. Manual, you would have known that all DDL operations, including ALTER TABLE, commit current transaction implicitly.