There is also http://sqlite.org/pragma.html#pragma_user_version which more 
closely resembles what you have now.

I strongly suspect that an update cycle of the user_version should be done 
within the transaction performing the changes.

BEGIN
Read user version
<your changes go here>
Write updated user version
COMMIT


-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Wout Mertens
Gesendet: Dienstag, 07. November 2017 19:08
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Most efficient way to detect on-disk change

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?

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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to