Bug #117735 Concurrent execution of transactions and DDL operations causes abnormal deadlocks.
| Submitted: | 18 Mar 2025 6:21 | Modified: | 18 Mar 2025 12:33 |
|---|---|---|---|
| Reporter: | Hongyu Yang | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 8.4, 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any |
[18 Mar 2025 6:21]
Hongyu Yang
[18 Mar 2025 6:28]
Hongyu Yang
To reproduce this concurrent execution bug, you can running the following java code:
import org.junit.Test;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
public class MysqlCoalescePartitionTest {
private static final Integer THREAD_NUM = 30;
private static ExecutorService threadPool;
private static final String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/test?useServerPrepStmts=true&cachePrepStmts=true";
private static final String username = "root";
private static final String password = "root";
@Test
public void test() throws ClassNotFoundException, SQLException, IOException {
boolean isSuccess = true;
int cnt = 0;
while (isSuccess) {
threadPool = Executors.newFixedThreadPool(THREAD_NUM);
System.out.println("loop: " + cnt++);
List<Connection> connectionList = new ArrayList<>(THREAD_NUM);
Class.forName("com.mysql.cj.jdbc.Driver");
for (int i = 0; i < THREAD_NUM; i++) {
Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
connectionList.add(connection);
}
List<Future<Boolean>> futures = new ArrayList<>();
initSchemaAndData(connectionList.get(0));
// execute parallel
futures.add(executeParallel(connectionList.get(1), "alter table table0 coalesce partition 1;"));
executeTxn1(connectionList.get(2));
for (Future<Boolean> future : futures) {
try {
future.get();
} catch (Exception e) {
System.out.printf("DDL return: %s%n", e.getMessage());
}
}
for (int i = 0; i < THREAD_NUM; i++) {
if (!connectionList.get(i).isClosed()) {
connectionList.get(i).close();
}
}
threadPool.shutdown();
}
}
// parallel execute
private static Future<Boolean> executeParallel(Connection connection, String query) throws SQLException {
Statement statement = connection.createStatement();
return threadPool.submit(() -> statement.execute(query));
}
// init schema
private static void initSchemaAndData(Connection connection) throws SQLException {
String[] sqlStatements = {
"drop table if exists table0;",
"create table table0 (pkId integer, pkAttr0 integer, commonAttr0_0 integer, commonAttr1_0 varchar(10), commonAttr2_0 double(10, 2), commonAttr3_0 varchar(10), commonAttr4_0 varchar(10), primary key(pkAttr0)) ROW_FORMAT = DYNAMIC PARTITION BY HASH(pkAttr0) PARTITIONS 4 ;"
};
try (Statement statement = connection.createStatement()) {
for (String sql : sqlStatements) {
String trimmedSql = sql.replace(";", "").trim();
if (!trimmedSql.isEmpty()) {
try {
statement.execute(trimmedSql);
} catch (SQLException e) {
System.err.println("Failed to execute SQL: " + trimmedSql);
}
}
}
}
System.out.println("Schema and data initialized.");
}
private static void executeTxn1(Connection connection) throws SQLException {
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
connection.setAutoCommit(false);
String[] sqlStatements = {
"select `pkId`, `commonAttr4_0`, `pkAttr0` from `table0` where ( `pkAttr0` = 77 ) ;",
"select `pkId`, `commonAttr4_0`, `pkAttr0`, `commonAttr0_0` from `table0` where ( `commonAttr4_0` = \"ZgZ55RG\" ) ;",
"select `pkId`, `commonAttr0_0`, `commonAttr1_0`, `commonAttr2_0` from `table0` where ( `pkAttr0` = 28 ) ;",
"select `pkId`, `commonAttr2_0` from `table0` where ( `pkAttr0` = 121 ) ;",
"select `pkId`, `pkAttr0`, `commonAttr0_0`, `commonAttr1_0` from `table0` where ( `pkAttr0` = 105 ) ;",
"update `table0` set `commonAttr1_0` = \"CLf\", `commonAttr2_0` = 19502.0, `commonAttr3_0` = \"cFvJfNc\" where ( `pkAttr0` = 132 );",
"update `table0` set `commonAttr0_0` = 16, `commonAttr1_0` = \"cmh\" where ( `pkAttr0` = 69 );"
};
for (String sql : sqlStatements) {
try (PreparedStatement statement = connection.prepareStatement(sql)) {
if (sql.trim().toLowerCase().startsWith("select")) {
statement.executeQuery();
} else {
statement.executeUpdate();
}
}
}
connection.commit();
}
}
[18 Mar 2025 6:41]
Hongyu Yang
Another case that makes it easier to reproduce the bug: -- Create a partitioned table (empty table) DROP TABLE IF EXISTS table0; CREATE TABLE table0 (pkId INTEGER, pkAttr0 INTEGER, commonAttr0_0 INTEGER, commonAttr1_0 VARCHAR(10), commonAttr2_0 DOUBLE(10, 2), commonAttr3_0 VARCHAR(10), commonAttr4_0 VARCHAR(10), PRIMARY KEY(pkAttr0)) ROW_FORMAT = DYNAMIC PARTITION BY KEY(pkAttr0) PARTITIONS 4; -- Execute the following workloads sequentially -- Execute the transaction workload in session 1 BEGIN; SELECT `pkId`, `commonAttr4_0`, `pkAttr0` FROM `table0` WHERE (`pkAttr0` = 77); --- Execute the DDL in another session, session 2 ALTER TABLE table0 COALESCE PARTITION 1; --- Return to session 1 to continue executing the same transaction UPDATE `table0` SET `commonAttr1_0` = "CLf", `commonAttr2_0` = 19502.0, `commonAttr3_0` = "cFvJfNc" WHERE (`pkAttr0` = 132); -- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction. txid: 29230581636137050. sql-node: node-9-002. proxy-executor: nil -- A deadlock is detected and the above exception is thrown.
[18 Mar 2025 7:11]
Hongyu Yang
The following online DDL can also cause wrong deadlock detect: -- reorg ALTER TABLE table0 REORGANIZE PARTITION P0 INTO(PARTITION p5); -- add partition alter table table0 add partition partitions 1; -- coalesce partition alter table table0 coalesce partition 1;
[18 Mar 2025 12:33]
MySQL Verification Team
Hello Hongyu Yang, Thank you for the report and feedback. regards, Umesh
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.