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?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With