Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Richard Hipp]

On 9/13/16, Eric Sink  wrote:
> Excellent.  Thanks.

Thanks for pointing out the issue.  I have attempted to improve the
documentation here:

   https://www.sqlite.org/wal.html#busy

Further improvements may be forthcoming.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

Excellent.  Thanks.

--
E


On Tue, Sep 13, 2016 at 3:05 PM, Richard Hipp  wrote:

> On 9/13/16, Eric Sink  wrote:
> >
> > I can fit this into your explanation:
> >
> > "Another process might have opened the same database with
> > locking_mode=EXCLUSIVE"
> >
> > if I change the word "process" to "thread", and if I assume that
> > sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an
> > exclusive lock, if even for a short time.
> >
> > Would this be a correct understanding?
>
> It's actually sqlite3_close() that gets the EXCLUSIVE lock for a very
> short time.  The last connection to close on a particular database
> gets an EXCLUSIVE lock on that database while it runs a final
> CHECKPOINT and then deletes the -shm and -wal files.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Richard Hipp]

On 9/13/16, Eric Sink  wrote:
>
> I can fit this into your explanation:
>
> "Another process might have opened the same database with
> locking_mode=EXCLUSIVE"
>
> if I change the word "process" to "thread", and if I assume that
> sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an
> exclusive lock, if even for a short time.
>
> Would this be a correct understanding?

It's actually sqlite3_close() that gets the EXCLUSIVE lock for a very
short time.  The last connection to close on a particular database
gets an EXCLUSIVE lock on that database while it runs a final
CHECKPOINT and then deletes the -shm and -wal files.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

The app had several threads that were frequently doing this:

open the sqlite file
do something
close it

When I changed it to stop opening the file so much and re-use the
connections, the problem went away.

I can fit this into your explanation:

"Another process might have opened the same database with
locking_mode=EXCLUSIVE"

if I change the word "process" to "thread", and if I assume that
sqlite3_open_v2() on a WAL-mode file can [perhaps sometimes] involve an
exclusive lock, if even for a short time.

Would this be a correct understanding?

--
E


On Tue, Sep 13, 2016 at 9:27 AM, Eric Sink  wrote:

>
> This is happening in an Android app.  No other process is involved, but
> the filesystem there is weird, so I'm focusing on the third possibility you
> mentioned.
>
> Thanks,
>
> --
> E
>
>
> On Mon, Sep 12, 2016 at 7:52 PM, Richard Hipp  wrote:
>
>> On 9/12/16, Eric Sink  wrote:
>> > OK, this seems like a simple thing, but I'm stuck and looking for
>> > inspiration or clues.
>> >
>> > How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
>> > statement when in WAL mode?
>> >
>> > Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.
>> >
>> > The failing call is just sqlite3_prepare_v2(), and the SQL passed is
>> > nothing more than
>> >
>> > SELECT (explicit column list) FROM (table) WHERE (pk) = @Id
>> >
>> > So if WAL mode means writers don't block readers, it seems like
>> preparing a
>> > SELECT statement should not give me error code 5.  Ever?
>>
>> Another process might have opened the same database with
>> locking_mode=EXCLUSIVE
>> (https://www.sqlite.org/pragma.html#pragma_locking_mode).  If the
>> database is owned by Chrome or Firefox then that is likely the problem
>> because they both do that.
>>
>> Or, there might have been an abnormal shutdown and some other
>> processes has since opened the database and is now trying to recover.
>> Recovery is done while holding an exclusive lock.
>>
>> Or there might be some issue with your filesystem that is preventing
>> the processing from acquiring locks on the file.
>>
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Eric Sink]

This is happening in an Android app.  No other process is involved, but the
filesystem there is weird, so I'm focusing on the third possibility you
mentioned.

Thanks,

--
E


On Mon, Sep 12, 2016 at 7:52 PM, Richard Hipp  wrote:

> On 9/12/16, Eric Sink  wrote:
> > OK, this seems like a simple thing, but I'm stuck and looking for
> > inspiration or clues.
> >
> > How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
> > statement when in WAL mode?
> >
> > Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.
> >
> > The failing call is just sqlite3_prepare_v2(), and the SQL passed is
> > nothing more than
> >
> > SELECT (explicit column list) FROM (table) WHERE (pk) = @Id
> >
> > So if WAL mode means writers don't block readers, it seems like
> preparing a
> > SELECT statement should not give me error code 5.  Ever?
>
> Another process might have opened the same database with
> locking_mode=EXCLUSIVE
> (https://www.sqlite.org/pragma.html#pragma_locking_mode).  If the
> database is owned by Chrome or Firefox then that is likely the problem
> because they both do that.
>
> Or, there might have been an abnormal shutdown and some other
> processes has since opened the database and is now trying to recover.
> Recovery is done while holding an exclusive lock.
>
> Or there might be some issue with your filesystem that is preventing
> the processing from acquiring locks on the file.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result code 5 from sqlite3_prepare_v2() in WAL mode?

[Richard Hipp]

On 9/12/16, Eric Sink  wrote:
> OK, this seems like a simple thing, but I'm stuck and looking for
> inspiration or clues.
>
> How can sqlite3_prepare_v2() return SQLITE_BUSY for a simple SELECT
> statement when in WAL mode?
>
> Immediately prior, a sqlite3_exec("BEGIN TRANSACTION") succeeded.
>
> The failing call is just sqlite3_prepare_v2(), and the SQL passed is
> nothing more than
>
> SELECT (explicit column list) FROM (table) WHERE (pk) = @Id
>
> So if WAL mode means writers don't block readers, it seems like preparing a
> SELECT statement should not give me error code 5.  Ever?

Another process might have opened the same database with
locking_mode=EXCLUSIVE
(https://www.sqlite.org/pragma.html#pragma_locking_mode).  If the
database is owned by Chrome or Firefox then that is likely the problem
because they both do that.

Or, there might have been an abnormal shutdown and some other
processes has since opened the database and is now trying to recover.
Recovery is done while holding an exclusive lock.

Or there might be some issue with your filesystem that is preventing
the processing from acquiring locks on the file.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users