Bug #94350 JDBC deadlocks occurs with autocommit=off and DDL after DML
Submitted: 15 Feb 16:18 Modified: 13 Mar 8:17
Reporter: Petr Chudanic Email Updates:
Status: Open Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version: OS:Any
Assigned to: CPU Architecture:Any

[15 Feb 16:18] Petr Chudanic
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;

	}
}
[15 Feb 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 8:38] Alexander Soklakov
Hi Petr,

Which driver and server versions do you use?
[13 Mar 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