SQLAlchemy transaction problem

[Thierry Florac]

Hi,

I'm using SQLALchemy (currently 0.7.3 in this project, also tested with 0.7.9) with cx_Oracle-5.1.1 in a Zope3 project.

SQLAlchemy transactions are handled in two-phase with ZopeTransactionExtension.

My problem is quite simple : in a form, I can order several items which updates an "index" attribute. Problem : if no ordering is done and "save" button is clicked, I get the given "transaction does not exist" error.

If I just add a control in my form handling method to set a new index value only if it was modified (instead of setting the attribute with the same value), everything is fine.

So my question is : is it normal that setting objects attributes without changing their actual value generates this error ?

Best regards,

Thierry

[Michael Bayer]

That's not a SQLAlchemy error. SQLAlchemy's Session has no linkage between its notion of a transaction and whether or not any state was changed. If you're working with a Session that hasn't accessed the database, there won't be any actual database transaction present until that happens. But that isn't an error condition.

You might want to check with the zope.transaction folks as well as with whatever web framework you're using.

[Thierry Florac]

Le Mon, 19 Nov 2012 10:23:14 -0500,
Michael Bayer <mik...@zzzcomputing.com> a écrit:

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!),
- then when the transaction is committed, SA probably knows that the
objects were not really modified, because no UPDATE instruction is
executed; but the SA two-phases transaction is begun, prepared and
aborts on commit because of the given Oracle error.
- 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,
- and of course, if I really modify any object of my set, the
transaction is begun, prepared and committed without any error...

Any idea ?

Best regards,
Thierry

[Michael Bayer]

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.

[Thierry Florac]

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 <mik...@zzzcomputing.com>

--
http://www.imagesdusport.com -- http://www.ztfy.org

[Michael Bayer]

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.  

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Thierry Florac]

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 ? 

[Michael Bayer]

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.

[Thierry Florac]

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 <mik...@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.

[Michael Bayer]

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.

[Michael Bayer]

this will need lots more work on my end and may not be possible without major caveats.  once you use connection.begin(*xid), the cx_oracle connection goes into a new mode that seems to render the usual DBAPI contract of "implicit begin" permanently broken.   I've spent about three hours trying to get the right combination of begin()/commit()/rollback() calls into the cx_oracle dialect, but the test suite still easily puts the connection into a permanently broken state as soon as it attempts to use the DBAPI connection traditionally after a two-phase operation.

On Nov 21, 2012, at 5:32 AM, Thierry Florac wrote: