Hi all,

in my application, I have multiple connections/thread to a database (I'm on linux, database stored on xfs and ext4) I'm using WAL.

Each thread "owns" one connection. There's one writer thread, which is the only one which writes in the database, doing transactions with usually hundreds of inserts/updates/deletes each.

All the other threads which access the database are read-only connections (mode=ro, and only allowed to perform query calls) with private cache each.

Every time the writer thread successfully commits a transaction, it notifies the reader threads about the updated data and the readers immediately read it after receiving such notification.

I noticed that, when I have only one reader thread/connection, this schema works as expected and the reader thread can always see the updated database (even performing thousands of reads per second). But, when I increase the number of readers, they sometimes appear to see an old version of the database, before the transaction.

I've been relying in the fact that after a transaction is persisted, all connections will "see" the same database, but I confess I don't know if I understood sqlite well or if such guarantee actually exists.

It might also be a bug in my application as well, so before I spend more time debugging it, I'd like to know if sqlite offers such guarantee and, if, not, if you have any advice on how to proceed to achieve such results.

More technical details:

• The application itself is written in Go (and it's open source, at https://gitlab.com/lightmeter/controlcenter/ :-)).
• The writer thread notifies the reader threads via Go channels, and the readers are organized in a "thread pool", where the thread which will handle the "results" of a transaction chosen arbitrarily by reading the same channel.
• Im'm using go-sqlite3 (wrapping sqlite 3.34.0).

Thank you folks in advance for such amazing piece of software :-)

I think your application would benefit from using SQLite Shared-Cache Mode.

I remember doing some experiments with the shared cache on the reader connections and notices that it ended up serializing all queries from different connections, much slower than having a share-nothing approach with private cache.

Should such behaviour be expected with the shared cache?

In any case, I did such experiments quite a while ago, so it was probably some bug in my code, so I will try to do some more experiments again.

Do you think the private cache could be related to the original behaviour I experienced?

shared cache ... ended up serializing all queries from different connections

During your experiments, did you use "Read-Uncommitted Isolation Mode"? Without that, even read queries are serialized.

Do you think the private cache could be related to the original behaviour I experienced?

I see that as likely, but have not read enough code or docs to see how SQLite determines that a page cache has become stale. But I find it hard to imagine that it would go to the persistent store to determine that, as that would reduce the performance of the cache significantly. Further, I do not see your post #1 as describing a violation of the ACID promise. (No real software could guarantee that all threads in all processes see the same DB at any instant.) Hence, I see a likelihood that the shared cache, (sharable across threads since v3.5.0), will cure the cache staleness issue you are seeing the effects of. And it appears that the read serialization you disliked can be alleviated as described in section 2.2.1 .

You would likely get relief from your stale cache issue by calling sqlite3_db_cacheflush() after doing writes. It's worth an experiment to see how that performs relative to using "Read-Uncommitted Isolation Mode" and a single (shared) cache.

When experimenting, you might increase the cache size when there is only one rather than many that are not shared.

Are you sure that you are actually committing the transaction when you think you are committing the transaction and that Go isn't "helping you" by running the transaction on a separate thread such that it is not actually committed when you think it is?

Are you sure that your "read thread pool" is not "hanging open" a prior transaction?

I see that as likely, but have not read enough code or docs to see how SQLite determines that a page cache has become stale. But I find it hard to imagine that it would go to the persistent store to determine that, as that would reduce the performance of the cache significantly.

Since a transaction is required before any read can be made from the database, the action of starting a read transaction, as far as I can see, will check the data_version so that the cache can be marked stale (discarded) before the read transaction is started. Once a read transaction is started the database cannot be updated so there is no need to check the data_version again until the next transaction is started on that connection.

I am not sure about either, as I suspect the issue is caused by bugs in my code, but now I know where to start investigating it :-)

The go sql layer is indeed quite "thick" and there might be the case, but I believe it's way more likely to be caused by my application itself.

Thank you (and all the other folks) for the answers.

From my application's perspective, that's the case, as a read transaction starts (that's guaranteed by the go channel semantics) only after a commit has been performed, but I confess I'll have to debug it to figure out whether that's done correctly.

I noticed that the Go database wrappers are quite "thick", with many layers from the users till the calls to the database itself, and I'll try to understand better how it decides to start and commit a transaction (apart from the explicit "BEGIN" and "COMMIT).