Sqlite, multiple processes and blocking

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

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 C. Phone : (64)(3) 358 6639
Tait Electronics Fax : (64)(3) 359 4632
PO Box 1645 Christchurch Email : [email protected]
New Zealand