Using SQLAlchemy sessions with flask & concurrency problems

Question

I'm working on an API with Flask and SQLAlchemy, and here's what I would like to do :

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

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

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

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

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

The point here is to not add the data to the server if the client app crashed / lost his connection before the « commitTransaction » was sent, thus preventing from having incomplete data on the server.

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

Here's the implementation I did in the __ 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 the API doesn’t work when several clients make a request, it seems like every client share the same session.

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

Thank you.

Any reason to not use the config option SQLALCHEMY_COMMIT_ON_TEARDOWN = False? — Sean Vieira
– Sean Vieira, Commented Apr 15, 2015 at 12:05
@SeanVieira According to this page, line 791, SQLALCHEMY_COMMIT_ON_TEARDOWN is already False by default. And this would not solve the problem of having a rollback at the end of each request with flask-sqlalchemy. — Kyrill
– Kyrill, Commented Apr 17, 2015 at 9:02

Tometzky · Accepted Answer · 2015-04-15 19:50:53Z

2

You seem to want several HTTP requests to share one transaction. It's impossible - incompatible with stateless nature of HTTP.

Please consider for example that one client would open transaction and fail to close it because it has lost connectivity. A server has no way of knowing it and would leave this transaction open forever, possibly blocking other clients.

Using transactions to bundle database request is reasonable for example for performance reasons when there's more than one write operation. Or for keeping database consistent. But it always has to be committed or rolled back on the same HTTP request it was open.

answered Apr 15, 2015 at 19:50

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Biswajit Kaushik · Accepted Answer · 2024-06-29 07:42:25Z

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

You don't need different session.

Note that Flask application by default is not concurrent, which means that it serves one request at a time. These requests are handled on the main thread one by one.

But now the API doesn’t work when several clients make a request, it seems like every client share the same session.

This is because of scoped_session, which guarantees that you get the same session object on every call within the same thread. This is not a problem because the requests are handled sequentially one by one.

Also you need to ensure that you commit your transactions after each request so that in each request your session starts afresh. Below is the snippet from the Flask documentation itself:

from yourapplication.database import db_session

@app.teardown_appcontext
def shutdown_session(exception=None):
  db_session.remove()

Cross · Accepted Answer · 2015-09-18 22:01:21Z

I know this is an old thread, but you can achieve this with djondb (NoSQL database),

With djondb you can create transactions and if something goes wrong, i.e. you lost the connection, it does not matter, the transaction could be there forever without affecting the performance, or creating locks, djondb has been made to support long-term transactions, so you can open the transaction, use it, commit it, roll it back or just discard it (close the connection and forget it was there) and it won't leave the database in any inconsistent state.

I know this may sounds weird for Relational guys, but that's the beauty of NoSQL it creates new paradigms supporting what SQL guys say it's impossible.

Hope this helps,

Collectives™ on Stack Overflow

Using SQLAlchemy sessions with flask & concurrency problems

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related