Re: [sqlalchemy] scoped_session, sessions and thread safety

Mike Bayer Wed, 30 Mar 2016 10:00:43 -0700


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 objectand it does not use any mutexing when it manipulates this internalstate. If you run multiple threads on it without applying mutexing toall Session operations (which includes all queries and all attributeaccess on all objects, since these trigger lazy loads and flushes), thisstate will be corrupted. At the very least you will get lots ofwarnings and errors since the Session does check for a few concurrencysituations that can occur even without multiple threads in use, seeexample below.

Additionally, the database transaction itself can only do one operationat a time, so the DBAPI connection also has to mutex appropriately inorder to ensure SQL operations run correctly; if the backend databaserequires 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 currentlysupported within the execution stage of the flush process. Results maynot be consistent. Consider using alternative event listeners orconnection-level operations instead.SAWarning: Usage of the 'Session.add_all()' operation is not currentlysupported within the execution stage of the flush process. Results maynot be consistent. Consider using alternative event listeners orconnection-level operations instead.

Exception!  Session is already flushing

Exception!  Session is already flushing

Exception!  Session is already flushing

Exception!  Session is already flushing

Exception!  Session is already flushing

Exception!  Session is already flushing

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

Re: [sqlalchemy] scoped_session, sessions and thread safety

Reply via email to