unable to get latest updates to database (WAL mode) from one open handler when updated from other open handler
(1.1) Originally by Kartik Thaore (kthaore) with edits by Richard Hipp (drh) on 2021-06-07 14:23:58 from 1.0 [link] [source]
Our device has SQLite database configured in WAL mode. Our SQLite database is configured as follows:
PRAGMA main.journal_mode = WAL PRAGMA main.wal_checkpoint(RESTART) PRAGMA locking_mode = NORMAL PRAGMA busy_timeout = 100
Compile time options selected are as follows:
-DSQLITE_TEMP_STORE=2 -DSQLITE_OMIT_DECLTYPE=1 -DSQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=32768 -DSQLITE_OMIT_DEPRECATED=1 -DSQLITE_OMIT_SHARED_CACHE=1
FW has 2 separate SQLite open connections for accessing the database: Connection 1 - to read/write from different tables of the database Connection 2 - only to read contents of few tables from the database
We are observing an issue where an entry to one of the table is added using "Connection 1". Immediately after this "Connection 2" is used to read the contents of same table expecting that newly added information by "Connection 1" must be updated. But "Connection2" does not find the updated information in the table.
If a sleep(1) is added just before the "Connection 2" runs query to fetch the updated information then it correctly gets the updated information from the table. But otherwise "Connection 2" does not get the updates. Alternatively a sync() invocation just before "Connection 2" read operation also resolves the issue.
We also tried doing wal-checkpoint(PASSIVE) after "Connection 1" updates; but this does not resolve the issue.
Why "Connection 2" is not able to see the updated information from "Connection 1" in SQLite database? and Why a sleep() or a sync() call is resolving the issue?
Is it not that all processes will first refer to WAL file for read / write operations instead of directly referring the database?
Is there some concurrency issue between "Connection 1" and "Connection 2"?
Request help or some directions to look into for understanding and resolving this issue at hand.
Thanks in advance.
(2) By Larry Brasfield (larrybr) on 2021-06-07 14:25:20 in reply to 1.0 [link] [source]
Why do you believe that the allegedly missing update is not part of a transaction not yet complete when the other connection fails to see it?
(5) By Kartik Thaore (kthaore) on 2021-06-07 17:38:59 in reply to 2 [link] [source]
Transaction is not handled explicitly in firmware. Can you please share a good way to know if at all any transaction is in progress at a certain point of execution?
(6) By Larry Brasfield (larrybr) on 2021-06-07 19:30:58 in reply to 5 [link] [source]
All DB operations effected via prepare and step, with the exception of some configuration, are done under a transaction. If the transaction is not setup explicitly, an automatic enclosing transaction is created.
(7.1) By Keith Medcalf (kmedcalf) on 2021-06-07 19:38:47 edited from 7.0 in reply to 5 [source]
See https://sqlite.org/c3ref/txn_state.html which will tell you the transaction state of a particular database schema.
(3) By Richard Hipp (drh) on 2021-06-07 14:28:03 in reply to 1.1 [link] [source]
The statement "
PRAGMA main.wal_checkpoint(RESTART);" is a one-time
operation, not a configuration setting.
You didn't mention what OS and Filesystem you are using. Is this perhaps
happening on a network filesystem of some kind? If so, have you tried
PRAGMA synchronous=FULL;" to see if that helps?
(4) By Kartik Thaore (kthaore) on 2021-06-07 17:37:41 in reply to 3 [link] [source]
Yes, agree with first point.
Device runs Linux OS; since SQLite database is in non volatile memory its filesystem is ubifs. I suppose the pragma Synchronous is configured to default; though I will confirm on this.