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
>
> 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
>
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/c25108de-14e1-400b-a3d0-439fda06ed17%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/c25108de-14e1-400b-a3d0-439fda06ed17%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/9b4d8497-a875-433f-b7f4-a5d8e0eda867%40www.fastmail.com.
