Java SQL Server 应用程序卡在 statements.executeUpdate() 上

Question

我有一个相当烦人的问题。在下面的代码片段中，我尝试将新行插入到数据库中的“RevisionDispersion”表中。但是，每当我调用 stmt.executeUpdate() 时，程序都会卡住，并且最终不会向数据库发送任何事务。无论我等待多久；数据库不会更新。以下是感兴趣的代码:

private static final String INSERT_DISPERSION = "insert into RevisionDispersion(" 
                                              + Assignments.ID + ", " 
                                              + Persons.EMAIL + ", " 
                                              + Handins.ID + ")" 
                                              + " values(?, ?, ?)";

public static void disperse(DataSource source, Assignment assignment) throws Exception
{
    List<String> handins = assignment.getHandins();

    //used to decide who checks which assignment
    int maxRNG = Math.max(1, handins.size() / assignment.getPeerCount());
    int rng = new Random().nextInt(maxRNG);

    PreparedStatement stmt = null;
    Connection con = null;

    try{
        //Get the connection, set it to TRANSACTION_SERIALIZABLE and set autocommit to false
        con = source.getConnection();
        configureConnection(con);

        //Prepare the statement to insert the new dispersion
        stmt = con.prepareStatement(INSERT_DISPERSION);
        stmt.setString(1, assignment.getID());

        //Iterate over all hand-ins and decide from which peer a peer receives feedback
        for(int i = 0; i < handins.size(); i++)
        {
            HandIn handin = new HandIn(source.getConnection(), handins.get(i));
            String student = handin.getEmail();

            stmt.setString(2, student);

            for(int j = 1; j <= assignment.getPeerCount(); j++)
            {
                HandIn otherHandin =  new HandIn(source.getConnection(), handins.get(j * rng));
                stmt.setString(3, otherHandin.getId());
                stmt.executeUpdate();
            }
        }

        con.commit();
    }catch(Exception e){
        throw e;
    }finally{
        closeQuietly(con, stmt);
    }
}

//This method is originally in the DBAO class, but I put it here for you folks.
protected static void configureConnection(Connection connection) throws SQLException
{
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
}

此问题不会出现在应用程序的其他地方。每当我在 SQL Server Management Studio 中使用相同的参数运行 SQL 语句时，它都不会卡住，并且可以很好地插入新行。删除行并在应用程序中尝试相同的操作后，它陷入困境。

任何人都可以指出我出了什么问题的正确方向吗？我已经连续尝试了 3 个小时了...

我已经尝试过的东西

-使用stmt.addBatch()而不是executeUpdate()(没有区别。它会卡在executeBatch())

-检查所有连接是否正确关闭；他们是。

-检查使用 RevisionDispersion 表的其他语句/结果集是否仍然打开(没有仍然打开。即使有，我认为应该不会产生影响？)

-完全删除数据库并重新设置

Answer 1

我解决了这个问题...

在另一段代码中，我有以下内容:

private static final String GET_NOT_DISPERSED = "select * from Assignments where "
                                            + Assignments.CLOSE_DATE + "<=? and " 
                                            + Assignments.PEER_START_DATE + ">=? and "
                                            + Assignments.ID + " not in(select " + Assignments.ID + " from RevisionDispersion)";
private void makeMailDispersion() throws Exception
{
    DateTime currentDate = DateTime.getCurrentDateTime();

    PreparedStatement assignmentsStmt = null;
    ResultSet assignments = null;
    Connection con = null;

    try{
        con = source.getConnection();
        configureConnection(con);
        assignmentsStmt = con.prepareStatement(GET_NOT_DISPERSED);
        assignmentsStmt.setString(1, currentDate.toString());
        assignmentsStmt.setString(2, currentDate.toString());

        assignments = assignmentsStmt.executeQuery();

        ArrayList<Assignment> requiresDispersion = new ArrayList<>();

        assignments.close();
        assignmentsStmt.close();
        while(assignments.next())
        {
            Assignment assignment = new Assignment(source.getConnection(), assignments.getString(Assignments.ID));
            AssignmentDisperser.disperse(source, assignment);
        }
    }catch(Exception e){
        throw e;
    }finally{
        closeQuietly(con, assignmentsStmt, assignments);
    }
}

在这段代码中，我关闭了变量“赋值”和“赋值Stmt”。我认为这足以在使用 GET_NOT_DISPERSED 查询后解锁表。显然不是: table 仍然锁着。

为了解决这个问题，我必须做的事情:除了调用 assignments.close() 和 assignmentsStmt.close() 之外，我还必须调用 con.close()。这完全解锁了表并允许代码正常运行。