How does this help with the second issue, the write locks?
Then writers queue up, while readers are unimpeded.
(in general you _really_ should use WAL mode if using sqlite concurrently, you also should read the documentation about WAL mode tho)
This only gets “worse” as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left, because they didn't line up?
And, if you have a single limited pool, your readers will now be stuck waiting for an available connection too (because they're all taken by sleeping writers).
It's much fairer and more efficient for writers to line up with blocking application locks.
It's fixable by periodically forcing the WAL to be truncated, but it took me a lot of time and pain to figure it out.
If I had a blog, I'd be writing about it.
They do point out the risks here: https://sqlite.org/wal.html#avoiding_excessively_large_wal_f...
sqlites design makes a lot of SQL concurrency synchronization edge cases much simpler as you can rely on the single writer at a time limitation. And it has some grate hidden features for using it as client application state storage. But there are use-cases it's just not very good at and moving from sqlite to other DBs can be tricky (if you ever relied on the exclusive write transaction or the way cells are blobs which can mix data types, even it it was by accident)
In the end I wrote an external process that forced a checkpoint a few times a day, which worked. I came across other exasperated people in various dark corners of the Internet with the same symptoms.