On Thu, Feb 21, 2019 at 7:40 AM Jan Sakalos <[email protected]> wrote:
>
> Hello,
>
> i would like to ask if is this correct approach in multi-threaded application:
>
> Base = declarative_base()
> engine = create_engine("sqlite:///datafiles/data.sqlite",
> connect_args={'check_same_thread': False})
> Base.metadata.create_all(engine)
>
> Session = scoped_session(sessionmaker(bind=engine))
> # Session = sessionmaker(bind=engine)
> ses = Session()
>
>
> class Table1(Base):
> __tablename__ = 'table1'
> id = Column(Integer, primary_key=True)
> name = Column(String)
>
>
> def print(self):
> print('\n',self.__class__)
> print('session factory:', Session)
> session = Session()
> print('thread local session:', session)
> session.query(Table1)
> print('sa_instance session:', self._sa_instance_state.session)
>
> # Base.metadata.drop_all(d.engine)
> Base.metadata.create_all(engine)
>
> t1 = ses.query(Table1).all()[0]
> t1.print()
> threading.Thread(target=t1.print).start()
>
>
> i see issue that queried Table1 instance t1 contains 'main thread'
> threadlocal session reference - t1._sa_instance_state.session.
>
> output:
>
> Main thread:
> <class '__main__.Table1'>
> session factory: <sqlalchemy.orm.scoping.scoped_session object at
> 0x00000000038275C0>
> thread local session: <sqlalchemy.orm.session.Session object at
> 0x0000000003827320>
> sa_instance session: <sqlalchemy.orm.session.Session object at
> 0x0000000003827320>
>
> Spawned thread:
> <class '__main__.Table1'>
> session factory: <sqlalchemy.orm.scoping.scoped_session object at
> 0x00000000038275C0>
> thread local session: <sqlalchemy.orm.session.Session object at
> 0x0000000003827438>
> sa_instance session: <sqlalchemy.orm.session.Session object at
> 0x0000000003827320>
>
> Process finished with exit code 0
>
> Beacuse of threads will spawn another threads and data are always handled by
> one thread - specific thread is initialized by specific table row and based
> on data will initialize rows in different table and spawn respective threads,
> it sounds to me as most suitable approach.
>
> So my question is - do you think this is acceptable design approach? If no do
> you have any suggestions?
> Will t1._sa_instance_state.session cause issues? If yes can be replaced by
> current threadlocal session? Or is there different technique how to pass ORM
> intances to threads?
The approach is "correct" but it's not very clear and looks easy to
break. I would keep the concern of spawning threads and implementing
thread worker logic *outside* of the scope of the object itself.
That is, when you spin up the thread outside, that "t1" object, inside
your method it's referred to as "self", should be *nowhere near* that
new thread. The new thread should have no way to access "self" or the
Session that is from the main thread, because the Session is not a
threadsafe object. The test you are illustrating is just printing
it, which is not a problem, but I would assume you seek to do a lot
more in this function. Even if you just try to print(self), that is
dangerous, if it attempts to load expired attributes in order to print
its state you are now running a query against the main thread's
session in your spawned thread.
The safest pattern for objects and threads is to make sure the object
that is being worked on in the thread is not associated with the main
thread's session in any way. This could mean detaching it and then
passing it, or it could mean passing only the primary key to the
worker thread where the worker thread loads a new instance with that
primary key from its local session. In a more complex case you might
want to create a copy of the object and its current state, which the
Session.merge() method can achieve, but this is also a bit risky to
use across threads without being careful as it needs to read from the
original object's state; it does this without emitting loader
callables but still can be subject to changes in state from the
original object.
basically the safest way is to load all new objects in worker threads
with an all new session, and don't touch the main thread's session or
objects at all, unless you need to do something more intricate or
you're attempting to reduce the number of database round trips (where
we'd look into copying), in which case feel free to share here.
def worker(session, obj):
obj = Session.query(Thing).get(key)
# work with object
obj = session.query(Thing).first()
threading.Thread(target=worker, obj=
>
> Thanks
> Jan
>
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
