From: John Carter <john.carter@...>
Date: 2008-09-25T10:06:10+09:00
Subject: Sqlite, multiple processes and blocking.

So I have two very independent (linux) processes a producer and
consumer.

I want the producer and the consumer to be able to run (or not) run
irrespective of whether the other is up or not.

The producer produces events and inserts them into a sqlite table.

The consumer picks them up and does many different somethings that
each take several hours of wall clock time.

If I decide to restart, or the process for some reason crash, or the
box reboots I definitely don't want to lose any events or the progress
I have made handling them.

Sqlite3, according to the FAQ handles to multiple processes, as far as
I can see, pretty much automagically out of sight below the SQL level.

Here is the relevant bit of the FAQ...

    SQLite allows multiple processes to have the database file open at
    once, and for multiple processes to read the database at once. When
    any process wants to write, it must lock the entire database file
    for the duration of its update. But that normally only takes a few
    milliseconds. Other processes just wait on the writer to finish
    then continue about their business. Other embedded SQL database
    engines typically only allow a single process to connect to the
    database at once.

Grreat! Looks like it should behave as I want... if the producer is
writing, the consumer just blocks and vice versa.

And then it gets me worried with...

    When SQLite tries to access a file that is locked by another
    process, the default behavior is to return SQLITE_BUSY. You can
    adjust this behavior from C code using the sqlite3_busy_handler()
    or sqlite3_busy_timeout() API functions.

Which suggests it doesn't block, it fails (presumably requiring you to
poll until it succeeds). Aargh! That's messy!

Notifications and triggers?
===========================

My other problem is... so the consumer has finally, many hours later,
consumed all that is available to consume, I now need it to go to
sleep (possibly for days) until the producer has added more events.

My current solution is to use Linux'es inotify, but is there more
elegant assistance available from the sqlite / ruby-sqlite
level. (sqlite seems to have an update_hook, which isn't carried
through to the Ruby wrapper)


John Carter                             Phone : (64)(3) 358 6639
Tait Electronics                        Fax   : (64)(3) 359 4632
PO Box 1645 Christchurch                Email : john.carter@tait.co.nz
New Zealand