Does OpenSwitch support JDBC XA clients?
Joe Weinstein
works with JDBC clients doing XA transactions? I strongly suspect
that if OpenSwitch fails over from one DBMS to another during a
client's ongoing XA transaction, that transaction is defunct,
and I'd hope that the JDBC connection would get an Exception
to indicate such. What I would be scared of, is if the under-the-
covers failover was in some way 'transparent', leaving the
JDBC client to discover that they are 'not in Kansas anymore'.
thanks much in advance,
Joe Weinstein at BEA Systems
Neal Stack
It should support JDBC doing XA transactions. I can't say
that there has been a lot of interoperability testing for
this combination.
Ideally, what should happen is this:
- OpenSwitch is aware that the client is in a transaction.
- When ASE1 fails, OpenSwitch issues a "1205 deadlock" error
to any client that is in a transaction (or busy executing a
query). This is OpenSwitch's way of telling the client that
it needs to resubmit the transaction.
- Then OpenSwitch switches the client over to ASE2.
- It is up to the client to either resubmit the transaction
or move on.
I only know of one customer who is using this combination of products.
They were recently given a JDBC fix to address these problems:
CR 302049 -> removed begin tran from the MDA scripts from table spt_mda , column Query for GET_AUTOCOMMIT and GET_ISOLATION
CR387519 - JDBC3.0 if you are in a middle of the transaction and autocommit value is changed, the commit the current transaction.
CR 393327 - when you are in a global transaction , rollback the current LocalTransaction and chain a warning. Similalry Just chain
the warning ( dont Throw a Error )if rollback/commit/setAutocommit is called when a Global Transaction is Active
There was also an ASE bug that was fixed a while back that the
customer was given a fix for:
360975 - ASE returns a parameter xactstatus that the XA client never requested
ASE returns a parameter xactstatus that the XA client never requested.
This causes Open Server (like OpenSwitch) based applications to get the error
"Output parameter not found" and consequently makes XA incompatible with Open Server.
Joe Weinstein
> Hello,
> It should support JDBC doing XA transactions. I can't say
> that there has been a lot of interoperability testing for
> this combination.
>
> Ideally, what should happen is this:
> - OpenSwitch is aware that the client is in a transaction.
> - When ASE1 fails, OpenSwitch issues a "1205 deadlock" error
> to any client that is in a transaction (or busy executing a
> query). This is OpenSwitch's way of telling the client that
> it needs to resubmit the transaction.
> - Then OpenSwitch switches the client over to ASE2.
> - It is up to the client to either resubmit the transaction
> or move on.
Neal!
Thank you very much for the quick response. So do you know
whether any of the JDBC client's prepared statements are still
operable, or all they all dead? I would expect that all subobjects
of the JDBC connection are dead, and the client has to recreate
any such statements, and for the client JDBC code, this is almost
the same as starting from obtaining a new connection. In fact,
what about certain session state changes that had been made,
such as arithabort processing etc. Do they propagate, or do they
have to be re-established. If a user executes the SQL "use myotherdatabase",
I guess that context is lost in the failover, right?
If the customer has a warm-standby DBMS, what portion of a user's
transaction (XA or local) is propagated to the warm standby? Aside from
OpenSwitch, how (much) does a sybase DBMS warm standby support XA?
If you can point me to any public documents that describe this
expected OpenSwitch and/or warm-standby+transactions behavior, it
would be a great thing for me to help one of our mutual customers.
Joe Weinstein at BEA Systems (nee j...@sybase.com '88-'96)
Neal Stack
OpenSwitch does not keep track of session level changes
made through "set xxxx" statements. It does not track
these because it would have to parse every SQL statement
that comes through it and that would have severe performance
implications. This is documented here:
http://sybooks.sybase.com/onlinebooks/group-os/osg1251e/openswug/@Generic__BookTextView/757;pt=763#X
It does keep track of database context. If an application
does "use datababse123", the server returns a "5701" "Changed
database context to database123" message. OpenSwitch keeps
track of this for the connection and restores it on the failover.
As far as what portion of the XA transaction gets propogated/
replicated to the standby server, that falls into the Replication
Server realm (which I am not an expert). I would assume that
if a transaction was committed, the Replication Server would
replicate it.