Thanks for the reply !
Ok, so if i understood what you said, the advice to use only one session
per thread is related to the fact that the session's internals are not
thread safe. It has nothing to do with the backend database's concurrency
constraint.
Some more questions:
1) One limitation that i find to the scoped_session is that you're limited
to exactly one session by thread. For example if i have a script update.py
that is scheduled in a crontab, and that does some work in the database
(multiple commits). Suppose I want to make sure that only one script
update.py runs at a time. What i would want to do is start a transaction a
the beginning of update.py and lock a row in some table. When the script is
over i would commit. This way, if an other update.py starts it will fail
because it cannot get the lock.
The problem is that if i use a scoped sessions, I cannot have independant
transactions (any commit in update.py will unlock the row). Can a scoped
session work in a case like this ?
2) when i use a session, it's usually within a context manager:
with session_ctx_manager() as s:
do_some_work(s)
where session_ctx_manager is smth like this:
@contextmanager
def session_ctx_manager():
s = Session()
try:
yield s
finally:
s.remove()
Is it a bad idea to design an application where I make sure not to
spawn/switch thread when i'm inside the session_ctx_manager ?
This would allow me to solve problem #1 because I can create as many
independant sessions as i want within the same thread and commit them when
i want to.
Le mercredi 30 mars 2016 19:00:16 UTC+2, Mike Bayer a écrit :
>
>
>
> On 03/30/2016 12:37 PM, Mehdi GMIRA wrote:
> > I've read a lot of stuff on scoped_session, thread safety, and sessions,
> > and i just don't get it.
> > For me, a session is just a "wrapper" around the actual database behind
> > it. And databases do not like concurrent updates of the same row within
> > multiple transactions.
> > So, it is my understanding that you should never try to change the same
> > row within multiple threads.
> > And this is a limitation that is more strict than just having thread
> > safe sessions.
> > For example, i think that something like this is unsafe, even though the
> > session used is thread safe (because item n°3 is present in two
> threads):
> >
> > |
> > importthreading
> >
> >
> > defworker1():
> > items =scoped_session.query(Item).filter(Item.id.in_([1,2,3]))
> > # do some stuff with items
> >
> >
> > defworker2():
> > items =scoped_session.query(Item).filter(Train.id.in_([3,4,5]))
> > # do some stuff with items
> >
> >
> > threads =[]
> >
> >
> > t1 =threading.Thread(target=worker1)
> > t2 =threading.Thread(target=worker2)
> > t1.start()
> > t2.start()
> > |
> >
> > So, i don't see the point of keeping one session per thread. There is no
> > harm in sharing the session, as long as the rows behind it are not used
> > by multiple threads.
> >
> > I'm a little bit confused and some explanations/examples would be much
> > appreciated :)
>
> The Session object itself is not thread safe. It is a stateful object
> and it does not use any mutexing when it manipulates this internal
> state. If you run multiple threads on it without applying mutexing to
> all Session operations (which includes all queries and all attribute
> access on all objects, since these trigger lazy loads and flushes), this
> state will be corrupted. At the very least you will get lots of
> warnings and errors since the Session does check for a few concurrency
> situations that can occur even without multiple threads in use, see
> example below.
>
> Additionally, the database transaction itself can only do one operation
> at a time, so the DBAPI connection also has to mutex appropriately in
> order to ensure SQL operations run correctly; if the backend database
> requires a separate "fetch the last inserted ID" step for an INSERT,
> this can also be corrupted by concurrent threads.
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
>
> e = create_engine("postgresql://scott:tiger@localhost/test")
> Base.metadata.drop_all(e)
> Base.metadata.create_all(e)
>
> s = Session(e)
>
> import threading
> import time
> import random
> import warnings
>
> warnings.simplefilter("always")
>
> def go():
> while True:
> time.sleep(1.5 * random.random())
> try:
> s.add_all([A() for j in range(5)])
> s.flush()
> except Exception as e:
> print "Exception! %s" % e
>
> workers = [threading.Thread(target=go) for i in range(10)]
> for worker in workers:
> worker.start()
>
> for worker in workers:
> worker.join()
>
>
>
> output:
>
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> SAWarning: Usage of the 'Session.add_all()' operation is not currently
> supported within the execution stage of the flush process. Results may
> not be consistent. Consider using alternative event listeners or
> connection-level operations instead.
> Exception! Session is already flushing
> Exception! Session is already flushing
>
>
>
>
>
>
>
> >
> > --
> > 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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > Visit this group at https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
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.
