[yunqa.de] Re: [yunqa.de] How to restore from backup or alter db in multi-user envionment?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Mon, 03 Dec 2012 23:17:14 +0100
On 02.12.2012 15:37, H M wrote:
> There are quite a few topics on the net about restoring SQLite
> databases. Most of them warn against not doing it when there are open
> connections but none seem to explain how application A is supposed to
> know that application B has open connections at the time. Each time I
> came up with an idea to check I seemed to run into a race condition
> needing semophores.
Contrary to what I wrote in my previous message, the SQLite Online
Backup API actually takes care that no other application can write to
the destination database during the backup. The documentation reads:
"The first call to sqlite3_backup_step() obtains an exclusive lock on
the destination file. The exclusive lock is not released until either
sqlite3_backup_finish() is called or the backup operation is complete
and sqlite3_backup_step() returns SQLITE_DONE."
This means that other applications can keep a handle to the destination
database open while the backup is running. However, they are not allowed
to operate freely on it as SQLite's locking restrictions apply once the
backup has started and until it has been finished. The backup will not
actually start before it can acquire an exclusive lock of the
destination database.
I have just tested this using the demo project in
DISQLite3_Install\Demos\DISQLite3_Backup\DISQLite3_Backup.dpr
and found it working well.
> Possibly for my issue I can get away with doing what I need in a
> transaction anyway, without backing up. Its often just small changes
> like adding a field to a table, making a new view or changing the sql of
> a view.
The SQLite Online Backup API is intended for what its name suggests:
Backups. If you do not need a backup, transactions work well for small
as well as large changes. They should run faster than a backup and will
automatically rollback if something goes wrong. They will just not not
leave you with a backup copy of your database in case the application
wrote erroneous data.
Given the above, both backup and transaction should work well for you.
Which one you want to prefer depends mostly on what you actually want to
achieve.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
- » [yunqa.de] Re: [yunqa.de] How to restore from backup or alter db in multi-user envionment?â - Delphi Inspiration
