5

Background

The get() method is special in SQLAlchemy's ORM because it tries to return objects from the identity map before issuing a SQL query to the database (see the documentation).

This is great for performance, but can cause problems for distributed applications because an object may have been modified by another process, so the local process has no ability to know that the object is dirty and will keep retrieving the stale object from the identity map when get() is called.

Question

How can I force get() to ignore the identity map and issue a call to the DB every time?

Example

  • I have a Company object defined in the ORM.
  • I have a price_updater() process which updates the stock_price attribute of all the Company objects every second.
  • I have a buy_and_sell_stock() process which buys and sells stocks occasionally.
    • Now, inside this process, I may have loaded a microsoft = Company.query.get(123) object.
    • A few minutes later, I may issue another call for Company.query.get(123). The stock price has changed since then, but my buy_and_sell_stock() process is unaware of the change because it happened in another process.
    • Thus, the get(123) call returns the stale version of the Company from the session's identity map, which is a problem.

I've done a search on SO(under the [sqlalchemy] tag) and read the SQLAlchemy docs to try to figure out how to do this, but haven't found a way.

tohster
  • 6,973
  • 5
  • 38
  • 55
  • Check out [`Session.expunge(...)`](http://docs.sqlalchemy.org/en/rel_0_9/orm/session_api.html#sqlalchemy.orm.session.Session.expunge) call before every `get(...)`. It might just be enough for you. However, I am not convinced this is the best practice for the UseCase. – van Mar 24 '15 at 19:18
  • Thanks @van would you happen to know how to check whether an instance for a given ID is in the identity map? Otherwise expunge may end up being a very expensive operation if I need to retrieve the object before expunging it – tohster Mar 24 '15 at 22:26

1 Answers1

4

Using session.expire(my_instance) will cause the data to be re-selected on access. However, even if you use expire (or expunge), the next data that is fetched will be based on the transaction isolation level. See the PostgreSQL docs on isolations levels (it applies to other databases as well) and the SQLAlchemy docs on setting isolation levels.

You can test if an instance is in the session with in: my_instance in session.

You can use filter instead of get to bypass the cache, but it still has the same isolation level restriction.

Company.query.filter_by(id=123).one()
SuperShoot
  • 9,880
  • 2
  • 38
  • 55
davidism
  • 121,510
  • 29
  • 395
  • 339