Hello Jimmy,
It's funny because I just had the same problem using Hibernate,
Postgresql and BTM transaction manager during this week. I had many
"<IDLE in transaction> state" which were locking my DB tables... As you
can see (cf. pgsql-jdbc post below), the current Postgresql JDBC driver
(XA implementation) contain a bug regarding transaction boundaries:
http://archives.postgresql.org/pgsql-jdbc/2007-11/msg00051.php
So maybe these strange transaction boundaries were letting transactions
open (I also forgot to set a transaction timeout)...
If you think it could be this kind of problem (i'm not a JTA expert),
you can try this postgresql-jdbc patched version:
http://archives.postgresql.org/pgsql-jdbc/2007-11/msg00054.php
Or if as you said: "you don't really need to synchronize across multiple
datasources", you can try (if your transaction manager allow this) the
1PC optimization which allow the use of a pure JDBC driver (no
XAResource needs). For BTM transaction manager you will find
informations here:
http://docs.codehaus.org/display/BTM/LastResourceCommit
And you can find great explanation about 1PC optimization here:
http://jroller.com/pyrasun/category/XA
For me, this work around worked perfectly ! I hope its help !
Joël
On Wed, 2007-12-05 at 14:46 -0600, Jimmy Wan wrote:
> Hello, I've been trying to simultaneously debug an issue with
> connections
> being left in the <IDLE in transaction> state. My software stack looks
> something like this:
>
> Custom J2EE Code & Jackrabbit JCR
> Hibernate 3.2.0
> Tranql 1.4.1/Tranql Connectors 1.4
> Geronimo 2.0.2
> PostgreSQL JDBC 8.2-504, 8.2-506, 8.2-507
> (been upgrading to no avail)
> PostgreSQL on the backend (8.2.4 on Windows or 8.2.3 on Linux)
>
> I've also got a unit test environment that looks something like this:
> Custom J2EE Code & Jackrabbit JCR
> Hibernate 3.2.0
> Tranql 1.4.1/Tranql Connectors 1.4
> Embedded OpenEJB 1.0
> PostgreSQL JDBC 8.2-504, 8.2-506, 8.2-507
> (been upgrading to try and fix my problems to no avail)
> PostgreSQL 8.2 on the backend.
>
> I saw the following note to the postgresql-jdbc list:
> http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00060.php
>
> Ludovic, I was wondering if your analysis for PostgreSQL evaluation
> for compatibility with BTM was complete. Also, I was wondering
> whether you could elaborate on the following statement about
> Hibernate and JTA:
>
> In particular, the following passage:
> "I'm not 100% sure about this but the fact that you cannot mix local
> and global transactions will prevent Hibernate applications to work
> when built with JTA. There are many cases where these features that
> look not that important at first are quite critical."
>
> I had been under the assumption (misunderstanding?) that using the
> JTASessionContext for Hibernate would force it to employ XA
> transactions, but when stepping through the Hibernate code, I saw
> that none of my Hibernate operations resulted in the enlistment of
> XA resources.
>
> I'm about to throw in the towel wrt using XA (we don't really need
> to synchronize across multiple datasources).
>
> I still consider myself an XA novice, so any pointers are helpful.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
