Skip to main content

tomcat dbcp abandoned connections

We recently switched from commons dbcp to tomcat dbcp and after 2-3 weeks another architect ran into an issue where his backend job would run into a resultset close issue.  He was saying it started right about the time we made the switch and it happens only for long running jobs.  That reminds me of something I had read about abandoned connections so I asked him to check after how many minutes it does this and it was 20min 1 sec. It seems I had added a property in dbcp

        dataSource.setLogAbandoned(true);
        dataSource.setRemoveAbandoned(true);
        dataSource.setRemoveAbandonedTimeout(1200);

so first thing was to confirm if this was really the case because our transaction timeout for long running queries is also 20 min but that has been for ages so this abandoned was the only recent change. Finally I found logAbandoned would log into tomcat catalina.out and I indeed saw this

Jun 18, 2014 3:54:07 PM org.apache.tomcat.jdbc.pool.ConnectionPool abandon
WARNING: Connection has been abandoned PooledConnection[com.mysql.jdbc.JDBC4Connection@2ba0c302]:java.lang.Exception
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getThreadDump(ConnectionPool.java:1062)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:779)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:615)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:187)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:128)

So now only thing left is to fix it.

There are two approaches:
  1. While iterarting resultset reset the abandoned timer for long running job using ResetAbandonedTimer
  2. Or  for now just use dataSource.setAbandonWhenPercentageFull(50);
I am leaning more towards second one because most long running jobs run on slave and I am sure we don't exceed more than 50% capacity for now. If this creates an issue I would do #1.  This was interesting issue and the only reason I was able to debug in 30min to 1 hour because I had read DBCP documentation before for atleast 2 hours so sometimes passive reading stuff helps when you indirectly :).


Comments

Popular posts from this blog

RabbitMQ java clients for beginners

Here is a sample of a consumer and producer example for RabbitMQ. The steps are
Download ErlangDownload Rabbit MQ ServerDownload Rabbit MQ Java client jarsCompile and run the below two class and you are done.
This sample create a Durable Exchange, Queue and a Message. You will have to start the consumer first before you start the for the first time.

For more information on AMQP, Exchanges, Queues, read this excellent tutorial
http://blogs.digitar.com/jjww/2009/01/rabbits-and-warrens/

+++++++++++++++++RabbitMQProducer.java+++++++++++++++++++++++++++
import com.rabbitmq.client.Connection; import com.rabbitmq.client.Channel; import com.rabbitmq.client.*; public class RabbitMQProducer { public static void main(String []args) throws Exception { ConnectionFactory factory = new ConnectionFactory(); factory.setUsername("guest"); factory.setPassword("guest"); factory.setVirtualHost("/"); factory.setHost("127.0.0.1"); factory.setPort(5672); Conne…

Spring query timeout or transaction timeout

If you are using spring to manage transactions then you can specify default transaction timeout using

    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
        <property name="defaultTimeout" value="30" /> <!--30 sec--->             
    </bean>

or you can override the timeout in the annotation

    @Transactional(readOnly = false, timeout=30)


or if you are doing it programatic transactions then you can do


DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSource);
transactionManager.setDefaultTimeout(30);

 or you can override the timeout for one particular transaction

TransactionTemplate transactionTemplate = new TransactionTemplate();
transactionTemplate.setTimeout(30);

Python adding pid file

I have a thumbnail generator that launches multiple processes and the correct way to shut it down is to send kill -HUP to the parent process. To automate I had to write a pid file from python, it was a piece of cake
def writePidFile(): pid = str(os.getpid()) f = open('thumbnail_rabbit_consumer.pid', 'w') f.write(pid) f.close()