Re: jdbc xa support - Mailing list pgsql-jdbc

On Sat, 23 Jul 2005, Heikki Linnakangas wrote:

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

What version of MySQL is this?  I cannot find documentation for these
commands.

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

Oliver pointed out the same thing.  I made a correction and will post it
later on.

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

Maybe I made that up.  I don't know.  I'm 99% sure the current
implementation satisfies the spec.

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

It's going to be more complicated/impossible than I first thought.  We
would need to do the switcharoo on the client's connection handle, too.
Otherwise, they'd be sending updates on one physical connection and
committing the transaction on the other physical connection.

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

I'm still not sure we should change the implementation of isSameRM().
It's a little more defensive right now.  To do "return this == other"
would make a (currently valid) assumption about how PGXAResource is used.

Likewise, I think the implementatin of start() with the TMJOIN flag is
correct, though with the current implementation it should never be called.

On a related note, can we get the backend to indicate if a transaction was
read-only when the "PREPARE TRANSACTION" statement is called?  (Also, I
believe preparing a read-only transaction should automatically "commit"
it.)  We could then return XA_RDONLY from the driver's prepare() method in
that case.  As it is, it either throws an exception or returns XA_OK.

Michael