尝试插入 Oracle DB 时，Java 线程永远保持锁定状态

Question

我们有一个 Java 应用程序，它定期将行插入 Oracle DB。这是一个多线程应用程序。除一个线程外，所有线程都会周期性地卡住。我们正在考虑升级 Oracle JDBC 驱动程序，但我有一种感觉它可能会再次出现。只是想获取一些有关我们的代码是否有错误或其他错误的信息。我有堆栈跟踪和下面的代码部分。我们在线程信息中看到周期性锁定。请给我们一些关于我可能出错的信息。

----代码----

LogEventBatchPreparedStatementUpdater statementUpdater = new LogEventBatchPreparedStatementUpdater(logEvents);

//        _jdbcTemplate.batchUpdate(INSERT_SQL, statementUpdater);
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try
        {
            connection = _dataSource.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(INSERT_SQL);
            for (int i = 0; i < statementUpdater.getBatchSize(); i++)
            {
                statementUpdater.setValues(preparedStatement, i);
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connection.commit();
        }
        catch (SQLException e)
        {
            _Log.error("Error inserting log line batch",e );
        }
        finally
        {
            try
            {
                preparedStatement.close();
                connection.close();
            }
            catch (SQLException e)
            {
                _Log.error("Error inserting log line batch",e );
            }
        }

----堆栈跟踪----

"Thread-258 " daemon prio=6 tid=0x09437400 nid=0x2300 runnable [0x0f55f000]
   java.lang.Thread.State: RUNNABLE
        at java.net.SocketInputStream.socketRead0(Native Method)
        at java.net.SocketInputStream.read(Unknown Source)
        at oracle.net.ns.Packet.receive(Unknown Source)
        at oracle.net.ns.NetInputStream.getNextPacket(Unknown Source)
        at oracle.net.ns.NetInputStream.read(Unknown Source)
        at oracle.net.ns.NetInputStream.read(Unknown Source)
        at oracle.net.ns.NetInputStream.read(Unknown Source)
        at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:931)
        at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:369)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:109
3)
        - locked <0x1ce417c0> (a oracle.jdbc.ttc7.TTC7Protocol)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.ja
va:2047)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.jav
a:1940)
        at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePrepare
dStatement.java:3899)
        - locked <0x18930c00> (a oracle.jdbc.driver.OraclePreparedStatement)
        - locked <0x1ce3f9f0> (a oracle.jdbc.driver.OracleConnection)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingSt
atement.java:294)
        at ************.insertLogEventBatch(JdbcL
ogEventBatchDao.java:61)
        at ************.DBLogEventBatchProcessor.processLo
gLineBatch(DBLogEventBatchProcessor.java:30)
        at ************.LogLineBatcher.processLogLineBatch
(LogLineBatcher.java:274)
        at ************.LogLineBatcher.processBatchBasedOn
Time(LogLineBatcher.java:192)
        at ************.LogLineBatcher.manageBatch(LogLine
Batcher.java:178)
        at ************.LogLineBatcher.access$000(LogLineB
atcher.java:24)
        at ************.LogLineBatcher$1.run(LogLineBatche
r.java:152)
        at java.lang.Thread.run(Unknown Source)

Answer 1

事实上，线程状态为 RUNNABLE，并且它正在尝试从套接字读取，这意味着它只是在等待数据库的响应。所以要调查的是数据库会话正在等待什么。如果您可以在 V$SESSION 视图中识别会话，则 EVENT 列将指示它正在等待什么。似乎数据库中可能存在锁等待。

仅供参考，线程转储显示“已锁定”，例如 locked <0x1ce417c0>，这只是告诉 yu 该线程已获取锁定；我相信十六进制代码是持有锁的对象的 ID。

这里是一些有关解释线程转储的有用信息。