Sqlite connection management with multiple client access?
(1.1) By wrenashe on 2021-12-13 12:53:35 edited from 1.0 [link] [source]
I have a little complicated user scenario, I am using sqlite3 in one application, due to historical reasons, the application accesses(write) to sqlite3 database via the application backend service(in C code)in WAL mode, and application UI(in java swing) talks to the sqlite3 database via JNI(db open/close and other operations are done in JNI), the application runs on both windows, and Linux.
On Linux, sometimes that application met the problem. When more than one frontend Java-based UI(via the same JVM) opens the database( via JNI, and read), the UI cannot display any new data which are saved from the application backend service after UI window is opened. Sqlite is built by ourselves and enabled with -DSQLITE_THREADSAFE=1, the serialized mode.
So I wonder if I can expect some comments here, how exactly the Sqlite connection management does? Thanks in advance.
(2.1) By wrenashe on 2021-12-13 12:53:41 edited from 2.0 in reply to 1.0 [link] [source]Deleted
(3) By Simon Slavin (slavin) on 2021-12-14 14:32:41 in reply to 1.1 [link] [source]
If I understand this correctly, you have one (old C) application making changes to the database, and many (new Java) applications trying to read from the same database, all at the same time. Or possibly the Java programs do writing too. The most likely scenario is that one or other application was not read with multiple simultaneous access in mind. It is not being 'polite' by committing its transactions as soon as possible.
SQLite does let simultaneous access happen, and there's no reason why WAL mode would prevent it, but the people who programmed the applications have to have had this in mind when they wrote the software: rather than optimize each program for speed, they have to optimize each program for leaving the database free. This means using transactions properly: BEGIN, a short burst of commands, then COMMIT as soon as possible. Or for a single command you can rely on automatic transactions and not use either command.
It's possible that you're using a JAVA library which constructs its own transactions. Perhaps whoever used that library misunderstood the implications of this, or perhaps even the library is broken and was never tested to make sure it really did commit transactions. I don't know enough about common SQLite3 libraries to comment.
You might be able to use the SQLite CLI to access the same database, and use that to test whether the above is true. You can use BEGIN and COMMIT commands around read/write commands to keep the database locked in different ways, and see whether you can simulate what you're seeing in your applications.
(4) By Rowan Worth (sqweek) on 2021-12-16 05:09:05 in reply to 1.1 [source]
Just to check, is the DB being stored on a local disk? ie. are the backend service and frontend UI running on the same machine? WAL mode only works properly when the DB is accessed via a single machine.
Otherwise, the likely candidate is poor transaction management. After a transaction starts, any changes to the DB made by other processes will not be visible until the transaction has finished. So all it would take is one dangling transaction to exhibit this symptom.
Transactions in sqlite are a connection-level concept, so I'd also make sure you aren't sharing the same connection between multiple UIs.
(5) By wrenashe on 2021-12-16 11:06:39 in reply to 3 [link] [source]
Thanks, Simon. I would like to check the new UI whether the proper transaction is performed in the scenario.
(6) By wrenashe on 2021-12-16 11:08:40 in reply to 4 [link] [source]
Part of UI (java application) access the sqlite database via localhost RMI call I recalled. I will double check that as well as the connection is shared or not.
(7) By Simon Slavin (slavin) on 2021-12-16 14:39:27 in reply to 6 [link] [source]
Because it's important, and may be the cause of your problem, can you tell us which of the many things called 'RMI' you're referring to ?
Are you using a real computer which you have physical access to, or some kind of virtual setup, as in a Virtual Machine or AWS/GCP/Azure/VMware ?
Does that real computer have a real storage device inside or next to it, accessing it via storage calls, or it it storing the database on a virtual or network drive ?