Hello,
I’m using gunicorn <https://gunicorn.org/> in front of a Pyramid server,
and I changed the configuration from 1 to 4 workers. Now I occasionally see
the following exception:
InvalidRequestError: This Session's transaction has been rolled back due to
a previous exception during flush. To begin a new transaction with this
Session, first issue Session.rollback(). Original exception was: (raised as
a result of Query-invoked autoflush; consider using a session.no_autoflush
block if this flush is occurring prematurely)
(pymysql.err.OperationalError) (1213, 'Deadlock found when trying to get
lock; try restarting transaction')
[SQL: UPDATE … SET foo=%(bar)s WHERE …]
[parameters: {…}]
(Background on this error at: http://sqlalche.me/e/e3q8)
The server follows the SQLA cookie-cutter template
<https://github.com/pylons/pyramid-cookiecutter-starter>, and is running
with a SQLAlchemy Session factory
<https://docs.sqlalchemy.org/en/13/orm/session_api.html#session-and-sessionmaker>
whose autoflush
<https://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.autoflush>
still defaults to true. Curiously, it seems that only one particular
endpoint keeps triggering this problem, and the failing UPDATE attempts to
flip a boolean flag.
However, the exception gives rise to two questions:
1. *SQLAlchemy perspective.* The reason for using autoflush here is to
ensure that new ORM objects whose primary key is generated like so:
id = Column(UUID(), default=uuid.uuid4, primary_key=True)
have a valid id after they’ve been newly created. Would using a manual
dbsession.flush() be preferable with autoflush disabled? Are there better
recommended ways of handling this problem? (See this related question
<https://groups.google.com/forum/#!topic/sqlalchemy-alembic/Nzz5gTyrptE>
.)
2. *Pyramid perspective.* Every Request object has its own Session
object associated which commits when request handling is done. However, the
above exception in a sense *fails* the request handling. If I was to
follow the suggestion to “begin a new transaction with this Session” then
how would I do that? Does Pyramid provide existing support to handle such
issues, or does the exception indicate a problem elsewhere that requires
attention? What is best practice here?
In general, I think I’d like to understand the finer details of
SQLAlchemy’s autoflush and how that interplays with Pyramid’s requests and
their Sessions and transactions. I wonder, for example, if the above
indicates that a client has sent the same request more than once (Android’s
httplib seems to do that), and with more than one workers enabled this
manifests a race condition.
Much thanks!
Jens
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/pylons-discuss/e0201c58-f801-42c6-93e8-a3bb08e9a230%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
