Is there some timeout in DatabaseQuery() with sqlite? Fred?

[Kukulkan]

Hi,

we're experiencing strange SQLite queries comming back after exactly 1000ms (one second). The SQLite guys do not have an idea why.

So maybe PureBasic is having such limitation or timeout somewhere? Or maybe is SQLITE_BUSY as result ignored (https://sqlite.org/rescode.html#busy)?

We build using PB 5.71 LTS and have the issue on both Windows machines (32 bit executable) and Linux machines (64 bit executable).

[mk-soft]

On macOS is timeout after 20000 ms.

query 'PRAGMA busy_timeout;'

[percy_b]

Hi,

we're experiencing strange SQLite queries comming back after exactly 1000ms (one second). The SQLite guys do not have an idea why.

So maybe PureBasic is having such limitation or timeout somewhere? Or maybe is SQLITE_BUSY as result ignored (https://sqlite.org/rescode.html#busy)?

We build using PB 5.71 LTS and have the issue on both Windows machines (32 bit executable) and Linux machines (64 bit executable).

Can you provide a sample of one of the queries that are coming after 1 second?

Also, are multiple users accessing the DB at the same time with write access?

Sent from my LM-Q710.FG using Tapatalk

[Fred]

DatabaseError() returns nothing ? What we can add is the error code as first part of the DatabaseError() so you could check what it is.

[firace]

You can use this command to change the busy timeout value of SQLite:

Code: Select all

DatabaseQuery(0, "pragma busy_timeout=30000")     ;; set value of busy_timeout to 30s

Add this line right after your OpenDatabase() call.

If this does not fix the issue, please post a code snippet.

[Kukulkan]

I just checked and connected to the same sqlite database my app is currently working with.

It turns out that PRAGMA busy_timeout; returns 0. Due to the documentation of sqlite, this means

Calling this routine with an argument less than or equal to zero turns off all busy handlers.

So there is no busy handler active for the database opened in PB, but how come? I do not really understand what that means for timeouts I think there is no 1 second timeout in PurePasic then, right?

Sadly, we experience these problems only randomly at customers and can not reproduce reliable here. So we can not verify what kind of errors they currently get. We only see that some queries need exactly 1000ms, which makes me nervous. The return code of these queries with DatabaseUpdate() is not 0, meaning success.

Affected queries are like

Code: Select all

update files set state = state & ~117440512 where id = 84
update files set state = state & ~117440512 where id = 103
update versions set state = state & ~ 1048588 where state & 32 = 0 and objectName != '2xcyhz2ytxk4ggw0' and fileId in ( select id from files where perms & 8388608 = 0 and id = 202 and boxId = 2)

There are only a few thousand entries in the tables, so it should be lightning fast. But we hammer the database with up to 12 threads, so there might be some concurrency. This is why we open with these pragmas:

Code: Select all

PRAGMA synchronous = 0
PRAGMA temp_store = 2
PRAGMA journal_mode = WAL

It works fine, but under some heavy load, queries seem to become slow (random between 100 and 500ms) but some queries need exactly 1000ms. This is why I get nervous. Multiple queries needing exactly 1000ms and returning success. This is strange.

[Marc56us]

Try to avoid SQLite auto-commit and set it in query (add BEGIN and COMMIT in query)
in single query or more (COMMIT/ROOLBACK depend of result)

Code: Select all

; ie: Replace
DatabaseUpdate(0, "CREATE TABLE info (test VARCHAR(255));")

; by
DatabaseUpdate(0, "BEGIN; CREATE TABLE info (test VARCHAR(255)); COMMIT")

; or
DatabaseUpdate(0, "BEGIN; CREATE TABLE info (test VARCHAR(255));
; [others tests], then
DatabaseUpdate(0, "COMMIT;")

[Kukulkan]

Hi Marc56us,

Try to avoid SQLite auto-commit and set it in query (add BEGIN and COMMIT in query)

I think auto commit is exactly doing the same (BEGIN; query; COMMIT). So what is the benefit of doing it by myself?

[Marc56us]

Auto-Commit validates each request, one by one.
You can clearly see the difference when importing a file.

I don't know if it will work better, but it's easy to test and non-destructive.

[Kukulkan]

I tested with a scenario of 2400 SQL executions (insert, update). All DB queries are wrapped in a small function so I was able to test by changing at one place only. I simply added this before execution. So all updates and inserts are covered like this.

Code: Select all

sql.s = "BEGIN;"+sql.s+";COMMIT"

There was absolutely no speed difference in using the database with Auto-Commit or enhancing SQL like this. No significant difference was found by using my test environment. Not sure if there are other benefits?

Also, this is not really the question. I wondered about the queries and updates needing exactly 1000ms(+-2ms). I can not find out why this happens sometimes. And I found others that needed 2301ms (for example). So I do not think that there is such 1000ms limitation or timeout in general.

[skywalk]

It may be time to consider a design change if you are trying many threads on SQLite.
Either postgresql or a single thread for all access to SQLite. The many threads submit their queries to a single queue which you manage. The single thread executes the queue in fifo manner.