Re: Support for XA

On Thu, Jun 18, 2009 at 10:27:46AM -0400, Hildo Biersma wrote:
David Dooling wrote on 06/18/09 00:01:
On Wed, Jun 17, 2009 at 09:15:49PM +0100, Tim Bunce wrote:
On Wed, Jun 17, 2009 at 01:53:36PM -0500, David Dooling wrote:
On Wed, Jun 17, 2009 at 01:08:26PM +0800, Matthew Watson wrote:
Does DBI ( specifically DBD::Oracle ) have any support for
distributed transactions (XA). I have a need to share an oracle
session between java and perl clients, this could be done in Oracle
11g via the DBMS_XA package, however it seems like it should be
doable in earlier versions via OCI, is this support at all?

UR can do this, but not directly at the database level. The bad part
of this is that it does not use Oracle's methodology to do this. The
good part is that it will work across databases residing on different
RDBMS's.

http://search.cpan.org/search?query=UR&mode=module

Basically, UR's object cache / Context acts as a software transaction
that sits on top of whatever database transactions are active through
the data sources. So what ends up happening is that you can load
objects that come from multiple databases, and when you tell the
Context to commit, it executes the SQL separately to each database in
its own transaction. After all the databases' SQL statements have
completed successfully, it tells each DB transaction to commit.

If any of the SQL fails, then all the DB transactions are rolled
back.

If two databases are being used, and the commit to the first succeeds
and the commit to the second fails, how does it "rollback" the first
commit?

If the SQL is successful and the commit fails, an error is thrown.
That requires some manual intervention (much like a failed commit for
a transaction on a single database). Theoretically, the STM engine
could attempt to undo what it had done on the successful commits, but
that may be dangerous and not what the developer would want.

The whole point of XA is that this situation cannot occur. An external
transaction manager works with the databases (and other transaction-aware
engines) to make sure all are aware of a cross-system transaction; all go
through a prepare phase and a commit phase; and outages are handled at
the transaction coordinator (how is another story - it's far from
trivial).

Yes, I should have been more precise in my language. UR, in the
software layer, does the best job it can in managing transactions
*across database instances*. This is different than the original
problem described and the one, to my understanding, XA is used to
solve. The XA problem involves sharing a session, and therefore
transaction, across multiple applications but within the same
database. I don't think XA will work across databases (although
Oracle allows dblinks to get around that if all the db's are running
Oracle).

I'm sorry, but the UR system describes doesn't even come close to
solving the XA problem.

Don't be sorry, you didn't write UR <wink />. The basic problem is that
UR attempts to be database agnostic, as such there is no way to do
something like you describe for XA. In the future, support for XA
could be added such that those features could be used if you were only
using Oracle on the back-end (that's an easier problem than
transactions distributed across databases).

--
David Dooling
http://www.politigenomics.com/
.

Relevant Pages

  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Driver AutoCommit issue
    ... I am using the container managed transaction and I expect the container to handle that for me. ... Is there different driver class that i have to use in the connection pool configuration. ... Why I am saying the DML always gets committed is when I step thought the code I can see the updated data in the database immediately after the callable statement is executed Even before the EJB method that invoked the call is completed. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Help - Timing Logic
    ... build a trigger on the database .. ... Transaction and Database Locking - look at isolation levels / settings ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... message broker gets all the necessary messages or message id's from the database to be sent ... ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Slow connection to Oracle 9i
    ... A commit() should be issues only when necessary - the cost in the database of a commit is large and doing so in this random fashion is an invitation to other performance problems. ... Slow connection to Oracle 9i ... do not get a transaction too long error (can't remember exactly what its ...
    (perl.dbi.users)