Closed yundatm closed 10 years ago
(Note that "sqlite:///memory:" is a file, changing to "sqlite:///:memory:" does not work due to each thread having its own sqlite memory db.)
From the README:
By default, zope.sqlalchemy puts sessions in an 'active' state when they are first used. ORM write operations automatically move the session into a 'changed' state. This avoids unnecessary database commits.
This means that the first transaction.commit()
in thread_2
will result in a session.close()
instead of a session.commit()
. Removing the ZopeTransactionExtension and using session.close()
here and session.commit()
elsewhere shows the same behaviour.
That difference in behaviour is because SQLAlchemy sessions by default have expire_on_commit=True. When the session is closed rather than committed the instances are not expired. Adding in a manual call to dbs.expire_all()
fixes the problem:
from sqlalchemy import inspect
def thread_2(test_id):
dbs = DBSession()
o = dbs.query(TestTable).get(test_id)
sleep(1) # by this time thread 1 should be finished and changes committed in the db
print 'expired: %r' % inspect(o).expired
dbs.expire_all() # must come before transaction.commit() / session.close()
transaction.commit()
print 'expired: %r' % inspect(o).expired
dbs = DBSession()
o = dbs.merge(o)
print "order num after merge " + o.value # this should print "123", but prints "initial" <- that's wrong!
transaction.commit() # and then it updates value "123" back to "initial" (!!!)
Possibly zope.sqlalchemy should call session.expire_all()
before calling session.close()
when there were no changes to commit.
Thanks for quick response and for explanation! You're right, calling dbs.expire_all() fixes it. I do believe zope.sqlalchemy should do that automatically, otherwise it would have to be executed everywhere before commit (and only if there is no change). Also considering the destructive consequences... I mean it would be acceptable if the new value wasn't read from the db at that stage. The problem is that it is actually updated back to the database(!). Cheers, Martin.
Thanks heaps, that was quick! :-)
Hi there, I found a very disturbing problem in multi-threaded application. What happens:
This doesn't happen when not using ZopeTransactionExtension (i.e. using only SQLAlchemy and it's session.commit()). Also using ZopeTransactionExtension('changed') fixes the problem. Sample code below demonstrates the problem (see comments in function thread_2). Only dependencies are sqlalchemy and zope.sqlalchemy, it uses in-memory slqite. The problem was observed on SQLAlchemy 8.1 and also the latest 9.4 (haven't tested any other versions), Zope version 0.7.2 and latest 0.7.4. SQLite is used in the example, but the same problem happens on postgresql, too.