On 12/07/2011 12:25 PM, Dan Kennedy wrote:
On 12/07/2011 01:35 AM, Alejandro Martínez wrote:
I'm trying to use sqlite3 as a cache layer for queries on other
database. Actually, replace an existing layer cache which is very
adhoc and propietary (and that sucks), and i'd like to replace it with
something peer reviewed / open source, etc like sqlite.
So... I would like to have a process which periodically gets some
tables from a master database and stores it in a sqlite database.
Then some other processes would access the sqlite database to read and
use such cached data.
So its the tipical scenario of "one writer and many readers".
So the question is... regarding the locking mechanism...
What would be better during the "writer" process's "refresh" cycle for
each table:
1. Create a new table, load the new content (lots of inserts), and
then replace the old table by dropping the old one and renaming the
new one within a transaction.
or
2. Drop the table and then insert the new content within a transaction.
The objective is to block readers the least ammount of time possible
(probably using read uncommited), and of course, not cause writer
starvation.
I don't understand well from the documentation how "read uncommited"
performs when the master table is modified (as in option 1).
And yes, i've considered using a wal journal, but the problem is i'm
scared of the wal file growing out of control as i can't completely
guarantee that ALL prepared statements would be "reset" at the same
time and thus reach a checkpoint. The reason for this is that i must
satisfy a certain api, beyond my control :S for backwards
compatibility reasons.
If you run the command:
PRAGMA wal_checkpoint = restart
Then it blocks (calls the busy-handler) until all frames in the
WAL file have been checkpointed and all readers are reading the
latest database snapshot from the database file. This guarantees
that the next client to write to the database starts writing to
the start of the WAL file.
Other database writers are blocked while the "PRAGMA wal_checkpoint"
is running, but readers are not.
http://www.sqlite.org/pragma.html#pragma_wal_checkpoint
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
