Re: jdbc xa support - Mailing list pgsql-jdbc

On Fri, 22 Jul 2005, Michael Allman wrote:

> On Fri, 22 Jul 2005, Heikki Linnakangas wrote:
>
>> On Thu, 21 Jul 2005, Michael Allman wrote:
>>
>>> I have serious doubts that any SQL database in the world supports this
>>> behavior correctly.  If you know of one that does, I'd like to see its
>>> magic.
>>
>> I tested it on some SQL databases, and at least Oracle seems to support it.
>> DB2 fakes it by preparing early. Derby seems to support it, but it only
>> supports XA in embedded mode.
>
> If Oracle supports it, it's likely because they have some server-side stored
> procedures that do something magical.  I don't know.  I'm not an SQL expert,
> but I don't think SQL by itself supports the association of discrete DML
> statements with arbitrary transactions.

SQL spec doesn't say anything about two-phase commit or XA, so no, SQL
itself doesn't support any of that.

Just looked at MySQL/InnoDB, they have these commands to deal with XA:

XA BEGIN <xid> [JOIN | RESUME]
XA START TRANSACTION <xid> [JOIN | RESUME]
XA COMMIT <xid> [ONE PHASE]
XA END <xid> [SUSPEND [FOR MIGRATE]]
XA PREPARE <xid>
XA RECOVER
XA ROLLBACK <xid>

They have all the support in the backend, so their driver
implementation is trivial. (Of course, since it's MySQL, I wouldn't bet
that they actually work the way they should, but anyway :))

> You might want to check out SimpleJTA:
>
> http://www.simplejta.org/
>
> They have some XA driver notes.  Among them the following nugget:
>
> <quote>
> JTA specifications allow an XAResource object to shared amongst multiple
> concurrent transactions with the restriction that the resource can be
> enlisted with a single transaction at a point in time. Resource sharing
> amonst multiple transactions appears to cause a problem in Oracle in a
> multi-threaded environment. Therefore, SimpleJTA is configured to defer the
> reuse of an XAResource object by other transactions until the existing
> transaction is completed, i.e., either committed or rolled back.
> </quote>

I wouldn't be surprised if all the other TMs did the same. We'll have to
test it.

>> I agree that it sucks.
>>
>>> I don't know what to do about this yet.
>>
>> The simplest implementation is one that returns all the recovered xids if
>> flags include TMSTARTRSCAN, and an empty array in all other cases. That
>> way, the internal implementation don't have to be stateful even though the
>> API is.
>
> I posted a new version last night that does this.  I think it works.

It's legal to give TMSTARTRSCAN | TMRENDSCAN as flags. Otherwise,
looks good to me.

>>>> 6. isSameRM considers two connections to the same database as different
>>>> RMs. I'm not sure what the implications of this are, but I feel that's
>>>> not right. I have the same issue in my implementation as well...
>>>
>>> They're different RM's because you can't join a transaction across two
>>> physical JDBC Connections.  Each XAResource instance is associated with
>>> exactly one physical connection instance.
>>
>> I don't think that's the correct definition of an RM. See section 2.2.4 of
>> the XA specification. I think the Postgres database or cluster is one RM.
>> But as I said, I don't know what implications your implementation has. It
>> might work just fine, or not.
>
> It's up to the implementor to define the scope of an "RM" and what isSameRM()
> means --- hence the interface method.
>
> The TM uses this method when it has another XAResource to enlist in the
> transaction and wants to know if it should start another branch for it (with
> start(newBranchXid, TMNOFLAGS)) or can join an existing transaction branch
> (with start(existingBranchXid, TMJOIN)).
>
> The DTP XA spec says a single RM *may* service multiple independent resource
> domains.  There are RM's that work like this, e.g. Berkeley DB where
> transactions are represented as first-class Objects which can be passed
> around within the same environment.  However, PostgreSQL does not support
> this behavior.  Again, you can't join a transaction across physical database
> connections.

What's a resource domain? The way I understand it, a resource domain might
be a Postgres database or cluster.

> One possible alternative we might explore is allowing an XAResource instance,
> say xaRes1, for the same database as another XAResource instance, say xaRes2,
> to adopt the same physical connection instance as xaRes2.  So
> xaRes2.isSameRM(xaRes1) would return true if the underlying physical
> connections pointed to the same PostgreSQL database (with the same user
> credentials).  Then if a TM tried to join xaRes2 to xaRes1's transaction
> branch, we could implement xaRes1.start(existingBranchXid, TMJOIN) to assign
> xaRes1.physicalConnection = xaRes2.physicalConnection. Then they would share
> the same transaction branch and context.  How about that?

That sounds right. We'll need a global map of xids and
physicalConnections.

But let's see how far we can get with the simpler method, that is, just
define isSameRM as "return this == other", and not implement TMJOIN at
all.

- Heikki