Bug #111068 Lock failure

• Submitted: 18 May 2023 8:38
• Modified: 18 May 2023 9:44
• Reporter: linfeng chen
• Status: Verified
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 8.0.25, 8.0.33
• OS: Any
• CPU Architecture: Any

[18 May 2023 8:38] 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);

[18 May 2023 9:44] MySQL Verification Team

Hello linfeng chen,

Thank you for the report and test case.

regards,
Umesh

[14 May 8:21] Michael Liu

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