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 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 --
