Using SQLAlchemy sessions with flask and concurrency issues


Kirill

I'm developing an API with Flask and SQLAlchemy and here's what I want to do:

I have a client application that works on multiple tablets and has to send several requests to add content to the server. But I don't want to use automatic rollback at the end of every API request (default behavior of flask-sqlalchemy), because the sending of data is done over multiple requests, like in this very simplified example:

1. beginTransaction /? id = transactionId - > open a new session for the client making that request. SessionManager.new_session() in the following code .

2. addObject /? id=objectAid - > add an object to the PostGreSQL database and refresh

3. addObject /? id=objectBid - > add an object to the PostGreSQL database and refresh

4. commitTransaction /? id = transactionId - > Commits what happened since beginTransaction. SessionManager.commit() in the following code .

The point here is that if the client app crashes/loses the connection before sending the "commitTransaction", the data is not added to the server, preventing incomplete data on the server.

Since I don't want to use automatic rollback, I can't really use flask-SQLAlchemy, so I'm implementing SQLAlchemy myself into my flask app, but I'm not sure how to use sessions.

Here is my implementation in __init__.py:

db = create_engine('postgresql+psycopg2://admin:pwd@localhost/postgresqlddb',
                   pool_reset_on_return=False,
                   echo=True, pool_size=20, max_overflow=5)

Base = declarative_base()
metadata = Base.metadata
metadata.bind = db

# create a configured "Session" class
Session = scoped_session(sessionmaker(bind=db, autoflush=False))



class SessionManager(object):

    currentSession = Session()

    @staticmethod
    def new_session():
    #if a session is already opened by the client, close it
    #create a new session
        try:
            SessionManager.currentSession.rollback()
            SessionManager.currentSession.close()
        except Exception, e:
            print(e)

        SessionManager.currentSession = Session()
        return SessionManager.currentSession

    @staticmethod
    def flush():
        try:
            SessionManager.currentSession.flush()
            return True
        except Exception, e:
            print(e)
            SessionManager.currentSession.rollback()
            return False

    @staticmethod
    def commit():
    #commit and close the session
    #create a new session in case the client makes a single request without using beginTransaction/
        try:
            SessionManager.currentSession.commit()
            SessionManager.currentSession.close()
            SessionManager.currentSession = Session()
            return True
        except Exception, e:
            print(e)
            SessionManager.currentSession.rollback()
            SessionManager.currentSession.close()
            SessionManager.currentSession = Session()
            return False

But now, when multiple clients make requests, the API doesn't work, it seems that each client is sharing the same session.

How should I implement sessions so that each client has a different session and can make requests at the same time?

Thank you.

Tomyski

You seem to want multiple HTTP requests to share a transaction. This is impossible - incompatible with the stateless nature of HTTP.

For example, consider a client that will open a transaction and cannot close it because it lost its connection. The server has no way of knowing it, and will keep that transaction open forever, possibly blocking other clients.

For example, for performance reasons, it is reasonable to use transactions to bundle database requests when there are multiple write operations. Or used to keep the database consistent. However, it must always be committed or rolled back on the same HTTP request that was opened.

Related


Basic Flask SQLAlchemy context issues

Johnny John Boy I've read many other articles that mention keeping the models separate from the main app, but I can't get it to work with just app.py (my actual app) and models.py (my database models). If I do the following, I get an app.db file with no tables

Basic Flask SQLAlchemy context issues

Johnny John Boy I've read many other articles that mention keeping the models separate from the main app, but I can't get it to work with just app.py (my actual app) and models.py (my database models). If I do the following, I get an app.db file with no tables

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

Concurrency issues when using ConcurrentHashMap

user4759317: I've been working on a REST API as part of some tricks. The current implementation has a small concurrency issue when inserting objects into the ConcurrentHashMap. My code checks to see if the consumed JSON contains an ID. If not, create a new uni

flask-sqlalchemy relationship between table issues

Hussein Alside I'm trying to build a dashboard for admins who can manage some settings about their organization. Below are 2 tables, User and Organization, when someone registers it should create a relationship between User and Organization, but after I try to

Netty: Concurrency issues using multiple event loops

Ohas I have a client connecting to n different servers. So, I am creating n different channels. Because I have more than 5000 servers. I used 10 event loops and only one event loop group. Also, each channel has a separate pipe. I already know there won't be an

Using Flask SQLAlchemy models with regular SQLAlchemy

mr pilot I have an existing model written in Flask-SQLAlchemy that cannot be changed. I'm writing another app that uses the same model, but doesn't need Flask, so I'm using the regular SQLAlchemy module. Unfortunately I get a lot: 'AttributeError: Module 'DB'

Using Flask SQLAlchemy models with regular SQLAlchemy

mr pilot I have an existing model written in Flask-SQLAlchemy that cannot be changed. I'm writing another app that uses the same model, but doesn't need Flask, so I'm using the regular SQLAlchemy module. Unfortunately I get a lot: 'AttributeError: Module 'DB'

Using Flask SQLAlchemy models with regular SQLAlchemy

mr pilot I have an existing model written in Flask-SQLAlchemy that cannot be changed. I'm writing another app that uses the same model, but doesn't need Flask, so I'm using the regular SQLAlchemy module. Unfortunately, I get a lot: 'AttributeError: Module 'DB'

Using Mixer with Flask-SQLAlchemy

Rupaceko I'm trying to generate test data for a Flask + SQLAlchemy powered website using blender . I created a file called the following generate_test_data.pyin the root directory of the project : # -*- coding: utf-8 -*- import app from mixer.backend.flask im

Hibernate sessions and concurrency

Alexey Kamenskiy I'm pretty new to Hibernate and have a newbie question in my head with a clear answer that I can't find online. In my multithreaded application, I want to use Hibernate for data persistence. The application is event-based in nature, which mean