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 <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 [email protected] - http://twitter.com/RemcoBoerma Van: [email protected] [mailto:[email protected]] Namens Niphlod Verzonden: woensdag 28 november 2012 16:23 Aan: [email protected] Onderwerp: [web2py] Re: Database deadlocks when using SQL Server 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. On Wednesday, November 28, 2012 3:52:52 PM UTC+1, Remco Boerma wrote: 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 <http://code.google.com/p/pyodbc/wiki/FAQs#Connecting_fails_with_an_erro r_about_SQL_ATTR_AUTOCOMMIT> ODBC drivers always start with autocommit turned on, but the Python DB API specification <http://www.python.org/peps/pep-0249.html> 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/ <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 [email protected] <javascript:> - 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 -- -------------------------------> 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 --
