> > > Take it like that. Transactions are a way to provide consistent data to > > every reader/writer connected to a database. > > Right, I've got that part down, it is the "what is the magic that > Web2py is doing behind my back" part that I was (and still a little > bit) unclear on. >
I don't think web2py is doing any magic -- I think it just relies on the standard database and database driver behavior. For example, here's the documentation on the Python SQLite driver: http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions. Once a connection is made, all insert/update/delete statements become part of a transaction. The transaction will auto-commit before any non-DML, non-select statement, or you can manually commit at any time. If you manually commit, then any subsequent insert/update/delete statements will once again become part of a new transaction, until that transaction is committed. During an HTTP request, web2py automatically does a commit at the end of the request (just before returning the response to the client). However, if you are using the DAL outside of an HTTP request (e.g., in a background process, as in this example), then you must manually commit for your changes to take effect. The trick comes in when SQLite is being used. > Just for completeness, if I do a commit on every loop, what is the > window where Web2py is causing the SQLite lock to be taken? > Is it just between the update_record and the commit? > I believe so. > I ask because I'm not yet clear on how it is that the commit on every > loop will allow the Web2py app to be more responsive than the commit > after all 500 cases. It must be that in the commit on every loop there > is a substantial chance for the Web2py app to get to the database. But if a commit causes a new transaction to start, how does Web2py get in? > A commit closes the open transaction, but it doesn't cause the db to lock again immediately. A new transaction doesn't really start until another insert/update/delete statement is issued. For more on SQLite locking, see http://www.sqlite.org/lockingv3.html. Anthony
