db2 jdbc driver does not release table locks

Question

situation: We have a web service running on tomcat accessing DB2 database on AS400, we are using JTOPEN drivers for JNDI connections handled by tomcat. For handling transactions and access to database we are using Spring.

For each select system takes JDBC connection from JNDI (i.e. from connection pool), does selection, and in the end it closes ResultSet, Statement and releases Connection in that order. That passes fine, shared lock on table dissappears.

When we want to do update the same way as we did with select (exception on ResultSet object, we don't have one in such situation), after releasing Connection to JNDI lock on table stays.

If we put maxIdle=0 for number of connections in JNDI configuration, this problem disappears, but this degrades performances, we have cca 100 users online on that service, we need few connections to be alive in pool.

What do you suggest?

Answer 1

Sounds like as if the auto-commit is by default disabled and that the code isn't calling connection.commit() anywhere. To fix this, either configure the connection pool so that it only returns connections with autoCommit = true, or change the JDBC code that it commits the transaction at end of the try block wherein the SQL action is been taken place.