[apitman]

The necessity of this sort of tribal knowledge kills a lot of the simplicity of sqlite for me. Honestly it seems to have a lot of footguns. I've tried to understand proper concurrent use of sqlite with Golang about 5 times and never come away feeling like I actually get it.

[nasretdinov]

With Go it's quite straightforward actually: use WAL mode + two connection pools, one for reads and the other, with MaxConnections set to 1, for writes. This way you should never encounter any concurrency issues, and Go will serialise writes for you too

[johannes1234321]

I have no experience in Go, but won't that be a full database lock and prevent transactions with more than one operation?

[nasretdinov]

Setting MaxOpenConns to 1 essentially limits the number of concurrently running (write) transactions to 1, which is exactly what we want. Whenever a concurrent thread wants to open a new transaction it'll have to wait.

Note that the application needs to be aware of that there are two pools — one for write operations and one for reads (the latter with no or high connection limit). The separation can be ensured on SQLite level too by adding ?_query_only=1 to connection parameters or setting the respective pragmas in the read-only pool.