On Sun, 1 May 2005, D. Richard Hipp wrote:
On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote:
i want to make a copy of a sqlite3 database file while it is open. is
there anyway that i can tell programatically that any caches/journals
are flushed out? or is there any way to get and exclusive lock i
guess, before making a backup copy?
1. Execute the SQL statement "BEGIN EXCLUSIVE".
2. Make your backup copy of the database file.
3. Execute the SQL statement "COMMIT".
The BEGIN EXCLUSIVE statement makes sure that the
database file is locked and will not be changed while
your are copying it. The COMMIT statement release
the lock after you have finished making the copy.
ignoring threads - would it be sufficient to just obtain an exclusive
(fcntl)
lock on the db? it seems to be:
i do something like this for some code i have that backs up a hot nfs mounted
sqlite db that has around 30 readers/writers doing about 1000 transactions per
hour (eg. heavy - but not __extremely__ heavy load)
- n times just blindly copy the db and try to open it. if the open succeeds
run an integrity check. if the integrity check succeeds we've made a
copy - exit.
- finally, if the method above fails, obtain an exclusive lock on the db,
copy it, run an integrity check, then (iff successful) release lock. i
don't have any threads and all access to my db is wrapped in a single
class so this ensures only one process in my system, on any given host,
has the lock while the copy takes place.
i've been using this method to take hourly snapshots of my db for the past 9
months, through several disk and power failures, with no issues. not that
there aren't any - but it seems to work out alright.
cheers.
-a
--
===============================================================================
| email :: ara [dot] t [dot] howard [at] noaa [dot] gov
| phone :: 303.497.6469
| renunciation is not getting rid of the things of this world, but accepting
| that they pass away. --aitken roshi
===============================================================================
