On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote: > My application is a mobile platform with a GUI that wants to display > frequently-updated data in a database. The GUI is a separate process > from the one providing the data, and is one of several consumers of that > data.
> I prefer not to poll for changes, because the system is > performance-constrained. So instead, I'm using an AFTER UPDATE trigger > in the data-generating process to launch a C function that posts to a > semaphore. The GUI process is thus unblocked, and it then does a SELECT > to get the data. I'm using the C/C++ API everywhere. > > The problem I'm seeing is that the GUI process is getting stale data in > its SELECT, unless it does a brief sleep between the sem_wait() and the > sqlite3_exec(). Specifically, the value returned is the value > immediately before the UPDATE. It's as if the trigger in the > data-generating process and the query in the GUI process are both > running before the new value is actually committed to the database file > and/or whatever caches are in between. The trigger necessarily fires immediately after the row change, but before the transaction is committed. It's the committing that takes most of the time in a database write operation. So yes, definitely expected behavior. If your platform has a file modification notification mechanism, you may be able to sleep on that instead. Of course the problem with this approach is that it's only a coarse-grained "something changed" notification, and doesn't tell you what changed. You may be able to counter that by having the trigger store a note about what changed in a separate table that your GUI queries when it wants to know specifics. If you don't have a cheap file notification, something more creative might be necessary. Do you control the sqlite library used by the data writers? Perhaps you can modify it to provide notifications immediately after commit instead. (I'm assuming you don't want to modify the writers themselves directly, by having them signal after they issue a COMMIT.) ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
