Re: Postgres XA support

Heikki,

> Right, this comes down to the definition of heuristic commit/abort. The
> way I read the specs, a heuristic commit/rollback means that the
> resource adapter has independently decided to commit or rollback a
> transaction. A typical implementation would be to roll back prepared
> transactions after a timeout if the connection to the TM is lost.
>
> The way you read the specs is that when the administrator manually
> commits or aborts a transaction, that's also a heuristic commit/abort.

The way I understand heuristics is as follows:
any decision not taken by the transaction manager is a heuristic
decision. I can be a timeout or an administrator manually terminating
the transaction or whatever else.

> The JTA spec doesn't go into details, but the XA spec (Section 2.3.3)
> says: "Some RMs may employ heuristic decision-making: an RM that has
> prepared to commit a transaction branch may decide to commit or roll
> back its work independently of the TM."
>
> Section 7.3 explicitly lists "Heuristics" as an optional feature.

Actually it is section 7.2 but you're right, it seems that resources
are not mandated to support heuristic decistions.

> I don't think the XA spec supports the view that manual commit or
> rollback is considered a heuristic decision. The administrator better
> not mess with in-doubt transactions. If she does, the safe way to do it
> is to first take the TM and all resource managers offline, manually
> finish all the transactions in the TM, and then manually finish all the
> transactions in the RMs.

I think this is where you are wrong as per my understanding of
heuristics. Why would there be a difference between a heuristic and a
manual intervention ?

A heuristic decision should be taken when it's been decided that
global consistency is less important that resource availability. It is
a process that should only be used at last resort, I agree but it can
make difference between life and death, when you prefer running with a
degraded service than with no service at all.

In the end, the TM will have to reconcile its log with the resource's
state and report potential inconsistencies due to decisions not taken
by itself. Why wouldn't it report an inconsistency created by a human
intervention ?

> BTW: If we wanted to report manual commits/aborts as heuristic
> decisions, that would be possible with some minor changes to the
> backend. We would need to add an option to the COMMIT/ROLLBACK PREPARED
> commands saying it's a heuristic decision, and have those commands
> insert a row to a table. forget would then delete the row. But I don't
> think we should do that.

That's one way of doing it but you can refine it a bit.

> > 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.

> > Opening a new connection as you describe would probably work. It is
> > then the responsibility of the EJB container to implement REQUIRES_NEW
> > in this way instead of using the TM's suspend service but I don't know
> > of any container that implements REQUIRES_NEW without calling suspend
> > on the TM.
>
> Oh, the container can call TM's suspend service. But that doesn't mean
> that the TM has to call suspend on the resource managers. In fact, if
> you have a non-distributed transaction, with a non-XA enabled JDBC
> driver, there isn't a suspend-method to call.
>
> 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

> Do you have a use case in mind that's not currently possible? I'd like
> to hear about it so we can try to make it work.
>
> Recovery definitely isn't optional, and it's required for reliable
> operation. Suspend/resume is a required part of the spec, but it isn't
> required for reliable operation and it's not required in practice
> because app. servers can work around it.
>
> 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.

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

> 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.

> 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.
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.
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 ?

> An incorrect implementation is indeed much worse than an incomplete
> implementation. Some DBMS vendors claim XA support even with incomplete
> or broken implementations. We've tried to be very up-front of the
> limitations of the PostgreSQL driver.
>
> No offense taken :). It's important to discuss these things if there's
> any doubts.

Agreed.

Ludovic