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 > file. > > 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 > timestamp? > 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 BEGIN SELECT your_nigth_watcher(); END HTH > > Thank you for your insights, > > Wout. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras <emorr...@yahoo.es> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
