Oracle transaction ids (XID)

[Wolfgang Schnerring]

Hello,

If several processes using SA to access an Oracle database run
concurrently, I'm encountering all sorts of errors, e. g. "ORA-02051:
another session in same transaction failed", "ORA-24756: transaction
does not exist", "ORA-02079: no new sessions may join a committing
distributed transaction" and a few others. I don't have access to a
test oracle system, so I can't really use SA's testsuite and provide a
proper failing test, but I've attached a small reproduction recipe
(execute a writing query and commit, do this twice concurrently -- and
voilá).

I suspect the way SA generates transaction IDs for Oracle is the wrong
way around. Oracle XIDs have two components, a transaction identifier
and a branch identifier. SA uses a fixed transaction ID and only
varies the branch ID. But this means that *any* python program that
uses SA to access the database says it wants to participate in the
same transaction!
As far as I understand what the XA standard says about transactions
and their identifiers, SA should generate XIDs the other way around:
vary the transaction ID (and probably keep the branch ID fixed for
simplicity), as in the patch below.

Any thoughts? Am I missing something?

Thanks for your help,
Wolfgang

Index: lib/sqlalchemy/databases/oracle.py
===================================================================
--- lib/sqlalchemy/databases/oracle.py (revision 5336)
+++ lib/sqlalchemy/databases/oracle.py (working copy)
@@ -441,7 +441,7 @@
do_commit_twophase(). its format is unspecified."""

id = random.randint(0, 2 ** 128)
- return (0x1234, "%032x" % 9, "%032x" % id)
+ return (0x1234, "%032x" % id, "%032x" % 9)

def do_release_savepoint(self, connection, name):
# Oracle does not support RELEASE SAVEPOINT

--
Wolfgang Schnerring · w...@gocept.com
gocept gmbh & co. kg · forsterstraße 29 · 06112 halle (saale) · germany
http://gocept.com · tel +49 345 1229889 0 · fax +49 345 1229889 1
Zope and Plone consulting and development

[Michael Bayer]

you're not missing anything - I've never gotten twophase to work with
Oracle, after poring over dozens of obscure documentation pages on
their site. I wrote Anthony Tuniga about it and he said he had never
seen Oracle twophase work either with cx_oracle, though he sent me
some details on things to try which seemed promising (it was probably
this). that was probably over a year ago and I never got to work on
it further.

So, with the below patch you're actually getting successful two-phase
transactions to work with oracle ?

> <ora-xid.py>

[Wolfgang Schnerring]

* Michael Bayer <zzz...@gmail.com> [2008-12-03 14:43]:

> you're not missing anything - I've never gotten twophase to work with
> Oracle, after poring over dozens of obscure documentation pages on
> their site. I wrote Anthony Tuniga about it and he said he had never
> seen Oracle twophase work either with cx_oracle, though he sent me
> some details on things to try which seemed promising (it was probably
> this). that was probably over a year ago and I never got to work on
> it further.

Ouch. That's bad news indeed.

> So, with the below patch you're actually getting successful two-phase
> transactions to work with oracle ?

I would have loved to say yes, and the small reproduction example I gave does
work (consistently) with that patch, but I've just seen our (rather bigger)
application tests fail again -- with the patch applied. Damn.
I'll try and investigate this some more.

Wolfgang

[Wolfgang Schnerring]

* Wolfgang Schnerring <w...@gocept.com> [2008-12-04 09:58]:

>> So, with the below patch you're actually getting successful two-phase
>> transactions to work with oracle ?
>
> I would have loved to say yes, and the small reproduction example I gave does
> work (consistently) with that patch, but I've just seen our (rather bigger)
> application tests fail again -- with the patch applied. Damn.
> I'll try and investigate this some more.

Phew. That was some unrelated issue that unfortunately looked way too similar on
first sight.

So, yes, with the patch applied I can do two-phase commits to Oracle
successfully. What should be done to get this included into SA?
I don't have a test system available to run SA's test suite to be
more sure about the change; I'll see if I can get access to one, or maybe
somebody else around here does?

[Michael Bayer]

I can just commit the change. im not sure if a real unit test for
this feature in full is really feasable.

[Wolfgang Schnerring]

* Michael Bayer <zzz...@gmail.com> [2008-12-04 15:18]:

>> So, yes, with the patch applied I can do two-phase commits to Oracle
>> successfully. What should be done to get this included into SA?

> I can just commit the change. im not sure if a real unit test for
> this feature in full is really feasable.

By all means, go ahead. ;-) I'd be very glad not having to patch this locally.
My application tests fully support the thesis that the patch helps. A lot.
I'll try and see if I can write an isoltated test for this when I get some spare
cycles, but like you I'm not sure it's easily done.

Thanks,

[Michael Bayer]

OK this is in r5419.