Spring + c3p0 + postgres

Question

I am using Spring 3.0.5.RELEASE and Postgres 9.1. I am limiting a maximum number of connections in 17:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
  <property name="driverClass" value="${database.driver}" />
  <property name="jdbcUrl" value="${database.url}" />
  <property name="user" value="${database.username}" />
  <property name="password" value="${database.password}" />
  <property name="initialPoolSize" value="3" />
  <property name="minPoolSize" value="1" />
  <property name="maxPoolSize" value="17" />
</bean>

When my application gets to occupy all the connections, it just hangs, and doesn't cancel or accept more transactions. All processes in Postgres are in state "idle in transaction".

Thank you!

You haven't given us much to work with. "Idle in transaction" should be a *very* transient state -- when a connection sits in that state for any length of time, it generally represents a very serious bug in application code, something you haven't shown us. — kgrittn, Aug 02 '12 at 20:20

score 2 · Answer 1 · answered Aug 02 '12 at 20:25

"idle in transaction" means that you started a transaction but never committed it or rolled it back. Make sure you're closing all of your statements when you're done using them. If its not obvious what statements you are holding open, modify your postgresql.conf file to log the process id of your queries with the following settings:

log_line_prefix = '%p'
log_statement = 'all'

When you reload PostgreSQL and check the log files, you'll be able to see all of the queries issued by the connections that are hung in the "idle in transaction" state.

score 1 · Accepted Answer · answered Aug 02 '12 at 22:21

The symptoms you describe are consistent with your application's leaking Connections. That is, your application is checking out Connections, but failing to check them back in [that is, failing to call close()].

Are you consistently using the robust resource cleanup idiom? See e.g

http://old.nabble.com/Re:-My-connections-are-all-idle...-p27691635.html

As suggested in that link, try temporarily setting an unreturnedConnectionTimeout and using
debugUnreturnedConnectionStackTraces to track down the leak. See

http://www.mchange.com/projects/c3p0/index.html#unreturnedConnectionTimeout

http://www.mchange.com/projects/c3p0/index.html#debugUnreturnedConnectionStackTraces

I hope this helps!

Spring + c3p0 + postgres

2 Answers2

Linked