On Nov 21, 2012, at 5:32 AM, Thierry Florac wrote: > 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 > ?
I can backport but I really need to make sure it works in a good deal of scenarios, so I'm hoping whatever prevented it from working for me some years back no longer applies. its tracked at http://www.sqlalchemy.org/trac/ticket/2611 for now. > > 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" : >> >> Prepared Data on the node has been modified by a statement in the >> distributed transaction, and the node was successfully prepared. >> Read-only No data on the node has been, or can be, modified (only >> queried), so no preparation is necessary. >> Abort The 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. -- 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.
