| Bug #117735 | Concurrent execution of transactions and DDL operations causes abnormal deadlocks. | ||
|---|---|---|---|
| Submitted: | 18 Mar 6:21 | Modified: | 18 Mar 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 6:21]
Hongyu Yang
[18 Mar 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 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 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 12:33]
MySQL Verification Team
Hello Hongyu Yang, Thank you for the report and feedback. regards, Umesh
