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 <[email protected]>
>
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
