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: https://www.sqlite.org/c3ref/backup_finish.html#sqlite3backupstep > 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. -Rowan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
