Database locking with online backup API

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Database locking with online backup API

Donald Shepherd
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.

Is that the expected behaviour, or is there a flaw in my testing
somewhere?  What defines the subset of time if it is correct?

I'm testing a WAL database if that affects it.

Thank you,
Donald Shepherd.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

R Smith-2

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.
>
> Is that the expected behaviour, or is there a flaw in my testing
> somewhere?  What defines the subset of time if it is correct?
>
> 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. If this is the case, the
controlling software needs an intervening step - and I don't think it
can be done with an immediate transaction either, because the backup too
will wait for that, but this is not tested by me, I might be wrong.

It would actually be real nice if the backup API had a parameter or flag
like "sqlite3_lockduringbackup".



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Donald Shepherd
On Tue, 8 May 2018 at 19:23 R Smith <[hidden email]> 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.
> >
> > Is that the expected behaviour, or is there a flaw in my testing
> > somewhere?  What defines the subset of time if it is correct?
> >
> > 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. If this is the case, the
> controlling software needs an intervening step - and I don't think it
> can be done with an immediate transaction either, because the backup too
> will wait for that, but this is not tested by me, I might be wrong.
>
> It would actually be real nice if the backup API had a parameter or flag
> like "sqlite3_lockduringbackup".
>

Given your description, if I copy all pages at once (sqlite3_backup_step
with a page count of -1) I take it the later writes will not be reflected
in the resultant database and that's the trade off for not locking across
the full sqlite3_backup_step execution.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Rowan Worth-2
In reply to this post by R Smith-2
On 8 May 2018 at 17:22, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

R Smith-2
On 2018/05/08 11:32 AM, Rowan Worth wrote:

> On 8 May 2018 at 17:22, R Smith <[hidden email]> wrote:
>
>> On 2018/05/08 9:37 AM, Donald Shepherd wrote:
>>
>>
>> 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.

Thank you for clarifying - but it is still my understanding that the DB
is not locked (if only in WAL mode), so the backup API, even with -1,
either must ignore changes, or restart. My proposed flag is to lock
rather than restart or ignore.

That said, it's hardly important - can be accomplished in controlling
software and only serves a small number of use cases - but the API is
best placed to effect the request. Perhaps place it on the
"nice-to-have-someday" TO-DO list.

Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Simon Slavin-3
On 8 May 2018, at 10:56am, R Smith <[hidden email]> wrote:

> Thank you for clarifying - but it is still my understanding that the DB is not locked (if only in WAL mode), so the backup API, even with -1, either must ignore changes, or restart. My proposed flag is to lock rather than restart or ignore.

You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN IMMEDIATE depending on which you want.  Then do all the backup stuff, then COMMIT or ROLLBACK without having changed anything.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Donald Shepherd
On Tue, 8 May 2018 at 22:12 Simon Slavin <[hidden email]> wrote:

> On 8 May 2018, at 10:56am, R Smith <[hidden email]> wrote:
>
> > Thank you for clarifying - but it is still my understanding that the DB
> is not locked (if only in WAL mode), so the backup API, even with -1,
> either must ignore changes, or restart. My proposed flag is to lock rather
> than restart or ignore.
>
> You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN
> IMMEDIATE depending on which you want.  Then do all the backup stuff, then
> COMMIT or ROLLBACK without having changed anything.
>
> Simon.
>

Having just tested that (for alternate purposes), it doesn't work.  The
sqlite3_backup_step calls following a "BEGIN IMMEDIATE" instruction
returned SQLITE_LOCKED.

Regards,
Donald Shepherd.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Simon Slavin-3
On 8 May 2018, at 1:12pm, Simon Slavin <[hidden email]> wrote:

> You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN IMMEDIATE depending on which you want.  Then do all the backup stuff, then COMMIT or ROLLBACK without having changed anything.

On 9 May 2018, at 1:50am, Donald Shepherd <[hidden email]> wrote:

> Having just tested that (for alternate purposes), it doesn't work.  The
> sqlite3_backup_step calls following a "BEGIN IMMEDIATE" instruction
> returned SQLITE_LOCKED.

Oops.  I assume you did the lock using the same connetion that was calling the backup API.  In which case I apologise for the incorrect information.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Database locking with online backup API

Donald Shepherd
On Wed, 9 May 2018 at 11:13 Simon Slavin <[hidden email]> wrote:

> On 8 May 2018, at 1:12pm, Simon Slavin <[hidden email]> wrote:
>
> > You can lock the database yourself, using BEGIN EXCLUSIVE or BEGIN
> IMMEDIATE depending on which you want.  Then do all the backup stuff, then
> COMMIT or ROLLBACK without having changed anything.
>
> On 9 May 2018, at 1:50am, Donald Shepherd <[hidden email]>
> wrote:
>
> > Having just tested that (for alternate purposes), it doesn't work.  The
> > sqlite3_backup_step calls following a "BEGIN IMMEDIATE" instruction
> > returned SQLITE_LOCKED.
>
> Oops.  I assume you did the lock using the same connetion that was calling
> the backup API.  In which case I apologise for the incorrect information.
>
> Simon.
>

I did mean to mention that I made sure I ran it on the same connection, so
yes.

To be honest I expected it to work too so I already had it on my list of
test cases.

Regards,
Donald Shepherd.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users