query returns old data until session is closed
J Stam
I realize that I need to close or remove the session when I am finished, but shouldn't a query return the current value from the database?
I boiled it down to a simple test program. I am not seeing what I'm missing. Thanks.
----
from sqlalchemy import *
from sqlalchemy.orm import *
import time, thread
class Thing(object):
pass
def Collector():
while 1:
session = Session()
for t in session.query(Thing):
t.tick += 1
print 'Thread C - %d' % t.tick
session.commit()
time.sleep(1)
def Analyzer():
while 1:
session = Session()
print '----'
for i in range(0,5):
for t in session.query(Thing):
print 'Thread A - %d' % t.tick
time.sleep(1)
session.close()
if __name__ == "__main__":
engine = create_engine('sqlite:////tmp/thing.db', strategy='threadlocal', echo=False)
mt = MetaData()
mt.bind = engine
Session = scoped_session(sessionmaker(bind=engine, autoflush=False, autocommit=False))
tbl_thing = Table('tbl_thing', mt,
Column('id', Integer, primary_key=True),
Column('tick', Integer, default=0 ),
)
Session.mapper(Thing, tbl_thing, save_on_init=False)
mt.create_all(engine)
session = Session()
t1 = Thing()
session.add(t1)
session.commit()
thread.start_new_thread( Collector, () )
thread.start_new_thread( Analyzer, () )
while 1:
time.sleep(1)
Michael Bayer
On Dec 14, 2008, at 12:40 PM, J Stam wrote:
>
> Within a contextual session, multiple queries all seem to return the
> same data even though with SQL logging I can see the data has been
> changed by another thread and a SELECT has been issued by the query.
Changes from other transactions will not be visible until SQL is re-
issued in a transaction that's begun after those transactions have
been completed (assuming you're using a system that features
transaction isolation, which is most). To satisfy the
transactional requirement, the session's transaction needs to be ended
either via rollback or commit (or close(), which is essentially a
rollback). This also happens implicitly after each operation if
autocommit=True. To satisfy the re-issuing of SQL requirement, the
contents of the session need to be expired (which is automatic after
an explicit rollback() or commit(), or occurs via expire_all()), or
removed entirely (which is a clear() or close(), or a remove() when
using a contextual session).
The session is designed to perform these tasks most closely
corresponding to the actual database transaction when you use
autocommit=False,autoflush=True,expire_on_commit=True, which also
produces the maximum back-and-forth SQL traffic. Adjusting these
elements will change the steps required to achieve this result, but
the essential facts that SQL needs to be re-issued in a fresh
transaction are universal to all scenarios.