Tomcat jdbc pooling with Spring

Question

I am getting this warning:

WARNING:[PoolCleaner[661646649:1440675349770]] org.apache.tomcat.jdbc.pool.ConnectionPool.abandon Connection has been abandoned PooledConnection[org.postgresql.jdbc4.Jdbc4Connection@7c9e8e2e]: java.lang.Exception

For the application, I am using spring framework with Tomcat JDBC pooling and Postgres as database. My configuration file :

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"> <property name="driverClassName" value="org.postgresql.Driver" /> <property name="url" value="jdbc:postgresql://ip/db" /> <property name="username" value="user" /> <property name="password" value="pass" /> <property name="initialSize" value="5" /> <property name="maxActive" value="10" /> <property name="maxIdle" value="10" /> <property name="testOnBorrow" value="true" /> <property name="validationInterval" value="30000" /> <property name="minIdle" value="5" /> <property name="removeAbandoned" value="true" /> <property name="removeAbandonedTimeout" value="60" /> <property name="logAbandoned" value="true" /> <property name="validationQuery" value="SELECT 1" /> <property name="initSQL" value="SET application_name = 'app'" /> </bean>

Here is the problem. From my understanding removeAbandoned should only abandon connections which are in use more than 60 seconds, that is queries that run more than 60 seconds. I do not have such queries at all. But my connections from pool get abandoned.

I suspect that removeAbandoned is removing idle connections in the pool. Though it is not supposed to do so. In my application i am getting connection this way:

//i am using autowiring. @Autowired private DataSource dataSource; //and in my methods i use it this way: Connection conn = dataSource.getConnection(); //and i am closing it after method completion //maybe i should not?? conn.close()

UPDATE: I found the error. My Connection variable was global, i made it local to method and everything worked out. Also tested by Apache benchmark, pooling is used. (connections are updated, seen by pg_stat_activity). Thanks for replies.

why am i getting downvotes? Can you guys at least explain your reasons? So that, next time i will not repeat the same mistake. — rone
– rone, Commented Aug 27, 2015 at 12:57

Kayaman · Accepted Answer · 2015-08-27 12:49:57Z

1

If the connection is not used during X seconds (the timeout) and isn't returned to the pool, it's considered to be abandoned, i.e. misbehaving code left a connection open (or rather didn't return it to the pool).

Idle connections aren't abandoned (or at least they very well shouldn't be), since they aren't in use.

It's either a bug in the connection pool (unlikely for such a standard functionality) or somewhere there's a connection being used improperly. It may not be obvious.

You can also monitor the connections from Postgres' side, with select * FROM pg_stat_activity;. You should see nicely the ones that are waiting in the pool, since they show the SELECT 1 validation query.

edited Aug 27, 2015 at 12:49

answered Aug 27, 2015 at 12:43

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

rone Over a year ago

no misbehaving code, i made sure that connections are closed. And in spring i do not think there is a way to explicitly return connection to the pool, i.e it is returned automatically, after i close the connection.

Kayaman Over a year ago

Well closing the connection is returning it to the pool, although semi-explicitly. The wrapper class overrides close(), so the connection isn't actually closed. Hold on for an edit...

rone Over a year ago

Kayaman, i am already monitoring connections using that, but thanks for suggestion. Well, i am pretty sure that i am closing the connection. I am checking whether it is closed. [Connection.isClosed()]

rone Over a year ago

select * from pg_stat_activity shows connections which are idle right(connections that are in pool i think)? Does it show abandoned connections? Or it considers abandoned connections as idle?

Kayaman Over a year ago

Postgres doesn't know about abandoned connections, nor does it care. It's either connected or not. An abandoned connection should show the last query performed and the query_start would be up to timeout seconds ago. But if a connection is checked out of the pool, not used and never returned, it would still show the SELECT 1 query as the last query.

|

Collectives™ on Stack Overflow

Tomcat jdbc pooling with Spring

1 Answer 1

9 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Related