> > Ok, just to double check, that is for all DB back-ends or just for SQLite? > > This is true for every backend.
> > > Ah, OK. So it would be easier to write code that uses any DB except > for SQLite... Hmmm > It's not a question of "easier" or "harder" : as always it's just choosing the right tools for the job :D > > Ok, so I think we've established that is a special limitation for SQLite. > For all the other DBs, I can have my Web2py app inserting new records > willy nilly and the background process updating them (mail sent, mail > failed, whatever) without the Web2py app having performance/response > time issues due to database contention. > > 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? > Yep, it's from when you do the first thing that changes the data in the database (update, delete) and the commit. > > 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? > In fact, the example in the book shows an outer loop that looks for > new work, and when done, sleeps for 60 seconds. If the commit call > starts a new transaction, the on the very last of the 500 records, > we'll fall out of the inner loop and then sleep for 60 seconds, which > should lock Web2py out for a full minute? how does the server process > drop the transaction in that case so that the Web2py process has a > chance to get in to the DB? > Web2py is not blocked, and again, it's only with SQLite that there is the problem. From the first update on the queue to the commit, no-one can access a SQLite database, hence an app trying to read from that will "hang". Anyway, in the "sleeping" loop, no modifications to the db are made so also with SQLite there will be no problems. > > (I'm beginning to think that The Book should just say: This example is > for any DB except SQLite, don't use SQLite for this kind of processing > ???) > Yep, I'll be fine with it ;-D Maybe I should make a slice on how to fix this behaviour also for SQLite..... it's just a bit complicated because it requires to rebuild the sqlite module with an updated library.
