Derby数据库执行TRUNCATE TABLE时报无法在请求时间内获取锁如何解决

操作Derby数据库的brother_reports表时，执行清空表、删除表操作均返回错误：A lock could not be obtained within the time requested（请求时间范围内无法获取锁），其余表操作正常。

错误根本原因 #

• 资源泄漏：原有代码中查询表是否存在的ResultSet对象使用后未关闭，异常场景下Statement、事务连接未正常释放，导致锁被长期持有
• 事务未回滚：执行操作抛出异常时，手动提交模式下的事务既没有提交也没有回滚，挂起的事务会一直持有表锁，后续操作无法获取锁
• 排他锁冲突：Derby的TRUNCATE TABLE操作需要获取排他表锁，只要有其他未提交的事务持有该表的共享读锁或写锁，就会触发锁超时

修复方案 #

1. 改造删除函数，使用try-with-resources自动释放资源、新增异常回滚逻辑 #

优化后的代码如下：

String TABLE_NAME = "brother_reports";
try {
    conn.setAutoCommit(false);
    // try-with-resources会自动关闭ResultSet、Statement，无需手动调用close
    try (Statement stmt = conn.createStatement();
         ResultSet tables = conn.getMetaData().getTables(null, null, TABLE_NAME.toUpperCase(), null)) {
        if (tables.next()) {
            String query = "TRUNCATE TABLE brother_reports";
            stmt.executeUpdate(query);
            conn.commit();
            return true;
        } else {
            conn.commit();
            return false;
        }
    } catch (SQLException e) {
        // 异常时强制回滚事务，避免挂起的事务持有锁
        conn.rollback();
        System.out.println("Error DeleteBrotherReportsTable " + e.getMessage());
        return false;
    }
} catch (SQLException e) {
    System.out.println("Transaction operation error " + e.getMessage());
    return false;
}

2. 同步优化建表函数的资源释放逻辑 #

建表函数存在相同的ResultSet未关闭问题，同样使用try-with-resources改造即可：

String TABLE_NAME = "brother_reports";
try (Statement stmt = conn.createStatement();
     ResultSet tables = conn.getMetaData().getTables(null, null, TABLE_NAME.toUpperCase(), null)) {
    if (tables.next()) {
        System.out.println("Tabela " + TABLE_NAME + " već postoji");
    } else {
        String query = "CREATE TABLE brother_reports( "
                + "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
                + "device_id INT NOT NULL, "
                + "location VARCHAR(255), "
                + "pageCounter BIGINT NOT NULL, "
                + "drumNumber BIGINT NOT NULL, "
                + "avgCoverageAll FLOAT NOT NULL, "
                + "avgCoverageCurrent FLOAT NOT NULL, "
                + "avgCoverageLast FLOAT NOT NULL, "
                + "drumLTCounter BIGINT NOT NULL, "
                + "drumLTPercentage FLOAT NOT NULL, "
                + "staplerLTCounter BIGINT NOT NULL, "
                + "staplerLTPercentage FLOAT NOT NULL, "
                + "laserLTCounter BIGINT NOT NULL, "
                + "laserLTPercentage FLOAT NOT NULL, "
                + "kompMP BIGINT NOT NULL, "
                + "kompMPPercentage FLOAT NOT NULL, "
                + "komp1 BIGINT NOT NULL, "
                + "komp1Percentage FLOAT NOT NULL, "
                + "toner FLOAT NOT NULL, "
                + "A4Counter BIGINT NOT NULL, "
                + "scanCounter BIGINT NOT NULL, "
                + "drumReplace INT NOT NULL, "
                + "staplerReplace INT NOT NULL, "
                + "laserReplace INT NOT NULL, "
                + "kompMPuvlak INT NOT NULL, "
                + "kompUvlak INT NOT NULL, "
                + "tonerReplace INT NOT NULL, "
                + "mjesec INT NOT NULL, "
                + "godina INT NOT NULL,"
                + "status INT NOT NULL,"
                + "PRIMARY KEY (Id))";
        stmt.execute(query);
    }
} catch (SQLException e) {
    System.out.println("Error setupServicesTable " + e.getMessage());
}

3. 额外排查项 #

如果改造后仍有锁超时问题，可做以下排查：

• 检查所有操作brother_reports表的业务逻辑，是否存在事务未提交/回滚的情况，哪怕是只读查询，在手动提交的连接上执行也会持有共享锁
• 临时调整Derby锁超时时间验证，执行语句SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.locks.waitTimeout', '30')，单位为秒，延长锁等待时间
• 确认没有其他进程/会话正在操作该表，比如开发环境中的数据库连接工具打开了该表的查询事务未提交

内容的提问来源于stack exchange，提问作者Armin Brkić