Tomcat DBCP: Problem with first update statement after Tomcat restart

[Remigiusz Andrzejak]

Hi gurus,

Every time I restart Tomcat, the first transaction trying to modify data gets stuck in "in transaction" state.

I'm running the following configuration on Gentoo:

Postgresql 8.4.4
Apache Tomcat/6.0.18
JVM: 1.6.0_13-b03
commons-dbcp-1.4
commons-pool-1.5.5

This is how I configured my connection pool:


<Context>

<Resource name="jdbc/weddingDataSource"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://127.0.0.1:5432/myApplication"
username="user" password="password"

maxActive="-1"
maxIdle="20"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="6"
logAbandoned="true"
initialSize="20"
validationQuery="select 1"/>

</Context>


It's enough to close this faulty browser session or to kill the stuck process and from then on things seem to work fine.

Any help would be much appreciated.

Kind regards,
Remik

[Tim Holloway]

I'm afraid that doesn't mean anything to me, although it sounds like a bug in the webapp. Whatever "stuck in transaction state" means.

Technically, it isn't "Tomcat DBCP", it's "Apache DBCP". Tomcat supports a pluggable connection pool architecture. It's just that when you download a copy of Tomcat, it includes the Apache DBCP component as part of the basic package. You then "plug it in" by referring to its primary class in your connection pool configuration. DBCP can be used as a general-purpose pooler, BTW. I've used it in stand-alone applications.

However, neither DBCP nor Tomcat+DBCP have any known bugs/quirks in "first update statements" and they get a lot of use by a lot of users everyday, so I'd take a closer look at the application code.

[Remigiusz Andrzejak]

Hi Tom,

Thanks for your initial feedback. I do plan to do some more checks on the webapp as such, although it seems strange that the problem happens exactly once per Tomcat life cycle and only with the transaction trying to modify data.

Just to clarify what I mean saying "stuck in transaction", here is what happens.

1. I start up Tomcat (making sure that the start is clean including loading DBCP as per my configuration)
2. I open web browser and navigate to my webapp
3. Already at this point webapp accesses db, since it needs some data to present to end-user
4. I navigate in my webapp to the site that triggers some data update.
5. This is when the webapp gets stuck in web browser displaying "Contacting..."
6. If I do ps -ef | grep post (to see Postgres processes on the server side) I can see exactly one of them showing the status: in transaction
7. Nohting else will happen until I kill this process or close the browser

After completing this excercis my webapps works just fine. All db access both read and write works correct - no more web site freezing nor postgres process in permanent "in transaction" state.

Any guess?

Kind regards,
Remik

[Tim Holloway]

That actually sounds more like a long-running transaction in PostgreSQL. Unless you got results, got hung in the webapp and didn't release the resultset (if any) and statement. In which case the connection would still be open even though technically it would be finished.

Your connection pool definition looks funny. I'm not sure why you're referencing a factory, and short of RTFM, I'm not even sure you should be referencing that class.

Here's a working connection pool definition. I have PostgreSQL ones too, this one just happens to be DB2. Only the driver class s different.

    <Resource name="jdbc/myDB" auth="Container"
        type="javax.sql.DataSource" maxActive="100" maxIdle="30"
        maxWait="10000" username="webuser" password="secret"
        driverClassName="com.ibm.db2.jcc.DB2Driver"
        url="jdbc:db2://db2.mousetech.com:50001/testdb"
    />

[Remigiusz Andrzejak]

Hi Tim,

You were actually right suggesting the problem is in my code.

It was incorrect concurrent threads synchronization - nothing to do with DBCP.

Thanks and regards,
Remik