I am maintaining a legacy code wherein we have a cocept of TempDB and FullDB, TempDB is just a small instance of FullDB, so that user can browse while FullDB is prepared.
Since lots of writes were involved in FullDB, reading and writing on same database file was creating a lock for readers on other thread. SO I am thinking of the following strategy, which best fits in our situation, in case its possible.
Here's what I want to do :
- Start preparing the DB, when threshold for tempDB is reached, commit the transaction and close the connection.Make a copy of this file, lets call them
orig
(which is the temp db) andcopy
(which is copy of temp DB and further writes will be done to this file). - Next, readers can open a connection on
orig
as soon as they receive an event. Writer will open a connection oncopy
and perform remaining writes for quite a long time during which readers are using theorig
temp db. - When the writer has prepared the full DB
copy
, I need to replace theorig
file with the updated full dbcopy
. - Here's the catch, readers will not close and reopen the connection. So I need to block the readers while I am replacing the DB. This I can achieve by acquiring an
EXCLUSIVE
lock on theorig
DB, and then I can replace theorig
db withcopy
db (renaming).
The Problem : The readers are not accepting the new DB file.How can I make them to do that? I mean when I tried through terminal : make a DB, copy it and make some entries into the copy and then replace the original with the copy, I was still getting entries that were present in the original DB. To the surprise, even when I deleted both (orig and copy) the DB files, I was still getting entries. It seems SQLite was picking data from some in-memory and not from the disk files.
Any help?
PS : On searching I found something called .open
command but not sure how it works or whether its really helpful.
EDIT Is this what I want?
See Question&Answers more detail:os