超过了锁定等待超时；尝试使用JDBC重新启动事务 [英] Lock wait timeout exceeded; try restarting transaction using JDBC

问题描述

我有一个名为Student的MySQL表，其中有两列Student_id和name.

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();
    }
}

我正在尝试使用我使用其他connection对象插入的connection1对象删除该行.

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

为什么会出现此错误?

推荐答案

修改代码并按如下方式重新排列执行顺序.它应该可以正常工作:

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();

问题是，当您尝试执行新的delete语句在DB内部创建死锁情况时，尚未执行以前执行的insert语句并在表上保持锁定.

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.

这篇关于超过了锁定等待超时；尝试使用JDBC重新启动事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！