Re: Tomcat 9.0.87 not releasing or re-using the pooled connections.

[Felix Schumacher]

Am 01.08.25 um 06:15 schrieb Parameshwara Bhat:

Hello All,

I am working on a task of migrating a  running a JSP/JSF application on tomcat-6.0 to tomcat 9.0 My installation of tomcat-9.0.87 is on Opensuse-Leap-15.6. Database is postgresql 17, using postgresql-42.7.7.jar for driver. I am able to run legacy application on tomcat-9.0.87 after making following changes ( picked up from tomcat-9.0 docs) to application's connection code.

    try {
            InitialContext cxt = new InitialContext();
            /*if ( cxt == null ) {
            throw new Exception("Uh oh -- no context!");
            }*/
            DataSource ds = (DataSource) cxt.lookup(
"java:/comp/env/jdbc/public_PostgreSQL" );
            /*if ( ds == null ) {
            throws new Exception("Data source not found!");
            }*/
            try {
            this.con = ds.getConnection();
            } catch (SQLException ex) {
            ex.printStackTrace();
            }
          } catch (NamingException e)  {
            e.printStackTrace();
          }

        /*        this.con = DriverManager.getConnection(url,
        username, password); */

It is never a good idea to store the connection in the servlet instance itself. You might (will) overwrite this field, if you are not super careful.

You can store the data source object inside your servlet, but for each request (or whenever you need it), get your own (method scoped) connection and make sure you release it on all paths. Chris has posted a link to an old blog post of his to manage db resources in a sane way: https://blog.christopherschultz.net/2009/03/16/properly-handling-pooled-jdbc-connections/

My context.xml content is below.

    <?xml version="1.0" encoding="UTF-8"?>
    <Context path="/ERP">
      <Resource auth="Container"
    driverClassName="org.postgresql.Driver" maxTotal="40" maxIdle="20"
    maxWaitMillis="-1" name="jdbc/public_PostgreSQL"
    password="kapital" type="javax.sql.DataSource"
    url="jdbc:postgresql://localhost:5444/das" username="das"/>
    </Context>

I am able to run application only after making maxTotal="40" from maxTotal="20" ; maxIdle="20" from maxIdle="10". Corresponding values for tomcat 6 running installation are maxActive="20", maxIdle="10".

With maxTotal="40" and maxIdle="20", I am able to barely run once. If I log out and try to login again, application can never login. In fact, I was able to login even once only after changing to maxTotal="40" and maxIdle="20". Otherwise application would be forever stuck at login, never able to enter application. I am able to login the first time I login and never afterwards. I have to kill tomcat-9.0.87 and restart,then I am able to login again.

You can configure the db pool to log abandoned connections, which will point you to the place, where those lost connections are instantiated (borrowed). The configuration options are explained on the dbcp pool configuration page (https://commons.apache.org/proper/commons-dbcp/configuration.html) and are removeAbandonedOnBorrow, removeAbandonedTimeout, logAbandoned and abandonedUsageTracking.

So I believe an adapted config with those enabled would look like


    maxWaitMillis="1000" name="jdbc/public_PostgreSQL"
    password="kapital" type="javax.sql.DataSource"
    url="jdbc:postgresql://localhost:5444/das" username="das"
   removeAbandonedOnBorrow="true"
   removeAbandonedTimeout="10"
   logAbandoned="true"
   abandonedUsageTracking="true"
/>





These settings are not meant to used in production!

Felix

<Resource auth="Container" driverClassName="org.postgresql.Driver" maxTotal="1" maxIdle="1" The default timeout for connections to be considered as abandoned is 300 seconds. To shorten your wait time, I used 10 seconds in the example. You might want to use an even shorter period while testing. Another tip from Chris is to set the number of connections in the pool to a really small size, so that you get early feedback, when you loose a connection.

Parameshwara Bhat

My guess is, pooled connections to database are not released to be re-used. I will thank any help to understand and resolve this. I have read documentation before making changes to DB connection, but I might have missed nuances.

OpenPGP_0xEA6C3728EA91C4AF.asc
Description: OpenPGP public key

OpenPGP_signature.asc
Description: OpenPGP digital signature