Note also the objects can be copied using copy.deepcopy(), or pickle loads()/dumps(), before sending them to the other Session. This might not have worked in older Python / SQLAlchemy versions but deepcopy() seems to rely upon the pickle internals e.g. __setstate__, which in SQLAlchemy will correctly construct the copied instance not linked to any Session.
Here's a demo: import copy from sqlalchemy import Column from sqlalchemy import create_engine from sqlalchemy import ForeignKey from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import object_session from sqlalchemy.orm import relationship from sqlalchemy.orm import Session Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) bs = relationship("B") class B(Base): __tablename__ = 'b' id = Column(Integer, primary_key=True) a_id = Column(ForeignKey("a.id")) data = Column(String) e = create_engine("sqlite://", echo=True) Base.metadata.create_all(e) s1 = Session(e, expire_on_commit=False) a1 = A(data='olddata', bs=[B(data='olddata')]) s1.add(a1) s1.commit() a2 = copy.deepcopy(a1) assert a1 in s1 assert a1.bs[0] in s1 assert a2 not in s1 assert a2.bs[0] not in s1 assert object_session(a2) is None assert object_session(a2.bs[0]) is None s2 = Session(e) s2.add(a2) a2.data = 'new data' a2.bs[0].data = 'new data' s2.commit() On Tue, Apr 16, 2019 at 9:41 AM Mike Bayer <mike...@zzzcomputing.com> wrote: > > On Tue, Apr 16, 2019 at 4:49 AM tonthon <tontho...@gmail.com> wrote: > > > > Celery tasks are using the same scoped_session factory, could it cause the > > errors we're facing here ? > > what's important is if these tasks run in the same process or not ? ( > i thought celery runs as a separate service?) If you are passing > ORM objects which are associated with a thread local Session in > process to a Celery task queue which is running distinct threads in > the same process, then yes that will exactly lead to this issue and > needs to be fixed. You can't share a persistent (meaning, associated > with a Session) ORM loaded object with another thread, because that > means you are sharing the whole Session across threads. The object > needs to be detached first (e.g. session.expunge(obj), or just > session.close()) , or the celery thread needs to get just the primary > keys and load the objects on its own. Another option is to merge() > the objects into the Session to be used by the celery worker but this > also has to be done carefully so that neither Session emits SQL on the > wrong thread. > > > > > > > > Le 15/04/2019 à 15:39, Mike Bayer a écrit : > > > > On Mon, Apr 15, 2019 at 5:41 AM tonthon <tontho...@gmail.com> wrote: > > > > I tried to set a lower value for the pool_recycle value and it seems to > > work. > > > > There is a celery service running in the background, maybe it could affect > > the session management. > > > > this will reduce the problem but the architectural issue that is > > causing it is likely still present. I'd want to look at how the > > interaction with Celery is occurring within the same process. > > > > > > Le 12/04/2019 à 15:58, Mike Bayer a écrit : > > > > it's likely that a database connection is being returned to the pool > > in an invalid state. > > > > Switching to NullPool temporarily might reveal that this solves all > > the issues ; at the very least, I would try setting pool_recycle to a > > low number, like 5 minutes, however this won't prevent the problem, > > just make it less likely. What you do need to find are stack traces > > that precede the error, to give a clue why a connection would be > > placed in the pool in a bad state. > > > > The other possibility is that your application is actually sharing a > > single connection across threads in some way which would be a > > different problem though with a lot of similar behaviors. Are there > > any global in-memory caches being used of objects where an ORM object > > might be shared out among threads, or a background worker thread of > > some kind, anything like that ? Does the application use a > > "scoped_session" pattern and maybe the session being passed around in > > some cases isn't actually scoped? > > > > > > On Fri, Apr 12, 2019 at 4:03 AM tonthon <tontho...@gmail.com> wrote: > > > > Le 10/04/2019 à 17:12, Mike Bayer a écrit : > > > > On Wed, Apr 10, 2019 at 9:23 AM tonthon <tontho...@gmail.com> wrote: > > > > Hi, > > > > We're using sqlalchemy in a Pyramid Web Application. > > > > We use the ZopeTransactionExtension and our session factory is initialized > > this way : > > > > DBSession = > > scoped_session(sessionmaker(extension=ZopeTransactionExtension())) > > > > We use the pyramid_tm that wraps each web requests in a transaction. > > > > Our services are served through apache and mod_wsgi (1 process, 10 threads). > > > > > > We recently faced the following error : > > > > 193.253.40.35 - 2019-04-08 09:29:23,054 ERROR Exception during reset or > > similar ([sqlalchemy.pool.QueuePool._finalize_fairy:721]) > > Traceback (most recent call last): > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", > > line 712, in _finalize_fairy > > fairy._reset(pool) > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/pool.py", > > line 881, in _reset > > self._reset_agent.rollback() > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > > line 1632, in rollback > > self._do_rollback() > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > > line 1670, in _do_rollback > > self.connection._rollback_impl() > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > > line 706, in _rollback_impl > > self._handle_dbapi_exception(e, None, None, None, None) > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > > line 1413, in _handle_dbapi_exception > > exc_info > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", > > line 265, in raise_from_cause > > reraise(type(exception), exception, tb=exc_tb, cause=cause) > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", > > line 704, in _rollback_impl > > self.engine.dialect.do_rollback(self.connection) > > File > > "/var/www/autonomie/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", > > line 1804, in do_rollback > > dbapi_connection.rollback() > > ProgrammingError: (_mysql_exceptions.ProgrammingError) (2014, "Commands out > > of sync; you can't run this command now") (Background on this error at: > > http://sqlalche.me/e/f405) > > > > > > I can't reproduce this problem that seems to happen randomly. > > > > Could anyone help me giving some points to investigate ? > > > > Is there some tuning to do (I already set the pool_recycle var) ? > > > > the most important thing is what driver are you using and what version? > > > > are you doing anything with SAVEPOINTs or greenlets / eventlet ? > > > > is the web application experiencing timeouts of some kind, such that > > transactions are being dropped ? > > > > is the above error only showing up in logs or is it occurring > > synchronously with a web request and causing the request to fail ? > > > > > > We use mysqlclient 1.4.1. > > > > We don't use any greenlet or eventlet nor savepoints. > > > > I havn't seen any timeout happening so far. > > > > The given error is happening during the request's lifecycle resulting in a > > HTTP 500 error code > > > > After the error, the user refreshes and it works. > > > > I don't know if it could be related, but we also see things like > > "NoSuchColumnError: "Could not locate column in row for column 'count(*)'". > > > > > > > > Thanks in advance > > > > Best regards > > > > Gaston > > > > -- > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > > > > > -- > > 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 post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. -- 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 post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.