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.
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.
-- 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, |
cx_oracle_prepare.patch
Description: Binary data
