StaleDataError when accessing a relationship attribute

[Vinit Shah]

I have code that looks like the following:

games = session.query(Game)

game = [g for g in games][0]

court = game.court # StaleDataError

game.

session.add(game)

Game looks like:

class Game(Base):

    __tablename__ = "Games"

    id = Column(Integer, primary_key=True)

    courtId = Column('courtId', Integer, ForeignKey('Courts.id'))

    courtId = Column('courtId', Integer, ForeignKey('Courts.id'))

    court = relationship(Court, foreign_keys=[courtId], backref="games")

    revision = Column(Integer, nullable=False)

    __mapper_args__ = {

        'version_id_col': revision

    }

I've found that this error occurs when another process has updated the same row, which makes sense, since that is the purpose of the version_id_col. 

In the contrived example above, it's been difficult to reproduce the error. In production, I have found this happens consistently, so long as another process has updated the same Game row. 

I can easily wrap a "try/except" around where "court" is accessed from "game", but I'm curious as to why an UPDATE statement happens there in the first place.

Below is the stacktrace:

    court = game.court

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 243, in __get__

    return self.impl.get(instance_state(instance), dict_)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/attributes.py", line 602, in get

    value = self.callable_(state, passive)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 634, in _load_for_state

    session, state, primary_key_identity, passive)

  File "<string>", line 1, in <lambda>

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py", line 727, in _emit_lazyload

    primary_key_identity)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/ext/baked.py", line 570, in _load_on_pk_identity

    result = list(bq.for_session(self.session).params(**params))

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/ext/baked.py", line 410, in __iter__

    self.session._autoflush()

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1493, in _autoflush

    self.flush()

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2313, in flush

    self._flush(objects)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2440, in _flush

    transaction.rollback(_capture_exception=True)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__

    compat.reraise(exc_type, exc_value, exc_tb)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2404, in _flush

    flush_context.execute()

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 398, in execute

    rec.execute(self)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 563, in execute

    uow

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 177, in save_obj

    mapper, table, update)

  File "/home/prism/.local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 797, in _emit_update_statements

    (table.description, len(records), rows))

StaleDataError: UPDATE statement on table 'Games' expected to update 1 row(s); 0 were matched.

Environment:

Python 2.7

SQLAlchemy 1.3.0b1

Oracle

[Vinit Shah]

To consistently reproduce the error, the object needs to have changed before accessing the relationship attribute:

import random

games = session.query(Game)

game = [g for g in games][0]

game.status = 'something_{}'.format(random.randint(0,100)) # Change made

court = game.court # StaleDataError if another processes has updated game as well

session.add(game)

Running the above code at the same time results in the consistent StaleDataError

[Mike Bayer]

On Fri, Aug 16, 2019, at 2:18 PM, Vinit Shah wrote:

I have code that looks like the following:

games = session.query(Game)

game = [g for g in games][0]

court = game.court # StaleDataError

game.

session.add(game)

Game looks like:

class Game(Base):

    __tablename__ = "Games"

    id = Column(Integer, primary_key=True)

    courtId = Column('courtId', Integer, ForeignKey('Courts.id'))

    courtId = Column('courtId', Integer, ForeignKey('Courts.id'))

    court = relationship(Court, foreign_keys=[courtId], backref="games")

    revision = Column(Integer, nullable=False)

    __mapper_args__ = {

        'version_id_col': revision

    }

I've found that this error occurs when another process has updated the same row, which makes sense, since that is the purpose of the version_id_col. 

In the contrived example above, it's been difficult to reproduce the error. In production, I have found this happens consistently, so long as another process has updated the same Game row. 

I can easily wrap a "try/except" around where "court" is accessed from "game", but I'm curious as to why an UPDATE statement happens there in the first place.

Below is the stacktrace:

the stack trace shows you what's going on.    the key thing to look for is the word "autoflush".   Any time you see an UPDATE happening when it seems like you are just accessing an attribute, look to autoflush for why that's happening.   Why does autoflush occur?  because the Session has pending data and it's being asked to run a query.  Why is it running a query when I'm only accessing an attribute?   it would be because that attrbute is unloaded. Why would an attribute be unloaded?   Either because it was expired, or just never loaded in the first place.  In this case I can see in your stack trace the use of _emit_lazyload which means that game.court is a many-to-one relationship that wasn't  loaded, so when you access it, it loads it, does autoflush, then detects that the row is stale.

Mitigation is to turn off autoflush in this block, described at https://docs.sqlalchemy.org/en/13/orm/session_basics.html#flushing

  with session.no_autoflush:

     court = game.court

--

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/c25108de-14e1-400b-a3d0-439fda06ed17%40googlegroups.com.

[Vinit Shah]

Thanks for the quick reply. That makes sense.
I ended up going with doing a series of joins and 'contains_eager' calls for all relationship attributes I knew I'd need to access.