The BEGIN IMMEDIATE only locks the source database so that its contents
will not change while the copy is in progress. The fact that the other
databases are read-only means that they do not need to be locked against
changes, but they do need have some form of synchronization so that
existing readers do not have the rug pulled out from under them.
The means of handling the read-only databases is somewhat OS dependent.
A method to try is to:
. copy the database file to a temporary name on the slave machine
. rename (mv) the read-only database to a temporary name
. rename (mv) the copied database to the read-only database name
New connections to the read-only database will use the new version,
existing connections will cease to use the old version when the
connection drops. If you have the misfortune to be using Windows, you
might find difficulty in doing it that way.
Nikola Miljkovic wrote:
[In the message "Re: [sqlite] 1-way replication best practices?" on Sep 28,
11:29, Dan Kennedy writes:]
On Thu, 2007-09-27 at 14:26 -0700, Cyrus Durgin wrote:
hi,
i'm using sqlite3 in a small project that will run on multiple servers.
only one of the instances will be read-write to the database, the others are
all read-only. i understand that the recommended process for replication on
the read-write instance looks something like:
BEGIN EXCLUSIVE;
<copy database file>
COMMIT;
is it roughly the same on the read-only (destination) side? in other words
is:
BEGIN EXCLUSIVE;
<copy new file into place>
COMMIT;
the correct procedure?
I think you will need to make sure that all SQLite instances have
closed the read-only database before copying over it. Otherwise all
the existing connections will continue to read the old data.
Dan.
This is very interesting.
I got impression that the connections will figure out that
they have new file (if it was overwrite and not
move and copy) and just invalidate cache.
Always ready to change the view :) .
So the strategy could be to connect to database every so often,
or even better to create adjoint database with one table like:
create table version (version integer);
and increment it in the same BEGIN/COMMIT scope.
Then have your program check for version and reopen main
database when needed
Now I do have a question regarding BEGIN/copy/COMMIT process
The BEGIN is made on the original database
copy could be overwrite or rename old/copy new
and COMMIT is made on the new database (or perhaps old if it was
moved).
How come there is no corruption of something here.
It does not sound veery consistent from the perspective
of a casual user.
Can someone tell us what really happens in there, and what
are possible ramifications.
Thanks,
Nikola
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
