On 8 May 2018 at 17:22, R Smith <ryansmit...@gmail.com> wrote:

> On 2018/05/08 9:37 AM, Donald Shepherd wrote:
>> I've long assumed that when using the online backup API on a SQLite
>> database, other processes will not be able to write to the source database
>> for the duration of the sqlite3_backup_step call.  However under some
>> testing I've been performing, I've found that this doesn't appear to be
>> the
>> case.  Instead writes are prevented for a very small subset of that time,
>> if at all.
>> I'm testing a WAL database if that affects it.
> Expected and documented indeed. The basic rule that backup abides by is
> this:
> "Copy the database in a completely wholesome state to the destination."
> "If the data changes (and it can) then restart the backup process from
> start on the new data state."
> This works well for 90% of cases, but care is to be exercised for a really
> big + busy database (where writes are likely within the period of backup),
> the backup can infinitely restart. <snip>
> It would actually be real nice if the backup API had a parameter or flag
> like "sqlite3_lockduringbackup".

Not quite right. sqlite3_backup_step accepts a number of pages to be copied
by the current invocation. If you specify a negative number of pages,
sqlite3_backup_step will obtain a read lock and copy the entire database
before returning. So the "lock during backup" mode already exists - the
only way you get the backup restarting behaviour is if you ask
sqlite3_backup_step to copy a subset of the database.

The behaviour I describe is documented here:

> Every call to sqlite3_backup_step() obtains a shared lock
<https://www.sqlite.org/lockingv3.html#shared_lock> on the source database
that lasts for the duration of the sqlite3_backup_step() call.

I'm not familiar with WAL mode, but since it's designed to allow readers
and writers to operate concurrently, it's perhaps no surprise that the
backup (purely a read operation) doesn't appear to block writes to the
source DB.

Fun fact: changes made to the source DB from an sqlite connection in the
same address space as the backup's sqlite connection are automatically
propagated to the destination DB, without having to restart the backup.

sqlite-users mailing list

Reply via email to