如何处理 FATAL: 由于空闲事务超时而终止连接
我有一种方法，其中一些数据库操作是通过一些 API 调用进行的。这是 Spring 和 postgres DB 的场景。我们还在 postgres DB 中为空闲事务设置了属性
idle_in_transaction_session_timeout = 10min
现在的问题是我有时会遇到异常
org.postgresql.util.PSQLException：此连接已关闭。根本原因是致命的：由于事务空闲超时而终止连接
例如，我的代码如下所示：
@Transactional(value = "transactionManagerDC")
public void Execute()
{
// 1. select from DB - took 2 min
// 2. call another API - took 10 min. <- here is when the postgres close my connection
// 3. select from DB - throws exception.
};
正确的设计是什么？我们使用 API 调用中步骤 1 的输出进行选择，并使用步骤 3 中使用的 API 调用的输出进行选择。所以这三个步骤是相互依赖的。
I have one method where some DB operations are happened with some API call. This is a scenario with Spring and postgres DB. We also have property set for idle transaction in postgres DB
idle_in_transaction_session_timeout = 10min
Now the issue is I do get Exception sometime
org.postgresql.util.PSQLException: This connection has been closed. Root Cause is FATAL: terminating connection due to idle-in-transaction timeout
For example, my code look like this:
@Transactional(value = "transactionManagerDC")
public void Execute()
{
// 1. select from DB - took 2 min
// 2. call another API - took 10 min. <- here is when the postgres close my connection
// 3. select from DB - throws exception.
};
What could be the correct design for it? We are using output for select from step 1 in API call and output of that API call used in step 3 for select. So these three steps are interdependent.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
十分钟对于保持交易开放来说是很长的时间。您的 RDBMS 服务器会自动断开您的会话，回滚事务，因为它无法判断事务是由交互式（命令行）用户启动，然后出去吃午饭而没有提交它，还是由像您这样的长时间运行的任务启动。
开放事务可能会阻止 RDBMS 的其他用户，因此最好快速提交它们。
最好的解决方案是重构应用程序代码，以便它可以在其他 API 调用十分钟响应后开始并快速提交事务。
在不了解更多信息的情况下很难给你具体的建议。但是，您可以在调用该慢速 API 之前在行上设置某种
status = "API call in Progress"列，并在 API 调用完成后清除事务中的该状态。或者，您可以使用类似的方法为该连接设置事务超时，以降低系统上的午餐风险。
Ten minutes is a very long time to hold a transaction open. Your RDBMS server automatically disconnects your session, rolling back the transaction, because it cannot tell whether the transaction was started by an interactive (command-line) user who then went out to lunch without committing it, or by a long-running task like yours.
Open transactions can block other users of your RDBMS, so it's best to COMMIT them quickly.
Your best solution is to refactor your application code so it can begin, and then quickly commit, the transaction after the ten-minute response from that other API call.
It's hard to give you specific advice without knowing more. But you could set some sort of
status = "API call in progress"column on a row before you call that slow API, and clear that status within your transaction after the API call completes.Alternatively, you can set the transaction timeout for just that connection with something like this, to reduce the out-to-lunch risk on your system.