Use SQLite WAL mode

[gisborne]

The Cozo documentation for the SQLite engine says:

SQLite… is effectively single-threaded when write concurrency is involved

There is an option when using SQLite that makes this very much false. It switches SQLite to use a Write-Ahead Log, which means one can happily open the same SQLite database from multiple separate process that can read and write simultaneously.

https://www.sqlite.org/wal.html

[zh217]

Currently there is an RWLock on SQLite connection in Cozo. There are various reasons, one of which is that we only open a single conneciton to the SQLite database (opening multiple connections to the same database will start failing after a pretty low number, I think 1024). The current design with the lock in place serializes writes regardless of WAL-mode support, but in exchange can handle much more read concurrencies and read throughput, which is the main focus of the SQLite engine (single write, multiple readers).

And with SQLite WAL enabled, the data files are no longer single files. In this case why not just use the RocksDB engine?

[gisborne]

I can't speak to whether to use RocksDB (although the lower resource use, say in a mobile app, would I think still favour SQLite).

If I contemplate what a regular website with thousands of concurrent connections, are those all running through a single process? Can I use multiple separate Cozo processes sharing a data store?

I've been meaning to ask more about concurrency, actually.