Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Richard Hipp]

On 9/4/19, Simon Slavin  wrote:
> On 4 Sep 2019, at 12:39pm, test user  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Simon Slavin]

On 4 Sep 2019, at 12:39pm, test user  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.



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

Re: [sqlite] 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  wrote:

> On Fri, 30 Aug 2019 at 04:18, test user 
> 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
> 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] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

[Rowan Worth]

On Fri, 30 Aug 2019 at 04:18, test user 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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