I have recently upgraded our web2py instance on production to v.1.95.1 from
very old version (1.81.5 or something). After a few hours of work it started
throwing the following error on every request that does db IO:
('HY000', '[HY000] [Microsoft][ODBC SQL Server Driver]Connection is busy
with results for another hstmt (0) (SQLExecDirectW)')
The instance of web2py runs on windows server and connects to SQL Server
2005. I googled that error message and found out that it happens if you try
to use ODBC cursor for multiple requests at the same time. I "fixed" the
problem by enabling MARS (Multiple Active Result Sets) in the connection
string, which makes it possible to use the cursor for multiple simultaneous
requests. However, I think there is an underlying problem somewhere in
web2py framework that needs to be fixed.
I run web2py DAL in "transactionless" mode (I have the following in my
model: db = DAL(connection_string, driver_args={'autocommit': True})). In
the past with the old version of web2py I had autocommit = False and it was
causing a different problem: every once in a while it would stop responding
completely and I would see in SQL Server Activity Monitor some hanging
request within an uncommitted transaction that would block all other
requests. I suspect those two symptoms are the manifistatins of the same
underlying problem... Looks like under some circumstances DAL connection
gets back to the pool without properly finishing previous operations, which
leads to uncommited transactions (if ODBC autocommit = False) or multiple
simultaneous requests per ODBC cursor (if ODBC autocommit = True).
Not sure if all the above makes sense, but please let me know if you have
any ideas on this matter.
