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.