Java, JDBC connection pool, JDBC connection rollback

We have a database connection pool (java JDBC). Every time we checkout, we first do preemptive connection rollback to avoid connection exceptions. Please ign开发者_Go百科ore the business case. Just from technical point of view, does JDBC connection roll back influence application performance a lot (since our application is mili-second business)?

---

I dont understand why connections coming directly from a connection pool need to be rolled back. Their state has to be idle otherwise you wouldn't be able to obtain them from the pool?!

Transactions have an influence to the performance. So if there are transactions than can be rolled back, there may be some query that cannot be executed because the dangling transaction is blocking them.

So if your application is time critical - you should invest some time optimizing your code. Only use transactions where you have to.

---

There is no such thing as a connection rollback. Perhaps you meant a transaction rollback. You should never rollback unless you intend to lose any changes made in the database in the transaction, and you should never commit unless you intend to commit all the changes made in the transaction.

Having said that, I do not understand the term "we first do preemptive connection rollback to avoid connection exceptions" and why you would be doing that in the first place. Assuming that you are referring to the Connection.rollback() method invocation, I would consider that to be a bad practice. It would actually result in a loss of performance, as it requires a network call, and forces the database to discard the state of the associated transaction (which is meaningless in this scenario, as no work has been done yet).

If you intend to avoid exceptions involving dead connection retrieved from a Connection pool, you must configure the pool to check if the connection is not stale (usually by executing a test SQL statement), before returning the connection to the application. Once the application has acquired the connection, it should not keep the connection around after it no longer needs it; the application should ideally return the connection to the pool. If you find yourself keeping the connection object around, the underlying physical connection is unavailable for use by other requests, and it could actually be closed by a firewall that is configured to close connections that have been alive beyond a certain duration.

---

Totally agree with mana.

You "check out" a connection from a pool and then work with it and then give it back to the pool, once you are done.

Please take a look at the JBoss Connection Pooling logic, or just go with a production grade connection pool package.

There is a discussion at Connection pooling options with JDBC: DBCP vs C3P0

---

The appropriate way to validate a connection coming from the pool is to use a validation query. If the rollback is because someone is afraid there may be a "dirty" connection returned to the pool, then you need to use a real resource management framework.