Issue with Flask uwsgi nginx deployment?

Question

Python: 3.5.2pyodbc: 4.0.28OS: Ubuntu 18.04DB: Microsoft SQL Serverdriver: ODBC Driver 17 for SQL ServerWSGI: uwsgi & nginx Issue:Hitting error SQL Server Failed to resume transaction, for Flask web app, production server. Description:I’m working on a Python Flask web application which runs on Ubuntu and MS SQL Server, for which i’m using pyodbc and SQLAlchemy for database connection and request handling. The code has a lot of database transaction, mostly SELECT, UPDATE, INSERT. The app seems to run fine when i run it on uwsgi command without any issues. But, when i run on uwsgi and nginx configured web server, I’ve been constantly hitting the error, (pyodbc.ProgrammingError) (‘42000’, '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server Failed to resume transaction.] Database config: # Database Configurations # engine = create_engine("mssql+pyodbc://sa:********@0.0.0.0/WDM?driver=ODBC+Driver+17+for+SQL+Server", echo=False, isolation_level="SERIALIZABLE", strategy='threadlocal') Base = declarative_base(bind=engine) Session = sessionmaker(bind=engine, autocommit=True, expire_on_commit=True, autoflush=True) sc_ss = scoped_session(Session) db_session = Session() connection = db_session.bind Get data: with db_session.bind as c9: states = pd.read_sql("select distinct a.StateID, a.statename from DeskStateMaster a, DeskUnitRegistration b " "where a.StateID = b.StateID", c9) states = states.to_dict('records') Flask route sample: # Set Master Values # @app.route('/set_master_data', methods=["GET","POST"]) def set_master_data(): logged_user = str(session["username"]).capitalize() with db_session.bind as c9: master_data = pd.read_sql("select ID,ParameterName,ParameterLowValue,ParameterHighValue from DeskMasterData", c9) if request.method == "POST": # master_data = pd.read_sql("select ID,ParameterName,ParameterLowValue,ParameterHighValue from TestMasterData", engine) updates = [] for key,value in master_data["ParameterName"].iteritems(): updates.append({ "ParameterName": value, "ParameterLowValue": request.form.get(str(value + " " + "Low")), "ParameterHighValue": request.form.get(str(value + " " + "High")) }) df = pd.DataFrame(updates) for i in range(0, df.__len__()): with db_session.bind as c9: c9.execute("update DeskMasterData set ParameterLowValue={low}, ParameterHighValue={high} " "where ParameterName = '{parameter}' ".format( parameter=df.iloc[i].ParameterName, low=float(df.iloc[i].ParameterLowValue), high=float(df.iloc[i].ParameterHighValue) )) if updates: message = "Master Values Updated" else: message = "" master_data = pd.DataFrame() return render_template('set_master_data.html', masterValues = master_data, message=message, logged_user=logged_user) return render_template('set_master_data.html', masterValues = master_data, message="", logged_user=logged_user) Please suggest how to find the root cause and resolve this issue. Appreciate your help. Thank you.

KFSys · Answer

The error message “Failed to resume transaction” typically means that there is an open transaction which the system is trying to resume but cannot. This could be caused by several factors. Here are a few things you might want to check or change:

Connection Pooling: By default, SQLAlchemy manages its own connection pool which allows for reuse of database connections. However, if you are using multiprocessing or multithreading, such as with uWSGI, and these threads are not properly managed, you might have a situation where two or more threads are trying to use the same connection concurrently. This can cause issues like the one you are experiencing. A potential solution would be to disable the SQLAlchemy connection pool and let pyODBC manage the connections. This could be done when creating the engine:

engine = create_engine("mssql+pyodbc://sa:********@0.0.0.0/WDM?driver=ODBC+Driver+17+for+SQL+Server",
                       echo=False, isolation_level="SERIALIZABLE", strategy='threadlocal', poolclass=NullPool)

Explicit Transactions: Instead of allowing SQLAlchemy to manage transactions implicitly, you can manage them explicitly using a with statement. This can ensure that transactions are properly closed even if an error occurs.

Here’s an example of how to manage transactions explicitly:

with db_session.begin():
    # your database operations

Handling of Sessions: Using the session as a global object db_session = Session() can lead to problems in a multithreaded environment. Instead, you should instantiate a new session for each request, and close it when you are done. One way to do this is to use a Flask context processor or a Flask request teardown function.

Here’s an example using a context processor:

@app.context_processor
def make_session():
    session = Session()
    try:
        yield session
    finally:
        session.close()

1. Handling of Connections: Your code is directly binding sessions and using with db_session.bind as c9:. SQLAlchemy’s ORM and Session are designed to manage the lifecycle of the connection. It’s better to use them as designed and not try to manually manage connections unless necessary.

If the problem still persists, you should look into the SQL Server logs for more details about what might be causing this error. The logs should be available in the SQL Server Management Studio under the “Management” node.

Furthermore, you could also use SQL Server Profiler or Extended Events to trace the SQL commands being executed, which can give you an insight into where the transaction is left open. But please note that using these tools should be done carefully and with consideration for performance implications.

Report this

Issue with Flask uwsgi nginx deployment?

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today