21

Yesterday I came across HikariCP and spent the whole night studying it. I'm really impressed with the amount of detail and effort put into fine tuning its implementation and design. Straight to the point, I could not determine how it actually deals with connections that are checked back into the pool with their autoCommit set to false, while neither commit() nor rollback() is issued on them, for example, due to an exception. This potentially can be the source of many serious transactional problems for the next requester that expects a fresh connection but unfortunately receives this connection with its dangling transaction state.

While C3P0 and Tomcat's JDBC pool have some of these so called Knobs for this very purpose (through configuration or interception), I could not find anything in HikariCP's documentation or support group. Please correct me if I'm wrong, but writing a simple unit test showed me that the pool does nothing about this.

I need to know if this observation is actually correct and I'm not missing anything about it. Also, if there is any plan for addressing this in HikariCP since it is critical for me.

Thanks.

kryger
  • 12,906
  • 8
  • 44
  • 65
Hamid Nazari
  • 506
  • 1
  • 6
  • 18

1 Answers1

32

I am one of the authors of HikariCP. HikariCP does not automatically execute either commit or rollback if auto commit is turned off. It is generally expected that an application that is turning off auto commit explicitly is prepared to properly handle these (recommended in a finally block) -- as in this example from the official JDBC documentation.

We are willing to add automatic "rollback" behavior to HikariCP (but not automatic "commit") if a connection is returned to the pool with auto commit set to false. Please open a feature request if you wish this behavior.

UPDATE: HikariCP 1.2.2 and above perform an automatic "rollback" for closed connections with auto-commit set to 'false'. Additionally, it will reset transaction isolation level to the configured default, and as noted in comments below will of course close open Statements, etc.

UPDATE: HikariCP 2.3.x and above now additionally track transaction state when auto-commit is set to false, and will bypass the automatic rollback operation if the transaction state is clean.

brettw
  • 10,664
  • 2
  • 42
  • 59
  • 1
    Yes an automatic rollback would be nice, since it makes the most sense in such context. I agree with you, the application should not have left the connection in such state in the first place, but since I'm dealing with a highly modular framework in my project I'm not in full control over my programmers and this could slip easily this way or the other. I'll open a feature request for this. Thanks for prompt response. – Hamid Nazari Dec 25 '13 at 21:13
  • A connection pool on close of the logical connection should 1) commit or rollback the connection and 2) close any dependent objects (statements, result sets etc). A (logical) connection handed out by a connection pool should for all intents and purposes behave like a normal (non-pooled) connection in all respects (except of course that the underlying physical connection is not closed). – Mark Rotteveel Dec 27 '13 at 10:01
  • @HamidNazari I disagree, although an application should of course be 'well behaved', the user of a connection retrieved from a connection pool should be able to use it just like it would a normal connection (JDBC 4.1, section 11.1 : "_Connection pooling is completely transparent to the client: A client obtains a pooled connection and uses it just the same way it obtains and uses a non pooled connection._") – Mark Rotteveel Dec 27 '13 at 10:04
  • @MarkRotteveel I see your point here, but I think when it comes to dealing with projects in excess of 1M lines of active code, being stringent becomes a necessity. In such domains the subjective concept of 'well behaved' coding starts to gain more and more importance. That's why I think the client should not have violated one basic rule of thumb, i.e., releasing the valuable resource they have obtained in a meaningful manner. – Hamid Nazari Dec 27 '13 at 18:10
  • @HamidNazari My point is: even if one 'client' of the connection pool misbehaved, other clients of that connection pool should not suffer the consequences of that. A connection pool that does not follow the rules established by the JDBC standard (a connection obtained from a pool should behave just like a normal connection), like HikariCP apparently does, makes for weird and hard to track bugs, – Mark Rotteveel Dec 27 '13 at 20:33
  • Sure, this is a common issue ammong many CPs, for example in Tomcat's JDBC pool, if you do not set `defaultAutoCommit` to `false` (which is apparently not a desired **default** behavior) setting `rollBackOnReturn` or `commitOnReturn` will be ignored, and the same problem can happen there, too. In this case I had to write a custom interceptor to get around it and to have a behavior similar to C3P0. – Hamid Nazari Dec 28 '13 at 11:16
  • If my tables are read-only and I don't have to worry about rollbacks, will autocommit be hindering my performance? – user2827214 Jun 04 '16 at 20:04