[Dev] c3p0 and JTA

[Brett Wooldridge]

By chance I discovered that the c3p0 connection pool is not really a XA (Transaction Aware) connection pool.  However, because we are using Hibernate in combination with JOTM (JTA), they have been covering for this fact.  JOTM is "emulating" XA behavior because c3p0 is only capable of handing out standard JDBC connections, not XADataSources.  In practical terms this has worked perfectly well.

However, when I was implementing the persistence of BIRT's intermediate report output I wanted to stream the binary directly into the database (Blob).  Because Hibernate does not support streaming of Blobs (that I could find), I just grabbed a connection from the c3p0 pool and used standard SQL to perform the insert.  Despite the fact that I was beginning a transaction, I noticed that the row was appearing in the database before I had performed the commit of the transaction.  After further investigation, I discovered that connections obtained directly from c3p0 have auto-commit set to 'true' (hence the reason the row appeared immediately after my insert).  This is also an indication that the connections are not XA aware.

After some internet research it seems that nobody is really using c3p0 in a JTA deployment.  In order for "raw" connections from c3p0 to be managed by XA (as Hibernate is doing for us) we would need to implement our own XA synchronization hooks for the connections.  I'm not interested in doing this.  It's silly.

One solution is to get an XA-aware connection pool (Apache DBCP comes to mind).  However, it turns out that some people have experienced "issues" with using JOTM with real XADataSources - which kind of sucks given that that's the scenario it's supposed to work with (http://coffeedrivenjava.blogspot.com/2006/08/distributed-xa-transactions-w-hibernate.html).

So, I am currently investigating the Bitronix TM (another open source JTA) which comes with it's own XADataSource connection pool implementation - we'd kill two birds with one stone.  I don't know if this will make it into this release or not (it's almost working right now), but it shouldn't matter much right now because the behavior is "correct" for Hibernate and I made a fix (added a ConnectionCustomizer) for c3p0 so that "raw" connections come out of the pool with auto-commit off.  However, this means that anyone doing "raw" SQL (I think only me right now) will need to commit() on the Connection before closing it.  Using the TransactionElf will do you no good with raw connections right now.  If you were already doing so, it only had the appearance of working because of the auto-commit.  Anyway, just a heads up.

Funny thing, when I searched google for '+c3p0 +"Transaction Manager" -spring' one of my post to this list came up 3rd.  Funny because back then (July) I was posting about the JOTM integration and said:

> ... Hibernate looks for a JTA Transaction Manager bound under this name.
> It is therefore possible to replace the JOTM OSGi bundle with another bundle
> providing a different JTA Transaction Manager, for example the JBoss JTA,
> Bitronix JTA, or Atomikos JTA with absolutely no code change to any other
> bundle.

Good thing, that.

-Brett
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ziptie.org/pipermail/dev/attachments/20080316/2d772d08/attachment.html