Re: Postgres XA support - Mailing list pgsql-jdbc

Ludovic Orban wrote:

>>  > You will have the same doubts when reconciliating the TM logs with the
>>  > DB logs after a heuristic decision happened.
>>
>> Heuristic decisions don't happen, because PostgreSQL doesn't do them.
>> (per my interpretation of the spec)
>
> Once again: manually logging into the DB after the TM crashed and
> manually calling COMMIT/ROLLBACK PREPARED is a heuristic IMU.

Well, I haven't found anything in the XA spec or by googling that
explicitly says whether manually finishing a transaction counts as a
heuristic decision or not. The XA spec refers to the OSI DTP standard.
Perhaps that would shed some light to the issue, however I don't have
access to it. Without an authoritative source, there's no point in arguing.

>> To turn the question around: Do you know of a container/app.server that
>> *does* require suspend/resume support from the XADataSource
>> implementation to support REQUIRES_NEW?
>
> OpenEJB/Geronimo:
>
http://svn.apache.org/viewvc/incubator/openejb/trunk/openejb3/container/openejb-core/src/main/java/org/apache/openejb/transaction/TxRequiresNew.java?view=markup

>
>
> I'm pretty sure that Weblogic, Orion and JBoss work the same as well
> but that was a long time ago so I'd need to double-check.
>
> Hibernate also uses suspend/resume for some of its PK generation stategies:
> http://www.hibernate.org/hib_docs/v3/api/org/hibernate/id/MultipleHiLoPerTableGenerator.html
>
> http://www.hibernate.org/hib_docs/v3/api/org/hibernate/id/TableGenerator.html

You seem to be confusing the suspend/resume facility of the Transaction
Manager, and suspend/resume on a resource adapter. All of the above
examples are calls to the TM's suspend method. A call to
TransactionManager.suspend doesn't imply a call to XAResource.suspend.

If you look at the implementation of that in Geronimo, for example:


http://svn.apache.org/viewvc/geronimo/server/trunk/modules/geronimo-transaction/src/main/java/org/apache/geronimo/transaction/manager/TransactionManagerImpl.java?view=markup

you will see that it simply removes the current transaction from a
HashMap that keeps track of which transactions are associated with which
threads. AFAICS, it does *not* call XAResource.suspend on the enlisted
resources.

I just downloaded Geronimo, and modified the Bank sample application to
call a REQUIRES_NEW bean method within another transaction. It seems to
work just fine, it opens a new connection and runs the REQUIRES_NEW
transaction with that. I can send you the code and/or logs if you want
to try it yourself.

>> What do you mean by mixing local/global transactions?
>
> This is a common way of using a XA connection pool that mixes both
> local and global transactions:
>
> c = ds.getConnection()
> c.executeUpdate("UPDATE 1")
> c.setAutoCommit(false)
> c.executeUpdate("UPDATE 2")
> tm.begin()
> c.executeUpdate("UPDATE 3")
> tm.rollback()
> c.commit()
>
> UPDATE 1 is done in a local transaction while in autocommit mode, it
> is committed.
> UPDATE 2 is done in a local transaction manually committed, it is
> committed.
> UPDATE 3 is done in a global transaction which is rolled back, it is
> rolled back.
>
> Does PostgreSQL supports this ? If not, applications using Hibernate
> might not work as it runs queries in local transaction mode on its own
> during startup.

Hmm, we should be supporting that, but it might not work with the
current released version given the bug with setAutoCommit reported by
Allan Saddi recently. We should add a test case for that...

> It's also common even with applications not using Hibernate to execute
> statements in local and then in global mode with the same connection.

Really? In my experience, you don't write applications that directly
interact with the TM. You leave that to the application server, and app
servers just always start a XA transaction because it doesn't know
beforehand if a transaction is going to be global or local.

>> I believe there's three reasons most RM implementations don't implement
>> suspend/resume or transaction interleaving:
>
> I only know two implementations not supporting these features:
> Postgres and Mysql. Oracle and Sybase have some limitations in the way
> transaction interleaving can be used but it more or less works as it
> should nevertheless.

I believe the limitations in Oracle and Sybase are the same as in
PostgreSQL: you can't do transaction interleaving. Or do they support
some limited kind of transaction interleaving that we don't? If they do,
  we could see what it would take for us to support those scenarios as well.

DB2 had problems when I tested it, search the archives for details. I
wonder how MS SQL Server works?

>> 1. They don't let you to do anything you can't do without
>> 2. It would be a very invasive change to add them to the underlaying
>> architecture and wire protocol of an existing DBMS
>> 3. Because of 1, and the lack of support in other DBMS's, app servers
>> don't use them, and provide ways to work around them, making the
>> features optional, de facto.
>
> For 1, not having them seriously lowers the flexibility given to the
> application programmer.

By "Application programmer" you're meaning the developer of the
transaction manager, right? People developing normal applications that
just utilize global transactions don't need to care about all this.

> For 2, if XA was that easy to implement it would be much more widely
> implemented, don't you think ? How many open source DB supports XA ?
> Close to zero.

Sure, if it was easy, we might as well do it to tick the "fully XA
compatible" box. What's your point?

> For 3, I don't know as all databases that I've seen used with XA
> transaction properly support those features.
> I've also never seen a transaction manager implementing suspend/resume
> by not using the XA facilities. Do you know one that does ?

See Geronimo example above. I haven't looked at any other TMs.

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