在pg_stat_activity中使用'idle'状态的持久'COMMIT'查询 [英] Long lasting 'COMMIT' queries with 'idle' state in pg_stat_activity

问题描述

如果我查询：

  select * from pg_stat_activity where application_name〜'example-application'; 
  

我得到很多行，其状态是空闲并且查询是 COMMIT 。它们持久而且不会消失。一段时间后，我的应用程序达到 hibernate.c3p0.max_size （池中JDBC连接的最大数量）限制并停止使用数据库。

有些应用程序实现细节在其他SO线程中描述：

为什么会发生？如何解决这个问题呢？

解决方案

如果会话空闲，查询列会显示连接已执行的 last 。 不是当前查询，所以连接不是等待提交完成。 $ b

如果列查询列仅显示当前状态显示有效。

空闲连接不是问题，基本上是为什么使用连接池的原因，以便它可以重新使用。然而，在闲置交易中保持很长时间的会话是一个问题。但是你说你的连接处于空闲状态。

如果连接池达到限制，这很可能意味着您的应用程序没有正确地将连接正确地返回到池中。这是你需要在你的应用程序中修复的东西。

If I query:

select * from pg_stat_activity where application_name ~ 'example-application';

I get many rows which state is idle and query is COMMIT. They are long lasting and do not disappear. After some time, my application reach hibernate.c3p0.max_size (maximum number of JDBC connections in the pool) limit and stops working with database.

Some application implementation details are described in other SO thread: Guice DAO Provider in thread pool - queries become 'idle in transation'

Why does it happen? How to solve this problem?

解决方案

If the session is "idle" the query column shows the last statement that connection has executed. It is not the "current" query, so the connection is not waiting for the commit to finish.

The query column only shows the current statement if status shows active.

An "idle" connection is not a problem and essentially the reason why a connection pool is used, so that it can be re-used. However, sessions that stay very long in "idle in transaction" are a problem. But you stated your connections are in "idle" state.

If your connection pool reaches the limit, this most probably means your application is not returning the connections properly to the pool. It is something you need to fix in your application.

这篇关于在pg_stat_activity中使用'idle'状态的持久'COMMIT'查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！