XA transactions and autocommit

Hi there,

I've been playing around with JTA using Apache Geronimo and the
postgresql-8.1-407.jdbc3.jar driver. I've noticed a minor problem.
The driver's XA code disables autocommit and assumes it will remain
so indefinitely. tranql-connection (used by Geronimo for pooling)
enables autocommit whenever the connection is returned to the pool.
(Specifically in its ManagedXAConnection.cleanup method.) According
to the J2EE Connector spec, this is apparently the correct thing to do.

I didn't notice the problem until I tried rolling back a transaction,
where I discovered it was being committed instead. Eventually I
traced it to the fact that autocommit was on.

Arguably, if you follow what the connector spec has to say about
autocommit and XA transactions, autocommit should be on by default
and should only be explicitly disabled during the duration of a
transaction.

I've made a minor patch against 407 that does just this.
XAResource.start disables autocommit, and autocommit is then re-
enabled at the appropriate place within the prepare, commit, and
rollback methods.

When searching the mailing list, I found this, which might be related:

http://archives.postgresql.org/pgsql-jdbc/2006-05/msg00104.php

I don't think there was any resolution.

The patch follows (though my mailer seems to be munging the empty
lines) and can also be found here:

http://www.saddi.com/software/tranql-postgresql/postgresql-
PGXAConnection-autocommit.diff

Regards,
- Allan

--- org/postgresql/test/xa/XADataSourceTest.java.orig 2006-04-26
11:59:31.000000000 -0700
+++ org/postgresql/test/xa/XADataSourceTest.java 2006-10-07
08:35:08.000000000 -0700
@@ -217,6 +217,37 @@
xaRes.rollback(xid);
}

+ public void testAutoCommit() throws Exception {
+ Xid xid = new CustomXid(6);
+
+ assertTrue(conn.getAutoCommit());
+
+ xaRes.start(xid, XAResource.TMNOFLAGS);
+ assertFalse(conn.getAutoCommit());
+ xaRes.end(xid, XAResource.TMSUCCESS);
+ assertFalse(conn.getAutoCommit());
+ xaRes.commit(xid, true);
+ assertTrue(conn.getAutoCommit());
+
+ xaRes.start(xid, XAResource.TMNOFLAGS);
+ xaRes.end(xid, XAResource.TMSUCCESS);
+ xaRes.prepare(xid);
+ assertTrue(conn.getAutoCommit());
+ xaRes.commit(xid, false);
+ assertTrue(conn.getAutoCommit());
+
+ xaRes.start(xid, XAResource.TMNOFLAGS);
+ xaRes.end(xid, XAResource.TMSUCCESS);
+ xaRes.rollback(xid);
+ assertTrue(conn.getAutoCommit());
+
+ xaRes.start(xid, XAResource.TMNOFLAGS);
+ xaRes.end(xid, XAResource.TMSUCCESS);
+ xaRes.prepare(xid);
+ xaRes.rollback(xid);
+ assertTrue(conn.getAutoCommit());
+ }
+
/* We don't support transaction interleaving.
public void testInterleaving1() throws Exception {
Xid xid1 = new CustomXid(1);
--- org/postgresql/xa/PGXAConnection.java.orig 2006-04-26
11:59:31.000000000 -0700
+++ org/postgresql/xa/PGXAConnection.java 2006-10-07
08:32:01.000000000 -0700
@@ -48,9 +48,8 @@

PGXAConnection(BaseConnection conn) throws SQLException
{
- super(conn, false, true);
+ super(conn, true, true);
this.conn = conn;
- this.conn.setAutoCommit(false);
this.state = STATE_IDLE;
}

@@ -104,6 +103,15 @@
if (state == STATE_ENDED)
throw new PGXAException(GT.tr("Transaction interleaving
not implemented"), XAException.XAER_RMERR);

+ try
+ {
+ conn.setAutoCommit(false);
+ }
+ catch (SQLException ex)
+ {
+ throw new PGXAException(GT.tr("Error disabling
autocommit"), ex, XAException.XAER_RMERR);
+ }
+
// Preconditions are met, Associate connection with the
transaction
state = STATE_ACTIVE;
currentXid = xid;
@@ -190,6 +198,7 @@
{
stmt.close();
}
+ conn.setAutoCommit(true);

return XA_OK;
}
@@ -278,27 +287,21 @@
state = STATE_IDLE;
currentXid = null;
conn.rollback();
+ conn.setAutoCommit(true);
}
else
{
String s = RecoveredXid.xidToString(xid);

conn.setAutoCommit(true);
+ Statement stmt = conn.createStatement();
try
{
- Statement stmt = conn.createStatement();
- try
- {
- stmt.executeUpdate("ROLLBACK PREPARED '" + s
+ "'");
- }
- finally
- {
- stmt.close();
- }
+ stmt.executeUpdate("ROLLBACK PREPARED '" + s +
"'");
}
finally
{
- conn.setAutoCommit(false);
+ stmt.close();
}
}
}
@@ -350,6 +353,7 @@
currentXid = null;

conn.commit();
+ conn.setAutoCommit(true);
}
catch (SQLException ex)
{
@@ -378,21 +382,14 @@
String s = RecoveredXid.xidToString(xid);

conn.setAutoCommit(true);
+ Statement stmt = conn.createStatement();
try
{
- Statement stmt = conn.createStatement();
- try
- {
- stmt.executeUpdate("COMMIT PREPARED '" + s + "'");
- }
- finally
- {
- stmt.close();
- }
+ stmt.executeUpdate("COMMIT PREPARED '" + s + "'");
}
finally
{
- conn.setAutoCommit(false);
+ stmt.close();
}
}
catch (SQLException ex)

Allan Saddi wrote:
>
> I've been playing around with JTA using Apache Geronimo and the
> postgresql-8.1-407.jdbc3.jar driver. I've noticed a minor problem. The
> driver's XA code disables autocommit and assumes it will remain so
> indefinitely. tranql-connection (used by Geronimo for pooling) enables
> autocommit whenever the connection is returned to the pool.
> (Specifically in its ManagedXAConnection.cleanup method.) According to
> the J2EE Connector spec, this is apparently the correct thing to do.

Specifically, the spec says that:

A resource adapter must manage the auto-commit mode as follows:
* A transactional resource adapter, either at XATransaction or
LocalTransaction level, must set the auto-commit mode to false within

transaction, either local or XA, on a connection participating in the
transaction.
This requirement holds for both container-managed and bean-managed
transaction demarcation.
* A transactional resource adapter must set the auto-commit mode to
true, on connections that are used outside a transaction.

What's the resource adapter in this scenario? Is it the PostgreSQL JDBC
driver or TranQL? If it's TranQL, then it's not properly calling
setAutoCommit(false) when the connection is used in a global
transaction. If it's the JDBC driver, then we're not doing our job
properly, but then TranQL shouldn't be messing with setAutoCommit in the
first place.

> I didn't notice the problem until I tried rolling back a transaction,
> where I discovered it was being committed instead. Eventually I traced
> it to the fact that autocommit was on.
>
> Arguably, if you follow what the connector spec has to say about
> autocommit and XA transactions, autocommit should be on by default and
> should only be explicitly disabled during the duration of a transaction.

Yeah, I agree that's what we should do. Even if the spec isn't 100%
clear on this, it looks like the safest thing to do. Furthermore, we
should probably throw an exception if you try to set auto commit to true
within a global transaction.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Allan Saddi wrote:
>
> I've been playing around with JTA using Apache Geronimo and the
> postgresql-8.1-407.jdbc3.jar driver.

I just downloaded geronimo to play with. Could you send me the
configuration you're using, to save me a few hours of fiddling with XML
files?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Sat, 7 Oct 2006, Allan Saddi wrote:

> I've been playing around with JTA using Apache Geronimo and the
> postgresql-8.1-407.jdbc3.jar driver. I've noticed a minor problem. The
> driver's XA code disables autocommit and assumes it will remain so
> indefinitely. tranql-connection (used by Geronimo for pooling) enables
> autocommit whenever the connection is returned to the pool. (Specifically in
> its ManagedXAConnection.cleanup method.) According to the J2EE Connector
> spec, this is apparently the correct thing to do.
>
> I've made a minor patch against 407 that does just this. XAResource.start
> disables autocommit, and autocommit is then re-enabled at the appropriate
> place within the prepare, commit, and rollback methods.
>

Patch applied, thanks.

Kris Jurka