> > > I don't think web2py is doing any magic -- I think it just relies on the > > standard database and database driver behavior. > > "There in lies the rub" - the problem is that The Book isn't really > very easy to use to find out just what the DAL does for each DB and > what "shows through" as the behaviour of the underlying DB. >
In this regard, I don't think the DAL does anything in particular for each DB -- the standard DB behavior is what "shows through". I suspect most RDBMS'es are similar with respect to these issues -- it is just SQLite that behaves differently because of its architecture. > That is, The Book isn't really a reference manual, which is fine, but > it also leaves me wondering if there is something the DAL does but > that I just haven't found/remembered the right part of the The Book to > find it. > Well, the book can't be a complete reference manual for each supported database backend. However, the book does make some effort to identify peculiarities of some of the backends. If you search for "SQLite" in the DAL chapter, there are multiple references to some of its oddities (including that the file is locked on each access). There are also multiple references to the GAE datastore, as well as a section of "gotchas<http://web2py.com/books/default/chapter/29/6#Gotchas>". Anyway, I suppose it would be nice to include a little more detail about locking in SQLite in particular. I guess the issue here is that I don't really want to tie my code (or > the example in The Book) to any one DB, but there are DB "quirks" > which manage to show through, and as a new Web2py user, its hard to > build up a model of what the DAL does and what leaks through. :-) > I'm not sure it's feasible or desirable for the DAL to try to normalize every last behavior across all database backends. What exactly would you have the DAL do differently in this case? > For example, the content at the link you provided ends with "7.0 > Transaction Control At The SQL Level" and talks about autocommit mode > vs. "out of autocommit mode". I searched The Book for "autocommit" and > found nothing. I also searched for SQLite and didn't find anything > mentioning that (that I was able to recognize). > web2py is just using the Python standard library sqlite3 module. According to the docs<http://docs.python.org/library/sqlite3.html#sqlite3-controlling-transactions>, it looks like it does not default to autocommit mode. > It matters because with autocommit off, SELECTs will acquire SHARED lock. > If I am reading things correctly, that might cause this server script > to block any writes/updates from the Web2py app. > As I read it, when an insert/update/delete request is made, SQLite prevents any new read locks (by initiating a PENDING lock), waits for the current reads to complete, and then acquires the necessary lock to do the insert/update/delete. Specifically, if the server does not find any records to update (i.e. > no work to do), the query/SELECT it does to find work items would > provoke a SHARED lock, but with no records to update the server won't > do a commit (or anything else that i can see) that would drop that > lock? > The shared lock should be dropped as soon as the select (i.e., the read) has completed. At some point, it might be worth doing some testing to see if you get the behavior you want. If you do db.commit() as in the example, I suspect it will be fine (assuming traffic levels aren't too high). Anthony
