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:
None 
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
Description:
Concurrent execution of transactions and DDL statements for coalesce partition will immediately detect unnecessary deadlocks.

How to repeat:
schema:
```
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 ;
```

Execute parallel: | Txn1                                                         | Session2                                 | | ------------------------------------------------------------ | ---------------------------------------- | | begin;                                                       | alter table table0 coalesce partition 1; | | 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  ); |                                          | | Commit;  -- DeadLock Found                                   | -- return ok;                            |
[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.