Can some one please explain me how to use removeAbandoned property properly? The issue is, we have service method which runs in a transaction (applied @Transactional). The body can be some thing like 1000 select statements followed by 1000 inserts for example. The removeAbandoned property was set to 60 seconds. Now if 1000 insert statements execution time exceeds 60 seconds, it is throwing exception "Connection already closed" for the next db statement. Why is this happening and how to over come this? If i remove the removeAbandoned property every thing is working well. Here is my dbcp config.
db.initialSize=5
db.maxActive=150
db.minIdle=0
db.maxIdle=8
db.defaultAutoCommit=false
db.defaultTransactionIsolation=-1
db.maxWaitMillis=3000
db.timeBetweenEvictionRunMillis=1000
db.minEvictableIdleTimeMillis=1000
db.testOnBorrow=true
db.testOnReturn=false
db.testWhileIdle=false
db.removeAbandoned=true
db.removeAbandonedTimeout=60
db.poolPreparedStatements=true
db.validationQuery=select 1
By using these properties, we are building a data source. Please correct me if i'm doing any thing wrong here. I'm assuming where ever this service is invoked, it opens a connection and executes db statements but it is trying to use the same connection after removeAbandonedTimeout?
This is the normal behavior of removeAbondoned, check here. You need to check your requirements and constraints and configure accordingly. If the execution takes that much time and this is the correct implementation then tune the configuration (increase the abandon timeout or remove it totally).
This might be common for nightly jobs and offline data migration processes for example. If there's a user interaction, I think you need to consider responsiveness, and hence change the implementation of the service. You might then need to separate it into different services or tune the SQL inside or remove some unnecessary calls, but keep configuration of the timeout.