Using SQLAlchemy sessions with flask & concurrency problems

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.

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.