On Tue, 07 Nov 2017 18:07:42 +0000
Wout Mertens <wout.mert...@gmail.com> wrote:
> I'm working with a db that's only written to in transations, and each
> transaction increases a db-global version counter.
> This means that I can cache all reads, unless the version changed.
> What would be the most efficient way to make sure I *never* serve
> stale data?
> Right now everything's a single process, so it's really easy, just
> clear the cache on every write. However, I want to be prepared for
> the near future where I will have multiple processes using this db
> I'm thinking that to detect writes, this might be a safe approach:
> Before serving any cached read, check the timestamp on the wal file.
> If it changed, read the global version. If it changed, clear the
> cache. Otherwise, serve the cached read.
> Is it safe to assume that all writes would mean change of the wal file
> More importantly, is it faster to check the timestamp or would a
> prepared query for the version actually be faster (and safer)?
> Also, I'm using WAL right now, but I wonder if that's really useful
> given the single-writer-at-a-time?
You can define triggers on insert, update and delete that fires a user defined
function that warns your other threads or an external process (I use a similar
setup on a AS400/DB2).
Something like this:
CREATE TRIGGER tg_night_watcher_insert AFTER INSERT ON table_name_to_watch_up
> Thank you for your insights,
> sqlite-users mailing list
Eduardo Morras <emorr...@yahoo.es>
sqlite-users mailing list