Java 超过锁等待超时；尝试使用 JDBC 重新启动事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18854988/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Lock wait timeout exceeded; try restarting transaction using JDBC
提问by Sunny Gupta
I have a MySQL table named Studentwith two columns Student_idand name.
我有一个Student用两列命名的 MySQL 表Student_id和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 connection1object that I inserted using the other connectionobject.
我正在尝试使用connection1我使用其他connection对象插入的对象删除该行。
Why am I getting this error?
为什么我收到这个错误？
采纳答案by Bimalesh Jha
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.
问题是，当您尝试执行新的删除语句时，先前执行的插入语句尚未提交并持有表上的锁，从而在数据库内造成死锁情况。
