Using SQLAlchemy sessions with flask and concurrency issues
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.
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.