SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

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

SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

test user
Hello,

Im getting this message in the log:

`SQLITE_BUSY, database is locked in "PRAGMA journal_mode"`

I get this response when running the query `PRAGMA journal_mode`.

The file is in journal_mode=WAL.

Another connection holds a write transaction.

Seeing as `PRAGMA journal_mode` is just a read, why would this return BUSY?

I assumed that in WAL mode you can have many reads and a single writer at
the same time?

Thanks
_______________________________________________
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: SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

test user
Just some more details to clarify the issue:

If I do a `BEGIN IMMEDIATE` on one connection, and then a `PRGAMA
journal_mode` on another, BUSY is not returned (as expected).

But if I have around 7 connections contending for a write lock via `BEGIN
IMMEDIATE`, and a different connection runs `PRGAMA journal_mode` (with no
`BEGIN`), it returns BUSY (not expected).

I also occasionally get a "SQLITE_BUSY_RECOVERY", but all API usages are
using finalize/close to give back any sqlite API resources.

*Questions:*
A. In which cases will BUSY be returned for read only queries when in WAL
mode?
    - I assumed this was never, as WAL mode allows many concurrent readers.
        - How can I find out exceptions to this rule?


B. Is there any method for determining lock transitions for connections?
    - Is there an API?
    - Would it be possible to use dtrace to instrument SQLite to detect
lock transitions?
        - Where should I be looking?

Id really appreciate any pointers,

Thanks.

On Wed, Aug 28, 2019 at 9:46 PM test user <[hidden email]>
wrote:

> Hello,
>
> Im getting this message in the log:
>
> `SQLITE_BUSY, database is locked in "PRAGMA journal_mode"`
>
> I get this response when running the query `PRAGMA journal_mode`.
>
> The file is in journal_mode=WAL.
>
> Another connection holds a write transaction.
>
> Seeing as `PRAGMA journal_mode` is just a read, why would this return BUSY?
>
> I assumed that in WAL mode you can have many reads and a single writer at
> the same time?
>
> Thanks
>
_______________________________________________
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: SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

Rowan Worth-2
On Fri, 30 Aug 2019 at 04:18, test user <[hidden email]>
wrote:

> B. Is there any method for determining lock transitions for connections?
>     - Is there an API?
>     - Would it be possible to use dtrace to instrument SQLite to detect
> lock transitions?
>         - Where should I be looking?
>

 On unix sqlite uses fcntl() with cmd=F_SETLK on specific byte locations to
acquire locks -- I'm not familiar with dtrace, but I've used strace + sed
to watch sqlite lock activity before. eg:

#!/bin/sh

PID=$1

replace() {
     echo "s#F_SETLK, {type=F_$1, whence=SEEK_SET, start=$2, len=$3}#$4#"
}

strace -Ttt -ff -e trace=fcntl -p $PID 2>&1 |
sed \
    -e "$(replace RDLCK 1073741824 1 acquireR{PENDING})" \
    -e "$(replace RDLCK 1073741825 1 acquireR{RESERVED})" \
    -e "$(replace RDLCK 1073741826 510 acquire{SHARED})" \
    -e "$(replace WRLCK 1073741824 1 acquireW{PENDING})" \
    -e "$(replace WRLCK 1073741825 1 acquireW{RESERVED})" \
    -e "$(replace WRLCK 1073741826 510 acquire{EXCLUSIVE})" \
    -e "$(replace UNLCK 1073741824 2 release{PENDING+RESERVED})" \
    -e "$(replace UNLCK 1073741824 1 release{PENDING})" \
    -e "$(replace UNLCK 1073741825 1 release{RESERVED})" \
    -e "$(replace UNLCK 1073741826 510 release{SHARED/EXCLUSIVE})" \
    -e "$(replace UNLCK 0 0 release{ALL})"

-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: SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

test user
Thanks Rowan, this is useful.

Is it normal to get a `SQLITE_BUSY_RECOVERY` response from an API when:
- No processes have crashed.
- All API uses close/finalize their db/stmt objects.

I am testing some code I wrote to make sure it retires on BUSY by creating
many processes that acquire locks with `BEGIN IMMEDIATE`.

`SQLITE_BUSY_RECOVERY` occurs at around 7 processes requesting a write
lock, but not at 5 processes or below.

Does this indicate corrupted data?







On Mon, Sep 2, 2019 at 2:45 AM Rowan Worth <[hidden email]> wrote:

> On Fri, 30 Aug 2019 at 04:18, test user <[hidden email]>
> wrote:
>
> > B. Is there any method for determining lock transitions for connections?
> >     - Is there an API?
> >     - Would it be possible to use dtrace to instrument SQLite to detect
> > lock transitions?
> >         - Where should I be looking?
> >
>
>  On unix sqlite uses fcntl() with cmd=F_SETLK on specific byte locations to
> acquire locks -- I'm not familiar with dtrace, but I've used strace + sed
> to watch sqlite lock activity before. eg:
>
> #!/bin/sh
>
> PID=$1
>
> replace() {
>      echo "s#F_SETLK, {type=F_$1, whence=SEEK_SET, start=$2, len=$3}#$4#"
> }
>
> strace -Ttt -ff -e trace=fcntl -p $PID 2>&1 |
> sed \
>     -e "$(replace RDLCK 1073741824 1 acquireR{PENDING})" \
>     -e "$(replace RDLCK 1073741825 1 acquireR{RESERVED})" \
>     -e "$(replace RDLCK 1073741826 510 acquire{SHARED})" \
>     -e "$(replace WRLCK 1073741824 1 acquireW{PENDING})" \
>     -e "$(replace WRLCK 1073741825 1 acquireW{RESERVED})" \
>     -e "$(replace WRLCK 1073741826 510 acquire{EXCLUSIVE})" \
>     -e "$(replace UNLCK 1073741824 2 release{PENDING+RESERVED})" \
>     -e "$(replace UNLCK 1073741824 1 release{PENDING})" \
>     -e "$(replace UNLCK 1073741825 1 release{RESERVED})" \
>     -e "$(replace UNLCK 1073741826 510 release{SHARED/EXCLUSIVE})" \
>     -e "$(replace UNLCK 0 0 release{ALL})"
>
> -Rowan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

Simon Slavin-3
On 4 Sep 2019, at 12:39pm, test user <[hidden email]> wrote:

> Is it normal to get a `SQLITE_BUSY_RECOVERY`

This code should only ever follow a crash, or some operation which has corrupted a database.  If your hardware does not crash you should never see it.  Something is wrong.

<https://sqlite.org/rescode.html#busy_recovery>

> - No processes have crashed.
> - All API uses close/finalize their db/stmt objects.

Are you checking the result returned by all your operations to make sure they are SQLITE_OK ?

Are you interfering with SQLite by manually deleting database files, or deleting journal files, or by using fcntl() on SQLite datbases ?
_______________________________________________
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: SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

Richard Hipp-3
On 9/4/19, Simon Slavin <[hidden email]> wrote:
> On 4 Sep 2019, at 12:39pm, test user <[hidden email]> wrote:
>
>> Is it normal to get a `SQLITE_BUSY_RECOVERY`
>
> This code should only ever follow a crash,

Just to be clear, "crash" in the above statement can also mean
"program exits without calling sqlite3_close()".

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users