JAVA/Oracle SQL 因阻塞而挂起

Question

我正在为我正在创建的投注程序创建一个 Elo 系统，以比较两个竞争对手并确定投注金额。我超时收集了一些数据，需要对所有这些数据进行计算以收集 Elo 分数。

我有大约 1150 条记录需要执行此操作。我最初的方法是查询所有记录，然后使用 while(rs.next()) 对每条记录进行计算，然后使用另一个查询用新值更新表。但程序卡在我的更新语句上。它挂起的位置在下面的代码中用注释“//HANGS RIGHT HERE”标记。有趣的是，它没有给出任何错误或异常。它只是坐在那里，不会再进一步​​发展。我尝试了几种解决方案，但无法提出/研究解决方案。下面的代码代表了整个程序:

package elomaker;

import java.awt.Component;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JOptionPane;

public class EloMaker {

/**
 * @param args the command line arguments
 */
public static void main(String[] args) throws SQLException {

    //initial variables
    Connection con;
    Statement stmt = null;
    Component frame = null;
    String sqlPullFights = "select  f.fid,\n"
            + "        f.p1id,\n"
            + "        f.p2id,\n"
            + "        f.winner,\n"
            + "        (select p.elo from players p where f.p1id = p.pid) p1elo,\n"
            + "        (select pl.elo from players pl where f.p2id = pl.pid) p2elo\n"
            + "from    fights f";
    ResultSet rs = null;
    int x = 0;

    //initializing the database to make sure the datbase is ready.
    String dbURL = "jdbc:oracle:thin:@localhost:1521:orcl";
    String userName = "NEGGLY";
    String password = "Yellow23";
    try {
        Class.forName("oracle.jdbc.OracleDriver");
        con = DriverManager.getConnection(dbURL, userName, password);
        stmt = con.createStatement();
    } catch (ClassNotFoundException e) {
        System.out.println("Couldn't register JDBC driver,");
        System.out.println("Applicaiton Ending.");
        System.exit(-1);
    } catch (SQLException e) {
        JOptionPane.showMessageDialog(frame, "Could not establish a connection to the database", "Database Error", +JOptionPane.ERROR_MESSAGE);
        System.exit(-1);
    }

    //pull the fight values for each player
    rs = stmt.executeQuery(sqlPullFights);
    while (rs.next()) {
        int intP1ID = rs.getInt("P1ID");
        int intP2ID = rs.getInt("P2ID");
        int intWinner = rs.getInt("WINNER");
        int intP1Elo = rs.getInt("P1ELO");
        int intP2Elo = rs.getInt("P2ELO");
        int intP1NewRating, intP2NewRating;
        double dblP1Outcome, dblP2Outcome;

        dblP1Outcome = 1 / (1 + (Math.pow(10, (intP2Elo - intP1Elo) / 400)));
        dblP2Outcome = 1 - dblP1Outcome;

        //determine the winner
        if (intP1ID == intWinner) {
            intP1NewRating = (int) (intP1Elo + 32 * (1-dblP1Outcome));
            intP2NewRating = (int) (intP2Elo + 32 * (0-dblP2Outcome));
        } else {
            intP2NewRating = (int) (intP2Elo + 32 * (1-dblP2Outcome));
            intP1NewRating = (int) (intP1Elo + 32 * (0-dblP1Outcome));
        }

        String sqlUpdP1Rate = "update players set elo = "+intP1NewRating+" where pid = " + intP1ID;
        String sqlUpdP2Rate = "update players set elo = "+intP2NewRating+" where pid = " + intP2ID;

        stmt.executeQuery(sqlUpdP1Rate); //HANGS RIGHT HERE.
        stmt.executeQuery(sqlUpdP2Rate);
        stmt.executeQuery("commit");

        x++;
        System.out.println(x);
    }

}

}

任何有关此问题的帮助将不胜感激。

Answer 1

当应用程序被阻止时执行此查询:

select /* +rule */
  s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' ' || s1.program || ' )  is blocking ' 
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ' || s2.program 
    || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

这将告诉您哪个 session 持有您正在等待的锁。

PS:也许使用 con.commit(); 来提交您的交易。您也不应该跨提交获取。将提交放在脚本的最后(循环之外)。但无论如何，我认为 JDBC 连接默认启用了自动提交(尝试将其关闭)。