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 pylons-discuss+unsubscr...@googlegroups.com. To post to this group, send email to pylons-discuss@googlegroups.com. 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.