| Bug #94350 | JDBC deadlocks occurs with autocommit=off and DDL after DML | ||
|---|---|---|---|
| Submitted: | 15 Feb 2019 16:18 | Modified: | 13 Sep 2019 9:30 |
| Reporter: | Petr Chudanic | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
[15 Feb 2019 16:20]
Petr Chudanic
Apology for the wording - I do not know if it is the deadlock, only that the executions hangs in the driver
[6 Mar 2019 8:38]
Alexander Soklakov
Hi Petr, Which driver and server versions do you use?
[13 Mar 2019 8:17]
Petr Chudanic
reproduced on version 8.0.11 with driver mysql-connector-java-8.0.12.jar version 5.7.11 with driver mysql-connector-java-5.1.40-bin.jar version 5.7.11 with driver mysql-connector-java-5.1.46.jar version 5.7.11 with driver mysql-connector-java-8.0.12.jar I haven't tried more but seems like common issue
[13 Sep 2019 9:30]
Alexander Soklakov
Hi Petr, It's not a bug. The DELETE transaction is waiting for SELECT transaction cmpletion to acquire the exclusive lock on the table. As mentioned on https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html "If autocommit mode is disabled within a session with SET autocommit = 0, the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts." So you should either issue statement.execute("COMMIT") or do dbConnection.commit() after reading all results from SELECT if you can't close the connection.
Description: When two different connections with autocommit off are used and select is issued in connection #1 on some table and then connection #2 tries to alter/drop that table it leads to deadlock. Closing connection #1 before executin DDL onconnection #2 prevents the issue, but it is not always possible. How to repeat: Create table named table in public schema. Modify attached code by specifying jdbc connections string and having JDBC driver on the classpath.Then run it. import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Test { private static final String DB_DRIVER = "com.mysql.jdbc.Driver"; private static final String DB_CONNECTION = "jdbc:mysql://XXXXX"; private static final String DB_USER = "root"; private static final String DB_PASSWORD = "root"; public static void main(String[] argv) { try { Class.forName(DB_DRIVER); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } selectRecordsFromDbUserTable(); } private static void selectRecordsFromDbUserTable() { String selectTableSQL = "SELECT * from public.table LIMIT 500"; try (Connection dbConnection = getDBConnection(); Connection dbConnection2 = getDBConnection()) { try (Statement statement = dbConnection.createStatement()) { statement.execute(selectTableSQL); try (ResultSet rs = statement.getResultSet()) { while (rs.next()) { } } } // dbConnection.close(); Statement statement2 = dbConnection2.createStatement(); statement2.execute("DROP TABLE public.table"); } catch (SQLException e) { System.out.println(e.getMessage()); } } private static Connection getDBConnection() { Connection dbConnection = null; try { dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD); dbConnection.setAutoCommit(false); return dbConnection; } catch (SQLException e) { System.out.println(e.getMessage()); } return dbConnection; } }