Lock wait timeout exceeded; try restarting transaction using JDBC

Question

I have a MySQL table named Student with two columns Student_id and name.

I am firing two queries using two connection objects, and it is giving me an Exception:

Exception in thread "main" java.sql.SQLException: Lock wait timeout 
exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:888)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:730)
    at jdbc.ConnectUsingJdbc.main(ConnectUsingJdbc.java:19)

Here is the code that produces the error:

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectUsingJdbc {

    public static void main(String[] args) 
        throws ClassNotFoundException, SQLException{

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/test","root","root");
        Connection connection1 = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/test","root","root");
        connection.setAutoCommit(false);
        connection1.setAutoCommit(false);
        Statement statement = connection.createStatement();
        statement.execute("insert into student values (3,'kamal')");
        Statement statement1 = connection1.createStatement();
        statement1.execute("delete from student where student_id = 3");
        connection.commit();
        connection1.commit();
    }
}

I am trying to delete the row using the connection1 object that I inserted using the other connection object.

Why am I getting this error?

Solution

Modify your code and reorder the executions as follows. It should work fine:

Statement statement = connection.createStatement();
statement.execute("insert into student values (3,'kamal')");
connection.commit();

Statement statement1 = connection1.createStatement();
statement1.execute("delete from student where student_id = 3");
connection1.commit();

The issue is, previously executed insert statement is not committed yet and holding the lock on the table when you are trying to execute a new delete statement creating a deadlock situation inside DB.

Comments

[Sunny Gupta]

But If I use a single connection object and I create two different statement objects using that. Then there is no deadlock situation arise. Do you have any idea why it is like that? <br/> Statement statement = connection.createStatement(); statement.execute("insert into student values (3,'kamal')"); Statement statement1 = connection.createStatement(); statement1.execute("delete from student where student_id = 3"); connection.commit();

[Bimalesh Jha]

When using a single connection (single session) DBMS can know both statements are coming from same client thread/session- hence it can optimize and avoid deadlock by working on uncommitted data. Pls read about transaction isolation also.

[Sunny Gupta]

If I would have executed this, with two different threads each having different connection object, One is calling insert and one is calling delete, so in that case, there will not be any deadlock. Am I right?