Database deadlocks when using SQL Server

[Boerma, Remco]

Hi all,

 

Just wanted to let you know that when using SQL Server as a backend using pyodbc you might end up with deadlocks in the database. At least when using your server as a CAS provider. Using mysql doesn’t give this problem, other platforms I haven’t tried.

 

The solution is simple. Use  driver_args = dict(autocommit=True) in the DAL constructor.

 

Explanation:

Autocommit is de default connection on MS SQL server (they name it implicit transactions=0 when you check the trace of a session). PyODBC on the other hand wants to be compatible with the Python DB Api (1) to switches to the implicit transaction. And that can cause a major problem when you get only a tiny bit more than a little load. I’ve used the solution as a CAS server for our departmental wiki with only 25 users and no problem occurred. But when we used the CAS for roughly 2000 people I got hammered by the deadlock. So when no implicit transactions were used there were no locks that were opened for a while and hence, no deadlock appears.

 

Extra help while testing:

Another one I want to give to you all is that the internal web2py testing browser is very basic. And it will perform in a lot of cases. But in my case it didn’t because of the CAS (redirecting back and forth resulted in broken sessions). So I started to use selenium (2) to automate a few Chrome browsers to login an logout of the system. This way I was able to stress test the machine a little with a script of only a few lines of code (3).

 

Thanks to all who have contributed to the web2py framework, it’s awesome!

 

With kind regards,

Remco Boerma

 

(1): http://code.google.com/p/pyodbc/wiki/FAQs#Connecting_fails_with_an_error_about_SQL_ATTR_AUTOCOMMIT
ODBC drivers always start with autocommit turned on, but the Python DB API specification requires it to be turned off. After a connection is made, pyodbc first attempts to turn off autocommit before returning the connection. If the driver does not support transactions, such as the Microsoft Excel driver, you will see an error like the following:

If you set the autocommit keyword to True, pyodbc will not attempt any changes to the connection since it will already have autocommit turned on:

 

(2):

http://seleniumhq.org/

Selenium automates browsers. That's it. What you do with that power is entirely up to you. Primarily it is for automating web applications for testing purposes, but is certainly not limited to just that. Boring web-based administration tasks can (and should!) also be automated as well.

 

Selenium has the support of some of the largest browser vendors who have taken (or are taking) steps to make Selenium a native part of their browser. It is also the core technology in countless other browser automation tools, APIs and frameworks.

 

(3):

from selenium import webdriver

from selenium.common.exceptions import TimeoutException

from selenium.webdriver.support.ui import WebDriverWait # available since 2.4.0

import time

webdriver.chrome.driver = 'f:/seleniumdrivers/chromedriver.exe'

webdriver.chrome.binary = r'C:\portableapps\GoogleChromePortable\App\Chrome-bin\chrome.exe'

 

driver = webdriver.Chrome()

for x in range(100):

    print '---------------- run',x

    # open with fake login

    driver.get("https://myserver/casconsumer/user/login?_next=/casconsumer")

    driver.get("https://myserver/casconsumer/user/logout?_next=/casconsumer")

 

driver.quit()

 

 

Remco Boerma - Database Administrator & Software Developer

ROC Drenthecollege - www.drenthecollege.nl - 088 188 4693

r.boe...@drenthecollege.nl - http://twitter.com/RemcoBoerma

 

------------------------------->

Vrijwaring/Disclaimer
De gegevens in dit elektronisch document en de eventuele bijlagen zijn uitsluitend bedoeld voor de geadresseerde(n).
De informatie wordt zorgvuldig samengesteld.
Het Drenthe College kan echter niet garanderen dat de informatie absoluut juist, volledig en tijdig overkomt via het Internet.
Voor belangrijke beslissingen aangaande de verstrekte informatie dient u een en ander rechtstreeks bij het Drenthe College te controleren.

Website van het Drenthe College:
www.drenthecollege.nl

[Niphlod]

do everything without transaction (with autocommit turned off) and you'll end up probably with an inconsistent database if there is some business logic involved. If this is only for raw "read" speed it may be safer, but I wouldn't post this as something recommended (i.e. a controller that does +1 on a "page visits" columns updates the visits even if the view returns an HTTP 500 (e.g. some errors in the template)).

the presentation of the problem is a little wrong: SQL Server (as nearly all other databases) has no notion of "autocommit". You can use transactions with BEGIN TRANSACTION, COMMIT, ROLLBACK or you don't.
ODBC - used often to interact with SQL Server - is a different beast: a different protocol, a different agenda, etc etc etc. No transaction specs on ODBC, because some of the drivers ODBC can handle does not know what a transaction is.

[Boerma, Remco]

Hi Hiphlod,

 

Thanks for  your response. I wasn’t saying “do everything without transactions”, because you can explicitly start a transaction if I’m not mistaken. But for the built in CAS functionality it doesn’t work when using implicit transactions. If you explicitly use a transaction and therefor commit as soon as possible it will quicken the transaction, but that is not the way the CAS server and the Authentication part of web2py seems to be built.

 

On the wrongness of my presentation of the problem I would gladly ask you to visit http://msdn.microsoft.com/en-us/library/ms187878%28v=SQL.100%29.aspx in which is stated “Autocommit mode is the default transaction management mode of the SQL Server Database Engine.”

 

My main intent was for others who might bump into the problem I encountered to find a solution and save them hours of research.

 

With kind regards,

 

Remco Boerma - Database Administrator & Technisch Applicatie Beheer

ROC Drenthecollege - www.drenthecollege.nl - 088 188 4693

r.bo...@drenthecollege.nl - http://twitter.com/RemcoBoerma

--
 
 
 

[Niphlod]

Thanks for  your response. I wasn’t saying “do everything without transactions”, because you can explicitly start a transaction if I’m not mistaken. But for the built in CAS functionality it doesn’t work when using implicit transactions. If you explicitly use a transaction and therefor commit as soon as possible it will quicken the transaction, but that is not the way the CAS server and the Authentication part of web2py seems to be built.

It does work. The fact that with transactions it can't keep up with the traffic your experience is another problem...CAS and Auth in web2py do what they do in Mysql, SQLite, etc and the same in MSSQL. What you call "implicit transaction" is just calling "BEGIN TRAN" before any operation on the cursor and "COMMIT" everytime someone calls connection.commit(): this is done transparently by the various drivers API to respect the DBAPI spec.
 

On the wrongness of my presentation of the problem I would gladly ask you to visit http://msdn.microsoft.com/en-us/library/ms187878%28v=SQL.100%29.aspx in which is stated “Autocommit mode is the default transaction management mode of the SQL Server Database Engine.”

This only means that if you open a connection and do "update table set column = 0" the operation is carried on without being "forced" to call a COMMIT. MySQL and Postgresql works in this way, too. That being said, you'd normally want to use transactions issuing BEGIN, COMMIT, ROLLBACK because they are the safest way to use a relational db.
 

My main intent was for others who might bump into the problem I encountered to find a solution and save them hours of research.

No probl with that, but a "solution" that disables transaction support is a heavy price nonetheless.