On Wed, Jun 20, 2012 at 5:53 PM, Anthony <abasta...@gmail.com> wrote: > Can you explain how whether SQLite autocommits or not matters in this case? > The DAL documentation recommends doing a db.commit() in scripts and > background tasks like this precisely because it doesn't want to assume any > autocommitting is happening. Within the context of HTTP requests, web2py > automatically does a commit at the end of the request. It's not clear the > documentation needs to say anything else here -- if you follow what the > documentation currently says, you should be fine.
>From http://www.sqlite.org/lockingv3.html, "7.0 Transaction Control At The SQL Level": "The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is optional) is used to take SQLite out of autocommit mode. Note that the BEGIN command does not acquire any locks on the database. After a BEGIN command, a SHARED lock will be acquired when the first SELECT statement is executed." So, if the DAL takes the DB out of autocommit mode, the "select" (which is done in the main/outer loop of the server script) will cause a SHARED lock to be taken, right? Then, let's say, there are no new records to process, so the server script goes to sleep and does another select/query on its next trip around the loop. So far, just fine, the server script doesn't block, right? However, if the Web2py application wants to update the DB, it can't, because the server script now has a potentially long-lived SHARED lock. Quoting from the same document, "3.0 Locking": "SHARED The database may be read but not written. Any number of processes can hold SHARED locks at the same time, hence there can be many simultaneous readers. But no other thread or process is allowed to write to the database file while one or more SHARED locks are active." Sooo.... Have I misunderstood the SQLite locking description? or somewhere in the the DAL documentation where it says it'll do a commit after a select or is it relying on (in the case of SQLIte) the autocommit to handle that for it? Thanks! --Doug
