Re: Problem with committing in XA mode - Mailing list pgsql-jdbc

Vlastimil Havranek wrote:
> yes the log_min_messages is set to notice. I've just rechecked it.

Ok, thanks.

One theory is that WebLogic, or your application code, is calling
setAutoCommit on the XA connection. I can reproduce a similar error with
a stand-alone test case by doing that:

         XAConnection xaconn1 = ds.getXAConnection();
         XAResource xares1 = xaconn1.getXAResource();
         Connection conn1 = xaconn1.getConnection();

         xares1.start(initxid, XAResource.TMNOFLAGS);
         Statement stmt = conn1.createStatement();
         stmt.executeUpdate("INSERT INTO foo VALUES (1234)");
         // 1. conn1.setAutoCommit(true);
         xares1.end(initxid, XAResource.TMSUCCESS);
         // 2. conn1.setAutoCommit(true);
         xares1.prepare(initxid);
         xares1.commit(initxid, false);

Uncommenting either of the commented lines will cause a similar error as
you're seeing. Since the end() call is made by WebLogic, the 1st case
would be an explicit setAutoCommit call in your application. You don't
have one, do you? That's forbidden by the JTA spec.

The 2nd call would be a call made by WebLogic. We've seen WebLogic do
strange call patterns before (see
http://archives.postgresql.org/pgsql-jdbc/2006-10/msg00011.php), so I
wouldn't be surprised if it was doing that. It's allowed by the JTA
spec, but not by the PostgreSQL driver because we don't support
transaction interleaving. We could try modifying the driver to tolerate
it like the aforementioned "end then join" case, by not passing the
setAutoCommit call immediately to the underlying connection object, but
delay it until prepare-call. Whether that would work depends on what
else the application server is trying to do with the connection between
end and prepare.

Calling commit or rollback at those points will also trigger the same
error. JTA spec also forbids setSavePoint, but I'm not sure if that
would actually be a problem for PostgreSQL.

There's some options in WebLogic to dumb-down the interaction with the
JDBC XA driver, see
http://edocs.bea.com/wls/docs100/ConsoleHelp/pagehelp/JDBCjdbcdatasourcesjdbcdatasourceconfigtransactiontitle.html.
At least "Keep Connection Open On Release" is required for PostgreSQL
driver, and setting the other settings marked with "Use this setting to
work around specific problems with JDBC XA drivers" might help too.
Please make sure you have those set.


Now, it would be very good if we threw an error immediately at the
offending call, regardless of whether it's allowed by JTA or not. I've
been trying to figure out how to do that. We could create a proxy for
the Connection object handed to the application in PGXAConnection, and
throw an error if you call one of those functions while an
XA-transaction is open. That would be fairly simple, but wouldn't catch
the pattern:

start(xid)
// do stuff in XA transaction
executeUpdate("UPDATE ...");
end(xid)
// do stuff outside the XA transaction
executeUpdate("UPDATE ...");
prepare(xid);

It would be better than nothing, but it would be even better if we could
catch all the cases we can't handle properly. If we could inject a proxy
to check that at lower level, around QueryExecutor, it would be more
robust, but I don't see any easy way to do that.


Another check that we really ought to do is to check the command tag
that PREPARE TRANSACTION returns. If it returns ROLLBACK, the driver
should throw an error. I'll write a patch for that.

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