1

I'm running some Groovy SQL in a Grails service and often get the following error message:

org.springframework.transaction.interceptor.TransactionInterceptor - Application exception overridden by commit exception java.sql.SQLException: Connection has already been closed.

From what I can tell, the JDBC Connection Pool is configured to remove unused connections often (and reset the abandoned timer every time the connection is used). I think this is the root cause of my trouble - the connection gets closed by the pool but my code doesn't know this. I'd like to know if there is a way to deal with this from the code and not by changing the connection pool configuration.

My code goes something like this (excuse the sql2 variable name)...

def sql = new Sql(dataSource)
sql.firstRow ...
sql.close()

// long running non-db code (an api call)

def sql2 = new Sql(dataSource)
sql2.executeUpdate ... // THIS IS WHERE THE ERROR HAPPENS
sql2.close

My thinking was that new Sql(dataSource) will give me a new connection, but it looks like I'm wrong about this. Is there a way to get a new connection, or deal with this problem in some other way?

Pool config:

maxIdle="0"
maxWait="30000"
removeAbandoned="true"
testOnBorrow="true"
validationInterval="34000"
validationQuery="select sysdate from dual"
removeAbandonedTimeout="60"
logAbandoned="true"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementCache;org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"
tim_yates
  • 167,322
  • 27
  • 342
  • 338
zoran119
  • 10,657
  • 12
  • 46
  • 88
  • Some thougts: chek for using sql where sql2 would be needed. Is dataSource in fact a DataSource or a Connection by mistake? Check your logs for closing abandonded while long running job was underway -mwhat do you see? Where wqs that connection taken from the pool? – Jan Jan 05 '16 at 06:09
  • 1
    Does the long action take longer than 60 seconds? If yes, then you either need to **close** the connection before the long action and obtain a new connection after, or you need to increase the `removeAbandonedTimeout` – Mark Rotteveel Jan 05 '16 at 07:41

0 Answers0