ObjectDeletedError in master/slave configuration

[Colton Allen]

Hi,

I'm using a custom session class to route requests to different database engines based on the type of action being performed.  Master for writes; slave for reads.  It looks my attributes on my models expire immediately after creation.  Anyway to prevent this?  Or should I not worry about preventing it the expiration?

Session class:

class RoutedSession(Session):

    def execute(self, *args, **kwargs):
        return super().execute(*args, **kwargs, use_master=True)

    def get_bind(self, mapper=None, clause=None, use_master=False):
        if use_master or self._flushing:
            return ENGINES['master']
        return random.choice(ENGINES['slaves'])

Application code:

model = MyModel()
session.add(model)
session.commit()

print(model.id)  # ObjectDeletedError: Instance '<Model at 0x...>' has been deleted, or its row is otherwise not present.

Traceback:

File "tests/mock.py", line 2414, in populate_db_with_mock_data
  contact_id=contact2.id, account_id=account.id,
File "sqlalchemy/orm/attributes.py", line 275, in __get__
  return self.impl.get(instance_state(instance), dict_)
File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)
File "sqlalchemy/orm/state.py", line 632, in _load_expired
  self.manager.deferred_scalar_loader(self, toload)
File "sqlalchemy/orm/loading.py", line 985, in load_scalar_attributes
  raise orm_exc.ObjectDeletedError(state)

[Jonathan Vanasco]

My first guess is two things are going on:

1. This is a behavior of `expire_on_commit` on the session.  Once you commit on the Primary database, the object is stale.

   https://docs.sqlalchemy.org/en/13/orm/session_api.html

2. The session is then trying to read off a Secondary database, but the row has not yet synced.

You can adjust `expire_on_commit` if you're only doing short-term read-only actions. However,  I would explore to ensure this is trying to read off the other database and why.

[Colton Allen]

> You can adjust `expire_on_commit` if you're only doing short-term read-only actions.

Can you expand on this?  Or link to docs/blog so I can do some research.  Google hasn't helped me so far.  Why would I want to expire after every commit?

---

I agree with your assessment.  I think its because every time I call "session".  I'm actually saying "session_maker()".  So the _flushing attribute will be reset because its a new session instance.

[Jonathan Vanasco]

By default, SqlAlchemy has `expire_on_commit=True`.

I'm going to poorly restate most of what Mike Bayer has told me in the past:  the rationale behind this- an active Session is supposed to mirror the current state in the database; within a transaction we know the object values are equal to the table values.  Once the transaction ends via `commit`, the object is no longer in sync - another transaction may have modified that database row.  

In your example, note these 2 lines from the stacktrace:

File "sqlalchemy/orm/attributes.py", line 669, in get
  value = state._load_expired(state, passive)

when you invoked a `commit`, SqlAlchemy expired the object (after the commit).

when you invoked `print()`, SqlAclhemy recognized the object as expired and is now trying to load the expired attributes so it can print them for you.

[Mike Bayer]

On Thu, Mar 26, 2020, at 2:18 PM, Colton Allen wrote:

> You can adjust `expire_on_commit` if you're only doing short-term read-only actions.

Can you expand on this?  Or link to docs/blog so I can do some research.  Google hasn't helped me so far.  Why would I want to expire after every commit?

because once the transaction is completed, there are other transactions going on in a database concurrently which can change the state of the objects as they are represented in the database.  in order that when you next access these local objects so that they have the correct state, they are automatically expired.   However this behavior is not desirable in many cases, so this flag is very commonly used to disable this behavior when you are not concerned about your objects having stale data relative to other transactions going on, when the new transaction begins.

this behavior is described at: https://docs.sqlalchemy.org/en/13/orm/session_basics.html#committing

---

I agree with your assessment.  I think its because every time I call "session".  I'm actually saying "session_maker()".  So the _flushing attribute will be reset because its a new session instance.

On Thursday, March 26, 2020 at 1:02:18 PM UTC-5, Jonathan Vanasco wrote:

My first guess is two things are going on:

1. This is a behavior of `expire_on_commit` on the session.  Once you commit on the Primary database, the object is stale.

   https://docs.sqlalchemy.org/en/13/orm/session_api.html

2. The session is then trying to read off a Secondary database, but the row has not yet synced.

You can adjust `expire_on_commit` if you're only doing short-term read-only actions. However,  I would explore to ensure this is trying to read off the other database and why.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/8185b96d-35cb-4973-a9cf-1e572ebd643b%40googlegroups.com.

[Colton Allen]

Aside from disabling expire_on_commit, any thoughts on how I can prevent this error?  I guess I just need a method to force the attribute refresh to use the master database.  I'm just not sure where I should put that.  Thoughts?

I think one of my previous comments got lost because of formatting.  Quoting it here for safety.

"I agree with your assessment.  I think its because every time I call "session".  I'm actually saying "scoped_session(session_maker)()".  So the _flushing attribute will be reset because its a new session instance."

To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Mike Bayer]

On Thu, Mar 26, 2020, at 2:45 PM, Colton Allen wrote:

Aside from disabling expire_on_commit, any thoughts on how I can prevent this error?  I guess I just need a method to force the attribute refresh to use the master database.  I'm just not sure where I should put that.  Thoughts?

is the issue that your follower database is only updating asynchronously?   I would likely organize the application to simply use two different Session objects, one for master one for follower.   Trying to do it on a per-query basis is needlessly implicit and complicated.    Individual service methods would declare whether they are "read only" or "writer" and they start out with one or the other session/engine.    That's how we do it in Openstack.   thinking in terms of logical service methods rather than individual queries is the way to go.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dd5c4d05-5af3-451e-b965-52ac50b432ac%40googlegroups.com.

[Jonathan Vanasco]

On Thursday, March 26, 2020 at 2:53:08 PM UTC-4, Mike Bayer wrote:

is the issue that your follower database is only updating asynchronously?   I would likely organize the application to simply use two different Session objects, one for master one for follower.   Trying to do it on a per-query basis is needlessly implicit and complicated.   

 In my applications, I have explicit Primary/Writer and Secondary/Reader sessions.  As a convenience, I will create a shortcut to proxy one of them onto a request(web)/context object as `.dbSession`.  Certain logic flows will only utilize the Primary/Writer, other logic flows will only utilize the Secondary/Reader.