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