# Sqlite, multiple processes and blocking **URL:** \ **Category:** Ruby\ **Created:** [September 25, 2008, 3:06am UTC](https://www.ruby-forum.com/t/sqlite-multiple-processes-and-blocking/149465 "2008-09-25T03:06:54Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![amin1426](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/4da419/32.png) [@amin1426](https://www.ruby-forum.com/u/amin1426)\ **Post date:** [September 25, 2008, 3:06am UTC](https://www.ruby-forum.com/t/sqlite-multiple-processes-and-blocking/149465/1 "2008-09-25T03:06:54Z")
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 C. Phone : (64)(3) 358 6639 Tait Electronics Fax : (64)(3) 359 4632 PO Box 1645 Christchurch Email : removed\_email\_address@domain.invalid New Zealand