On Sat, Jun 16, 2012 at 5:56 PM, Niphlod <niph...@gmail.com> wrote:
> The bit of magic that I forgot to tell explicitely is that db.commit()
> commits every opened transaction and starts a new one :D
Ok, just to double check, that is for all DB back-ends or just for SQLite?
> Note, if your site does mostly reading, than SQLite is fine with that.
> Additional note, if you're not using SQLite there are no "locks" at all.
Ah, OK. So it would be easier to write code that uses any DB except
for SQLite... Hmmm
> 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.
> So, if there is only one daemon running, it's safe to commit every 500 or
> every 1 with all the dbs: if you commit every row everytime you app will try
> to see the queue will see a "freshier" version of it, if you commit every
> 500 the app will see a "older" version of the queue.
> The caveat of locking is valid for sqlite only: if you commit every 500 the
> app won't be allowed to read or write to the db while processing 1 to 500.
> If you commit every 1, the app will "lock" less.
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?
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?
(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
???)
Thanks,
--Doug
--
