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

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会中断查询，但使连接保持打开状态。

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

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

另一种可能更简单的方法是 设置statement_timeout.这可以在配置文件中设置，也可以为单个会话或事务设置。若要为事务设置它，请使用：

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