Multi-threading use of SQLAlchemy Sessions

Viewed 19

I've been reading about the multi-threading use of the Sqlalchemy session, but still wonder if it is the best way to use one global session for multiple threads and allow only one thread to use it at once or to limit the number of sessions created and make a new session per thread and have like a limit on the number of sessions running together. In my project, I need to make several requests simultaneously, and while I wait for the response to those requests, the session is still active. Once the response is received, I add it to the PostgreSQL DB and close the session. Up to 6-7 requests per second are fine and 85 concurrent sessions seem to be enough, but when I increase it to 8-9 requests per second, due to larger response time, many sessions get stuck and don't close in time.

    def plan_request(self, url, time_num):
        logger.info(f'Sleeping for {time_num} seconds')
        session_manager = GetNewLocalSession()
        time.sleep(time_num)
        # after delay new session is created using session manager I made
        session_local = session_manager.get_new_local_session()
        self.tool.test(session_local, item)
        self.session_manager.remove_local_session(session_local)
        session_local.close()
        db.Session.remove()
class GetNewLocalSession():
    def __init__(self):
        self.sessions = []
    
    def get_new_local_session(self):
        print(f"Sessions used: {len(self.sessions)}")
        if len(self.sessions) <= 85:
            # creates new scoped_session object
            session_local = Session()
            self.sessions.append(session_local)
            return session_local
        else:
            success = False
            while not success:
                if len(self.sessions) <= 85:
                    session_local = Session()
                    self.sessions.append(session_local)
                    success = True
                else:
                    time.sleep(0.1)
            return session_local

    def remove_local_session(self, session_local):
        if session_local in self.sessions:
            self.sessions.remove(session_local)
        else:
            pass
0 Answers
Related