By vemanaren
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.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
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
withstatement. 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()
-
- 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.
- Handling of Connections: Your code is directly binding sessions and using
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.
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.