Hi Michael, I applied your patch on my current SA 0.7.3 without any problem, and added a few traces. Everything seems to work perfectly!
Here is the first output with updates: >>> do_begin_twophase >>> do_prepare_twophase = True >>> do_commit_twophase > oci_prepared = True > do_commit and without "real" updates: >>> do_begin_twophase >>> do_prepare_twophase = False >>> do_commit_twophase > oci_prepared = False > no commit ! That's great, for me it works like a charm! Do you think that this patch could be backported to a new 0.7.x release of SA ? Best regards, Thierry 2012/11/21 Michael Bayer <mike...@zzzcomputing.com> > great. can you try the attached patch please (latest 0.8 tip), which will > not call "commit" if the "prepare" returned false. I'm not sure if this > is complete though, if we should be doing a "rollback" afterwards, or what. > > > not sure if I can get my local oracle XE to do two phase for real, that > would help a lot. this patch would need quite a few tests. > > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > > On Nov 20, 2012, at 6:33 PM, Thierry Florac wrote: > > Hi Michael, > > Following your guidelines, this is the first test I made against cx_Oracle > : > > >>> conn = cx_Oracle.connect('scott','tiger','localhost:1521/ORCL', > twophase=True) > >>> id = random.randint(0, 2 ** 128) > >>> xid = (0x1234, "%032x" % id, "%032x" % 9) > >>> conn.begin(*xid) > >>> conn.prepare() > False > >>> conn.commit() > Traceback (most recent call last): > File "<console>", line 1, in <module> > DatabaseError: ORA-24756: la transaction n'existe pas > > > As you can see, the "prepare" method returns False, meaning that the > transaction was *NOT* prepared. > If I add any update statement, everything is OK. > > Oracle 11g documentation seems to indicate that the "prepare" call can > return three "status" : > > PreparedData on the node has been modified by a statement in the > distributed transaction, and the node was successfully prepared. Read-onlyNo > data on the node has been, or can be, modified (only queried), so no > preparation is necessary. AbortThe node cannot successfully prepare. > > cx_Oracle returns False "when there is nothing available to prepare", so > probably at least in case 2, case for which "commit shouldn't be done" to > avoid ORA-24756 error. > So it's behaviour seems conform with Oracle OCI documentation... > > So my last question is : in the context of an SA session, who is > responsible of the prepare/commit statements, so that commit is not called > if prepared was not OK ? > > Best regards, > Thierry > > > 2012/11/20 Michael Bayer <mike...@zzzcomputing.com> > >> Googling for the ORA code yields the usual tautological answers that say >> nothing. >> >> The first step here would be to isolate the issue in a simple test case >> with SQLAlchemy only, and then cx_oracle where I suspect the bug lies. >> >> The Session will not create a transaction at all, if no SQL is emitted. >> So that would mean SQL is definitely emitted. Can you confirm this ? (look >> at your SQL logging). >> >> Then, an initial test would be like this: >> >> s = Session(twophase=True, bind=my_oracle_engine) >> s.execute("select 1 from dual") >> s.commit() >> >> >> Here's my test, against Postgresql: >> >> from sqlalchemy.orm import Session >> from sqlalchemy import create_engine >> >> e = create_engine('postgresql://scott:tiger@localhost/test', echo=True) >> >> s = Session(e, twophase=True) >> s.execute("select 1") >> s.commit() >> >> the output: >> >> 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine select >> version() >> 2012-11-20 10:53:06,894 INFO sqlalchemy.engine.base.Engine {} >> 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine select >> current_schema() >> 2012-11-20 10:53:06,897 INFO sqlalchemy.engine.base.Engine {} >> 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine select 1 >> 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} >> 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine PREPARE >> TRANSACTION '_sa_ddca4886b1f5db002e83058341de2609' >> 2012-11-20 10:53:06,900 INFO sqlalchemy.engine.base.Engine {} >> 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine COMMIT >> PREPARED '_sa_ddca4886b1f5db002e83058341de2609' >> 2012-11-20 10:53:06,908 INFO sqlalchemy.engine.base.Engine {} >> 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine BEGIN >> 2012-11-20 10:53:06,909 INFO sqlalchemy.engine.base.Engine {} >> >> which I will explain, first there's a "BEGIN TWOPHASE" that unfortunately >> is not logged, I have just committed a fix for that in tip. Then there's >> the PREPARE and the COMMIT - but this is Postgresql's syntax. The Oracle >> implementation only uses an XID when it calls cx_oracle's "begin()" method, >> and then I'd assume its up to cx_oracle to do the rest. So if there's a >> bug anywhere, it's probably in cx_oracle. My guess is that cx_oracle >> itself is not emitting the BEGIN unless it sees DML (INSERT, UPDATE, >> DELETE). The "BEGIN" you see afterwards is a tweak that's specific to >> the psycopg2 dialect when twophase is used so that it can run the next >> transaction. >> >> A cx_oracle script would be: >> >> import cx_Oracle >> import random >> >> conn = cx_Oracle.connect(...) >> >> id = random.randint(0, 2 ** 128) >> xid = (0x1234, "%032x" % id, "%032x" % 9) # no idea where i got this >> from, probably cx_oracle >> conn.begin(*xid) >> conn.prepare() >> conn.commit() >> >> >> if that fails, but then if you were to insert in the middle: >> >> cursor = conn.cursor() >> cursor.execute("INSERT INTO sometable (q) VALUES (3)") >> >> and then it works, there you go. cx_oracle's bad behavior. >> >> All of the "two phase" implementations required weird hacks to get them >> to work, as this feature is exceedingly rare. But for cx_Oracle, I never >> saw it work completely hence we haven't been able to come up with >> workarounds for existing issues. >> >> >> >> On Nov 20, 2012, at 4:28 AM, Thierry Florac wrote: >> >> Hi Michael, >> >> This is a complete traceback produced by removing my checking code >> against attribute modification. >> Original code is then as simple as this: >> >> @ajax.handler >> def updateOrderMenuItemBO(self): >> session = getSession(SESSION_NAME) >> ids = [ int(id) for id in self.request.form.get('ids') ] >> for menu in >> session.query(MenuItemBO).filter(MenuItemBO.id.in_(ids)): >> index = ids.index(menu.id) + 1 >> menu.rang = index >> return getUtility(IJSONWriter).write({ 'status': 'OK' }) >> >> >> URL: >> http://172.23.109.204:8080/boconsult/%40%40menuItemBo.html/%40%40ajax/updateOrderMenuItemBO >> File >> '/var/local/env/rdf/lib/python2.5/site-packages/Paste-1.7.5.1-py2.5.egg/paste/evalexception/middleware.py', >> line 306 in respond >> app_iter = self.application(environ, detect_start_response) >> File '/var/local/env/rdf/webapp/src/webapp/startup.py', line 33 in wrapper >> return app(environ, start_response) >> File >> '/var/local/env/rdf/webapp/eggs/zope.app.wsgi-3.4.3-py2.5.egg/zope/app/wsgi/__init__.py', >> line 55 in __call__ >> request = publish(request, handle_errors=handle_errors) >> File >> '/var/local/env/rdf/webapp/eggs/zope.publisher-3.4.10-py2.5.egg/zope/publisher/publish.py', >> line 138 in publish >> publication.afterCall(request, obj) >> File >> '/var/local/env/rdf/webapp/eggs/zope.app.publication-3.4.4-py2.5.egg/zope/app/publication/browser.py', >> line 78 in afterCall >> super(BrowserPublication, self).afterCall(request, ob) >> File >> '/var/local/env/rdf/webapp/eggs/zope.app.publication-3.4.4-py2.5.egg/zope/app/publication/zopepublication.py', >> line 175 in afterCall >> txn.commit() >> File >> '/var/local/env/rdf/webapp/eggs/ZODB3-3.8.5-py2.5-linux-x86_64.egg/transaction/_transaction.py', >> line 325 in commit >> self._commitResources() >> File >> '/var/local/env/rdf/webapp/eggs/ZODB3-3.8.5-py2.5-linux-x86_64.egg/transaction/_transaction.py', >> line 432 in _commitResources >> rm.tpc_finish(self) >> File '/var/local/src/ztfy/ztfy.alchemy/ztfy/alchemy/datamanager.py', line >> 148 in tpc_finish >> self.tx.commit() >> File >> '/var/local/env/rdf/webapp/eggs/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/orm/session.py', >> line 317 in commit >> t[1].commit() >> File >> '/var/local/eggs-2.5/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/engine/base.py', >> line 1971 in commit >> self._do_commit() >> File >> '/var/local/eggs-2.5/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/engine/base.py', >> line 2059 in _do_commit >> self.connection._commit_twophase_impl(self.xid, self._is_prepared) >> File >> '/var/local/eggs-2.5/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/engine/base.py', >> line 1290 in _commit_twophase_impl >> self.engine.dialect.do_commit_twophase(self, xid, is_prepared) >> File >> '/var/local/eggs-2.5/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/dialects/oracle/cx_oracle.py', >> line 720 in do_commit_twophase >> self.do_commit(connection.connection) >> File >> '/var/local/env/rdf/webapp/eggs/SQLAlchemy-0.7.3-py2.5-linux-x86_64.egg/sqlalchemy/engine/default.py', >> line 306 in do_commit >> connection.commit() >> DatabaseError: ORA-24756: la transaction n'existe pas >> >> >> SA integration in Zope in done throught my own "ztfy.alchemy" package, >> which is mainly a copy of "zope.alchemy" package to which I added several >> utility tools to facilitate engines definition via persistent utilities. >> But the transaction machinery is unchanged. >> Session definition in getSession function is globally done as follows >> (simplified): >> >> def getSession(dsn, status=STATUS_ACTIVE): >> engine = sqlalchemy.create_engine(dsn,echo,pool_size,pool_recycle, >> encoding,convert_unicode,strategy='threadlocal') >> factory = scoped_session(sessionmaker(bind=engine, twophase=True, >> extension=ZopeTransactionExtension())) >> session = factory() >> if join: >> join_transaction(session, initial_state=status) >> return session >> >> >> Adding a few traces in ZopeTransactionException class also add these >> messages on the console: >> >> >>> after begin >> 2012-11-20T10:01:12 INFO sqlalchemy.engine.threadlocal.TLEngine SELECT >> consultbo."MENU_ITEM_BO".id AS "consultbo_MENU_ITEM_BO_id", >> consultbo."MENU_ITEM_BO".id_app AS "consultbo_MENU_ITEM_BO_id_app", >> consultbo."MENU_ITEM_BO".id_appapp AS "consultbo_MENU_ITEM_BO_i_1", >> consultbo."MENU_ITEM_BO".niveau AS "consultbo_MENU_ITEM_BO_niveau", >> consultbo."MENU_ITEM_BO".libelle AS "consultbo_MENU_ITEM_BO_libelle", >> consultbo."MENU_ITEM_BO".rang AS "consultbo_MENU_ITEM_BO_rang", >> consultbo."MENU_ITEM_BO".nature AS "consultbo_MENU_ITEM_BO_nature" >> FROM consultbo."MENU_ITEM_BO" >> WHERE consultbo."MENU_ITEM_BO".id IN (:id_1, :id_2, :id_3, :id_4, :id_5, >> :id_6, :id_7, :id_8, :id_9) >> 2012-11-20 10:01:12,266 INFO sqlalchemy.engine.threadlocal.TLEngine >> {'id_2': 8, 'id_3': 1, 'id_1': 11, 'id_6': 12, 'id_7': 13, 'id_4': 10, >> 'id_5': 5, 'id_8': 14, 'id_9': 15} >> ------ >> >>> after flush >> >>> before commit >> ------ >> 2012-11-20T10:01:12 CRITICAL txn.139760315672320 A storage error occurred >> during the second phase of the two-phase commit. Resources may be in an >> inconsistent state. >> >>> after rollback >> >> >> Hope this can help to understand the problem; any advise is greatly >> welcome... >> >> Best regards, >> Thierry >> >> >> >> 2012/11/20 Michael Bayer <mike...@zzzcomputing.com> >> >>> >>> On Nov 19, 2012, at 6:23 PM, Thierry Florac wrote: >>> >>> > As far as I can understand it, I'm globally OK with you but... probably >>> > not completely :-\ >>> > I agree with the fact that SQLAlchemy is not the only package which >>> > takes part into the global transaction, as SA's session is handled by a >>> > Zope transaction manager. And the whole mechanism works globally >>> > perfectly. >>> > What I don't understand is that when: >>> > - I open a session, >>> > - I load a set of objects from the database, >>> > - I update these objects, setting an attribute with it's current value >>> > (I know, said like that it can seems a little silly!), >>> >>> no problem with that, it produces an event which will cause the flush to >>> do something, but no UPDATE will be emitted in most cases if no net change >>> >>> > - then when the transaction is committed, SA probably knows that the >>> > objects were not really modified, because no UPDATE instruction is >>> > executed; >>> >>> yup >>> >>> > but the SA two-phases transaction is begun, prepared and >>> > aborts on commit because of the given Oracle error. >>> >>> the first news to me here is that two-phase commit works with cx_oracle >>> and SQLAlchemy. I've never seen it work or been able to test it. >>> >>> I'm also not familiar with this behavior. Oracle won't allow PREPARE ? >>> Can I see stack trace + full ORA message ? the error code is especially >>> significant, had no idea this was an Oracle error. >>> >>> Also, is this two-phase as implemented by SQLAlchemy itself ? You have >>> "twophase=True" with sessionmaker() ? I hope zope.sqlalchemy isn't >>> hardcoding that.. >>> >>> >>> > - but if I add a check to update objects attribute only if the new >>> > value is different from the actual one, the SA transaction is begun >>> > on my first request, but not prepared nor committed, and no error is >>> > raised, >>> >>> doesn't sound right. If SA starts a transaction, it will also be >>> PREPARE/COMMITTED because zope.sqlalchemy is calling commit() on the >>> Session. You can't be just leaving that new transaction hanging open. >>> Or it might be rolled back, but its one or the other. >>> >>> >>> >> >> >> -- >> http://www.imagesdusport.com -- http://www.ztfy.org >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to sqlalchemy@googlegroups.com. >> To unsubscribe from this group, send email to >> sqlalchemy+unsubscr...@googlegroups.com. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > > > -- > http://www.imagesdusport.com -- http://www.ztfy.org > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalchemy@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > > -- http://www.imagesdusport.com -- http://www.ztfy.org -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
