Python: 3.5.2
pyodbc: 4.0.28
OS: Ubuntu 18.04
DB: Microsoft SQL Server
driver: ODBC Driver 17 for SQL Server
WSGI: 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.

Submit an answer

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!