Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Wed, 9 May 2018 at 11:13 Simon Slavin  wrote:

> On 8 May 2018, at 1:12pm, Simon Slavin  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 
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[Simon Slavin]

On 8 May 2018, at 1:12pm, Simon Slavin  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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Tue, 8 May 2018 at 22:12 Simon Slavin  wrote:

> On 8 May 2018, at 10:56am, R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[Simon Slavin]

On 8 May 2018, at 10:56am, R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[R Smith]

On 2018/05/08 11:32 AM, Rowan Worth wrote:

On 8 May 2018 at 17:22, R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[Rowan Worth]

On 8 May 2018 at 17:22, R Smith  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. 
>
> 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
 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

Re: [sqlite] Database locking with online backup API

[Donald Shepherd]

On Tue, 8 May 2018 at 19:23 R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking with online backup API

[R Smith]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users