Trevor Talbot wrote:
On 10/31/07, Bill Gatliff <[EMAIL PROTECTED]> wrote:
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.
Bummer. :)
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.
Perhaps, but that may just move the problem. The "GUI information"
table might not be committed before the GUI process wakes up to find out
what happened.
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.)
I control the code on both ends. I looked at the sqlite3 code, and the
places to modify didn't exactly jump off the page at me. :)
Does sqlite3_enable_shared_cache() factor into this any? I turned it
on, and it didn't seem to help.
What about sqlite3_commit_hook()? Though the (very few) examples I've
managed to find don't make it clear how to figure out the rowid that was
updated. And without that, I can't easily determine which semaphore I
need to post to... Can I pass the database handle as the argument to
the callback?
b.g.
--
Bill Gatliff
[EMAIL PROTECTED]
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
