Re: Postgres XA support

Ludovic Orban wrote:

> I rencently found this thread on PotgreSQL's forum:
> http://archives.postgresql.org/pgsql-jdbc/2006-04/msg00150.php
> and I just wanted to let you know that calling Connection.close()
> before or after the commit is the transaction manager's job or more
> precisely the XA connection pool's job.

So you're suggesting that the code example here is incorrect and it must
call commit before close? It really seems like a transaction ought to
correcty wrap the connection open/close which is the fix that I put in.

http://archives.postgresql.org/pgsql-jdbc/2006-04/msg00149.php

> I'd like to know a bit more about Postgres' XA support before testing
> it (to add it to my support list:
> http://www.bitronix.be/Btm/DatabasesXASupportEvaluation#postgres) and
> I'd be happy if you could brielfy tell me how you think it's working.

Honestly, I have no idea. As a maintainer I occasionally have to
write/review code that I don't know much about and XA is one of those.
Asking on the jdbc list (cced) is the best place to get information as
the authors and users of the XA code can attest to its support much
better than I can. I hear it works with Orion and with a small patch
Weblogic. My limited testing showed it worked with Simple-JTA.

Kris Jurka

Ludovic Orban wrote:
> From the comments I saw in the source, transaction interleaving, join
> and suspend/resume are still not supported and forget is still
> unimplemented. This means you cannot mix local and global
> transactions, cannot support EJBs with REQUIRES_NEW CMT declaration
> and can get into troubles during crash recovery.
>
> I think it was Michael Allman that said the engine wasn't able to
> properly support XA in Aug 2005 and unfortunately it seems that things
> haven't changed much since then.
>
> I'm afraid you still have some work to do on the engine before you can
> implement XA in JDBC.

Well there are two perspectives on this. If you need a way of
implementing multi-resource transactions, than the simple two phase
commit approach implemented in postgresql is adequate. If full XA
compliance is required then postgresql comes up far short. Yes, backend
development has stalled on this. Backend developers were not aware of
the full XA requirements and when informed said, "We implemented all
this two phase stuff and now you're telling us it's inadequate!" So
they've moved on and the ability to do things like transaction
interleaving are very complicated given the postgresql backend model, so
I wouldn't hold my breath on things changing anytime soon.

Kris Jurka

Kris Jurka wrote:
> Ludovic Orban wrote:
>
>> I'd like to know a bit more about Postgres' XA support before testing
>> it (to add it to my support list:
>> http://www.bitronix.be/Btm/DatabasesXASupportEvaluation#postgres) and
>> I'd be happy if you could brielfy tell me how you think it's working.

We had off-list discussions about this some time ago, as you probably
remember. I'd be glad to tell you more if you have any further questions.

> Asking on the jdbc list (cced) is the best place to get information as
> the authors and users of the XA code can attest to its support much
> better than I can.

Agreed.

> I hear it works with Orion and with a small patch
> Weblogic. My limited testing showed it worked with Simple-JTA.

It's very important that we have a wide range of XA test cases in test
suite, because we can't test with every possible transaction manager
implementation whenever we change something.

It would be nice to collect sample configurations of application servers
/ transaction managers as people get the driver to work with them.

I personally used JOnAS for testing. We should start to collect a list
of known working configurations.

* JOnAS
* WebLogic (with patch; I hope we can get it applied soon)
* Orion
* Simple JTA
* Geronimo (with patch, see the recent thread about auto commit problems)

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

> Ludovic Orban wrote:
>> From the comments I saw in the source, transaction interleaving, join
>> and suspend/resume are still not supported and forget is still
>> unimplemented. This means you cannot mix local and global
>> transactions, cannot support EJBs with REQUIRES_NEW CMT declaration
>> and can get into troubles during crash recovery.

Can you clarify how that can get you into trouble during crash recovery?

We had a discussion about this earlier, and it seems I never replied to
your latest comment on the EJB REQUIRES_NEW support. Let me do that now:

Ludovic Orban wrote:
> Heikki Linnakangas wrote:
>> Ludovic Orban wrote:
>>> Appart from that, suspend/resume is mandatory to implement CMT
>>> REQUIRES_NEW semantics of the EJB servers.
>>
>> Why? Can't you just open a new connection on a REQUIRES_NEW method
>> call?
>
> REQUIRES_NEW requires to start a brand new transaction for the
> duration of the call. The only way to do this is to sak the TM to
> suspend the current transaction, start the new one and when it's
> finished resume the first one.
>
> Since a transaction is bound to a thread the is no way to do this
> other than suspend/resume.

AFAICS, the transaction manager can simply keep the original connection
associated with the original transaction, and open a new connection
associated with the new transaction when needed. Can you give a specific
example scenario that's impossible to implement without suspend/resume
support?

>> I think it was Michael Allman that said the engine wasn't able to
>> properly support XA in Aug 2005 and unfortunately it seems that things
>> haven't changed much since then.
>>
>> I'm afraid you still have some work to do on the engine before you can
>> implement XA in JDBC.
>
> Well there are two perspectives on this. If you need a way of
> implementing multi-resource transactions, than the simple two phase
> commit approach implemented in postgresql is adequate. If full XA
> compliance is required then postgresql comes up far short. Yes, backend
> development has stalled on this. Backend developers were not aware of
> the full XA requirements and when informed said, "We implemented all
> this two phase stuff and now you're telling us it's inadequate!" So
> they've moved on and the ability to do things like transaction
> interleaving are very complicated given the postgresql backend model, so
> I wouldn't hold my breath on things changing anytime soon.

I don't see much value in implementing the full XA spec. What we have
now is enough to implement distributed transactions reliably, and that's
what XA is all about.

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

Heikki,

> It's very important that we have a wide range of XA test cases in test
> suite, because we can't test with every possible transaction manager
> implementation whenever we change something.
>
> It would be nice to collect sample configurations of application servers
> / transaction managers as people get the driver to work with them.
>
> I personally used JOnAS for testing. We should start to collect a list
> of known working configurations.
>
> * JOnAS
> * WebLogic (with patch; I hope we can get it applied soon)
> * Orion
> * Simple JTA
> * Geronimo (with patch, see the recent thread about auto commit problems)

I agree but what do you call 'working configuration' ?

Due to the current limitations you could get simple cases working
while at the same time excluding some very important ones because
they're unsupported.

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.

Ludovic

Ludovic Orban wrote:
>>
>> I personally used JOnAS for testing. We should start to collect a list
>> of known working configurations.
>>
>> * JOnAS
>> * WebLogic (with patch; I hope we can get it applied soon)
>> * Orion
>> * Simple JTA
>> * Geronimo (with patch, see the recent thread about auto commit problems)
>
> I agree but what do you call 'working configuration' ?

Good question :).

> Due to the current limitations you could get simple cases working
> while at the same time excluding some very important ones because
> they're unsupported.

Agreed. It'd be nice to have a test application, packaged in an EAR for
example, that would exercise all the unusual scenarios including
recovery. It'd be extremely useful for testing JTA implementations as
well. Sun might actually have a test kit like that, but they're probably
proprietary and expensive.

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

Interesting. I don't know Hibernate very well, but I'd assume that it's
just using the UserTransaction et al. APIs, and shouldn't look any
different than other applications to the JDBC driver.

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

> >> From the comments I saw in the source, transaction interleaving, join
> >> and suspend/resume are still not supported and forget is still
> >> unimplemented. This means you cannot mix local and global
> >> transactions, cannot support EJBs with REQUIRES_NEW CMT declaration
> >> and can get into troubles during crash recovery.
>
> Can you clarify how that can get you into trouble during crash recovery?

Simply because XAResource.forget() is not implemented. You won't
exactly be into trouble because of this but extra manual work might be
needed to check that an after-crash recovery succeeded.

You will have the same doubts when reconciliating the TM logs with the
DB logs after a heuristic decision happened.

> We had a discussion about this earlier, and it seems I never replied to
> your latest comment on the EJB REQUIRES_NEW support. Let me do that now:
>
> AFAICS, the transaction manager can simply keep the original connection
> associated with the original transaction, and open a new connection
> associated with the new transaction when needed. Can you give a specific
> example scenario that's impossible to implement without suspend/resume
> support?

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.

> I don't see much value in implementing the full XA spec. What we have
> now is enough to implement distributed transactions reliably, and that's
> what XA is all about.

I disagree. There are some parts of the XA spec that might well be
considered as optional but suspend/resume, ability to mix local/global
transactions and recovery can't be. Yes you can run distributed
transactions reliably but with so many restrictions that it's of no
use.

Please don't get me wrong here. I'm not bashing Postgres nor your work
in any way I'm just saying that it's still lacking features to make it
really usable with XA transactions. Mysql and Ingres fall in exactly
the same category: they provide incomplete (even worse, sometimes
incorrect) XA support as well.

Ludovic

Ludovic Orban wrote:
>> >> From the comments I saw in the source, transaction interleaving, join
>> >> and suspend/resume are still not supported and forget is still
>> >> unimplemented. This means you cannot mix local and global
>> >> transactions, cannot support EJBs with REQUIRES_NEW CMT declaration
>> >> and can get into troubles during crash recovery.
>>
>> Can you clarify how that can get you into trouble during crash recovery?
>
> Simply because XAResource.forget() is not implemented. You won't
> exactly be into trouble because of this but extra manual work might be
> needed to check that an after-crash recovery succeeded.

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

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.

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.

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

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

>> I don't see much value in implementing the full XA spec. What we have
>> now is enough to implement distributed transactions reliably, and that's
>> what XA is all about.
>
> I disagree. There are some parts of the XA spec that might well be
> considered as optional but suspend/resume, ability to mix local/global
> transactions and recovery can't be. Yes you can run distributed
> transactions reliably but with so many restrictions that it's of no
> use.

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?

> Please don't get me wrong here. I'm not bashing Postgres nor your work
> in any way I'm just saying that it's still lacking features to make it
> really usable with XA transactions. Mysql and Ingres fall in exactly
> the same category: they provide incomplete (even worse, sometimes
> incorrect) XA support as well.

I believe there's three reasons most RM implementations don't implement
suspend/resume or transaction interleaving:

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.

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.

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

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

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

Heikki,

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

How would you call it then ? You will never be able to see this 'OSI
DTP standard' unless you're going to pay for the specs so you
shouldn't count on it.

My question stands: why would you make a difference between a manual
intervention or an automated intervention of the engine ? The end
result is the same: the transaction has been terminated ultimately
without the transaction manager's knowledge.

You could just write in the Postgres documentation 'never call
COMMIT/ROLLBACK PREPARED on in-doubts transactions as heuristics are
not supported' but that's just postponing (and documenting which is
nice) the problem. It is not very realistic to have a resource on
production that does not support heuristics.

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

Actually I ran some tests against Weblogic and Atomikos and I must say
that none of them are using suspend/resume facilities of the RMs to
implement transaction suspension. You were entirely right.

Depending on the enlistment implementation (agressive or gentle) the
TM will use end(TMSUCCESS) and start(TMJOIN) or silently won't allow a
connection to hosts two global transactions.

Weblogic uses an agressive policy while Atomikos, Websphere and old
JBossTM (not Arjuna's) are gentle. The practical difference between
the two is that agressive will work fine with tricky use cases like
this one: http://www.jroller.com/page/maximdim?entry=is_xa_transaction_support_broken
Agressive is also much more performant with resources fully supporting
transaction interleaving.

> >> 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 might be possible to workaround in the TM a potential limitation of
the RM on this subject but as far as I can say, this works fine with
Oracle, Sybase, Firebird, Informix, Derby and FirstSQL.

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

That's correct for CMT but I've seen more applications requiring XA
where JTA transactions were controlled manually with BMT or that
weren't using EJBs at all. In those cases, a JTA transaction is only
started when it is known that two resources will be impacted.

Am I biaised by relying on my sole experience ?

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

I believe Oracle supports transaction interleaving in its engine as
OCI has a very complete set of functions to manipulate transactions:
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10779/oci08sca.htm
There are some limitations but it works well for most cases.

FirstSQL, Firebird and Informix fully support transaction interleaving
with no restiction.

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

No idea as I never tested any of these two.

As you were right on the TMSUSPEND discussion, please ignore my
arguments against TM workarounds as they are bogus.

Ludovic

On Tue, 10 Oct 2006, Heikki Linnakangas wrote:

> Ludovic Orban wrote:
>
>> 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...
>

I see how Allan's patch would get UPDATE 1 to commit, but UPDATE 2 and 3
are the equivalent of an interleaved transaction and I don't see how that
could possibly work. I'm not sure what sort of protection we need to add
to prevent people from doing this or other transaction handling actions on
the connection. Right now the Connection object doesn't know if it is
being used by a XADatasource.

Kris Jurka

Kris,

2006/10/30, Kris Jurka <books(at)ejurka(dot)com>:
>
> I see how Allan's patch would get UPDATE 1 to commit, but UPDATE 2 and 3
> are the equivalent of an interleaved transaction and I don't see how that
> could possibly work. I'm not sure what sort of protection we need to add
> to prevent people from doing this or other transaction handling actions on
> the connection. Right now the Connection object doesn't know if it is
> being used by a XADatasource.

The problem is not if a connection was created by an XADatasource or
not but if it is being used in a global transaction context or not.

Weblogic's connection pool has a parameter called
local-transaction-supported which when set to false will disallow
local transactions to be executed on connections taken from that pool.

I guess you could enforce the same behavior in the driver by throwing
a SQLException when some query is executed and XAResource.start() has
not been called yet or XAResource.end() has already been called.

Ludovic

Ludovic Orban wrote:
> Kris,
>
> 2006/10/30, Kris Jurka <books(at)ejurka(dot)com>:
>>
>> I see how Allan's patch would get UPDATE 1 to commit, but UPDATE 2 and 3
>> are the equivalent of an interleaved transaction and I don't see how that
>> could possibly work. I'm not sure what sort of protection we need to add
>> to prevent people from doing this or other transaction handling
>> actions on
>> the connection. Right now the Connection object doesn't know if it is
>> being used by a XADatasource.
>
> The problem is not if a connection was created by an XADatasource or
> not but if it is being used in a global transaction context or not.

Agreed.

> Weblogic's connection pool has a parameter called
> local-transaction-supported which when set to false will disallow
> local transactions to be executed on connections taken from that pool.

The WebLogic manual isn't very helpful, this is all I found regarding
that parameter:

"local-transaction-supported — Optional. Boolean. Set the
local-transaction-supported to true if the XA driver supports SQL with
no global transaction; otherwise, set it to false. The default value is
false."

It doesn't actually say what difference it makes or why you would want
to set it to true. I don't see how a transaction manager could generally
make use of the feature, because it doesn't know if a transaction is
distributed or not until it's committed or the second resource is enlisted.

> I guess you could enforce the same behavior in the driver by throwing
> a SQLException when some query is executed and XAResource.start() has
> not been called yet or XAResource.end() has already been called.

That seems like the easiest solution. We'll have to wrap the Connection
object returned by PGXAConnection.getConnection so we can intercept
execute-calls, though.

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

Heikki,

> > Weblogic's connection pool has a parameter called
> > local-transaction-supported which when set to false will disallow
> > local transactions to be executed on connections taken from that pool.
>
> The WebLogic manual isn't very helpful, this is all I found regarding
> that parameter:
>
> "local-transaction-supported — Optional. Boolean. Set the
> local-transaction-supported to true if the XA driver supports SQL with
> no global transaction; otherwise, set it to false. The default value is
> false."
>
> It doesn't actually say what difference it makes or why you would want
> to set it to true. I don't see how a transaction manager could generally
> make use of the feature, because it doesn't know if a transaction is
> distributed or not until it's committed or the second resource is enlisted.

It's not the transaction manager but the connection pool (this is a
parameter of the connection pool, not of the TM !) that is going to
use this feature. If it detects you're using a connection outside a
tm.begin() / tm.commit() block it will throw a SQLException.

To achieve this you need tight integration between the connection pool
and the TM but that's definitely possible. I think I should implement
that as well.

> > I guess you could enforce the same behavior in the driver by throwing
> > a SQLException when some query is executed and XAResource.start() has
> > not been called yet or XAResource.end() has already been called.
>
> That seems like the easiest solution. We'll have to wrap the Connection
> object returned by PGXAConnection.getConnection so we can intercept
> execute-calls, though.

That sounds good enough.

Cheers,
Ludovic