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:18] Petr Chudanic
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 {


		} catch (ClassNotFoundException e) {




	private static void selectRecordsFromDbUserTable() {

		String selectTableSQL = "SELECT * from public.table LIMIT 500";

		try (Connection dbConnection = getDBConnection(); Connection dbConnection2 = getDBConnection()) {

			try (Statement statement = dbConnection.createStatement()) {
				try (ResultSet rs = statement.getResultSet()) {

					while (rs.next()) {

//			dbConnection.close();

			Statement statement2 = dbConnection2.createStatement();
			statement2.execute("DROP TABLE public.table");

		} catch (SQLException e) {




	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {



		return dbConnection;

[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.