5

I have I thread in a daemon, that loops and performs the following query:

    try:
        newsletter = self.session.query(models.Newsletter).\
               filter(models.Newsletter.status == 'PROCESSING').\
               limit(1).one()
    except sa.orm.exc.NoResultFound:
        self.logger.debug('No PROCESSING newsletters found. Sleeping...')
        self.sleep()
        return
    # (...) more code to do with found newsletter

Where the sleep method just stops the execution of this thread for the configured time and the return statement returns to the main loop. However I found, that if I change any newsletter's status to 'PROCESSING' while the daemon is running, nothing happens, ie. the query still raises NoResultFound. If I restart the daemon however, it will find the newsletter. So I see, that the results of this query must be cached. What can I do to invalidate the cache? session.expire_all() doesn't work. I could also create new Session() object every iteration, but don't know if it's a good approach regarding system resources.

zefciu
  • 1,967
  • 2
  • 17
  • 39
  • 2
    Bad assumptions on your part. Do you know how you database is serializing these two transactions? Do you know what database locks are involved in these two transactions? How do you know it's a SQLAlchemy cache? It's just as likely to be a database lock that prevents the update until the query finishes because the query takes out the wrong locks. Do you have evidence for caching? Or is that a guess? – S.Lott Nov 26 '10 at 13:06
  • I don't have any evidence. Just a guess. Maybe the caching is done somewhere else. Using rollback() seems to work here. – zefciu Nov 26 '10 at 13:31
  • It's more likely that it's **not** caching, but locking. – S.Lott Nov 26 '10 at 13:36
  • I'm having the exact same issue here, my results don't update for some reason, The output stays the same, weird part is it even happens on records I haven't fetched yet :/ and this is with vanilla SQLAlchemy – Not Available Dec 21 '10 at 12:31
  • http://stackoverflow.com/a/15788051/497208 can be useful – Jakub M. Apr 03 '13 at 13:02

4 Answers4

6

The problem in your code is due to database using REPEATABLE READ isolation level by default, so the query returns the same result unless you call commit() or rollback() (or use autocommit=True as Xeross suggested) or manually change isolation level.

Yes, SQLAlchemy does cache mapped objects (not query results!), because ORM pattern requires single object for each identity. By default SQLAlchemy uses weak identity map as cache, so object is automatically expunged from session when there is no references left to it. Note, that subsequent queries will update state of cached objects with new data, so no need to worry about this cache.

Denis Otkidach
  • 32,032
  • 8
  • 79
  • 100
5

SQLAlchemy doesn't cache on itself. Unless you explicitly implemented a cache, like this one.

Pass echo=True to your sessionmaker and look into the logging output.

knitti
  • 6,817
  • 31
  • 42
  • Passing echo=True doesn't work. I also tried to setup logging from sqlalchemy.engine logger, but it didn't work also. – zefciu Nov 29 '10 at 07:11
  • You might need to enable logging app-wide: https://gist.github.com/aphillipo/25ae5456cf1fe7fed1a5 – pip Oct 14 '14 at 14:41
1

don't use autocommit=True and expire_on_commit=True

for state in self.identity_map.all_states():
    state.expire(state.dict, self.identity_map._modified)

you can: after Query :db.session.commit()

qinmiao
  • 5,559
  • 5
  • 36
  • 39
1

Hmm I already found the answer, you apparently need to explicitly do session.commit() to get it to update, or you need to set autocommit=True on the session, through for example the sessionmaker.

sessionmaker(bind=self.engine, autocommit=True)

However I haven't tested the session.commit() way

So this isn't a caching issue it seems to be just the way transactions work

Not Available
  • 3,095
  • 7
  • 27
  • 31