java - 如何安全地终止已超时的查询

Question

我正在使用 PostgreSQL JDBC，并且有一些选择和插入查询的连接。有些查询需要一些时间，所以我添加了超时。问题是超时关闭了连接，但查询仍在数据库服务器中执行并创建了锁。

问题的简化代码(真正的代码更复杂、更大，但这并不重要):

    PGPoolingDataSource source = new PGPoolingDataSource();
    source.setUrl(url);
    source.setUser(user);
    source.setPassword(password);
    source.setMaxConnections(10);
    source.setSocketTimeout(5); // Timeout of 5 seconds

    // Insert a row data, and make timeout
    Connection con = source.getConnection();
    con.setAutoCommit(false);
    try {
        Statement st2 = con.createStatement();
        st2.execute("insert into data.person values (4, 'a')");

        Statement st3 = con.createStatement();
        st3.executeQuery("select pg_sleep(200)"); // A query which takes a lot of time and causes a timeout
        con.commit();
        con.close();
    } catch (SQLException ex) {

        if (!con.isClosed()) {
            con.rollback();
            con.close();
        }
        ex.printStackTrace();
    }


    Connection con2 = source.getConnection();
    con2.setAutoCommit(false);
    try {
        Statement st2 = con2.createStatement();

        // This insert query is locked because the query before is still executed, and the rollback didn't happen yet, and the row with the id of 4 is not released
        st2.execute("insert into data.person values (4, 'b')");
        con2.commit();
        con2.close();
    } catch (SQLException ex) {

        if (!con2.isClosed()) {
            con2.rollback();
            con2.close();
        }
        ex.printStackTrace();
    }

(data.person 是一个包含 id 和 name 的表。)

超时关闭连接，甚至没有到达 con.rollback(); 行。我读到，当查询发生异常时，它会在后台回滚，所以没关系。

但是查询需要花费大量时间(几个小时)，因此，回滚将在大选择查询完成后发生。因此，我在几个小时内无法将行添加到 data.person (第二次尝试插入时，我收到超时异常，因为它等待锁被释放......) .

我读到可以使用 PostgreSQL 中的函数 pg_terminate_backend 来终止查询，这样我就可以第二次执行插入查询。

我的问题是:

1)它的安全性如何？

2) 这个解决方案有多常见？

3) JDBC 或 PostgreSQL 是否提供更安全的解决方案？

Answer 1

pg_terminate_backend 将起作用，如果您想中断查询并关闭数据库连接，这是安全且正确的过程。

还有 pg_cancel_backend 它将中断查询但保持连接打开。

这些函数要求您知道 session 后端进程的进程 ID，您可以使用 pg_backend_pid 函数获取该进程 ID。

您必须在与原始数据库连接不同的数据库连接上运行这些语句!

另一种可能更简单的方法是设置statement_timeout。这可以在配置文件中设置，也可以针对单个 session 或事务进行设置。要将其设置为事务，请使用:

BEGIN;  -- in JDBC, use setAutoCommit(false)
SET LOCAL statement_timeout = 30000;  -- in milliseconds
SELECT /* your query */;
COMMIT;