By the way, I noticed you're using SetMaxConnections(1), however in WAL mode (which you're using) SQLite does support writes that don't block reads, so you might benefit from allowing read concurrency (in theory).
SQLite only allows one writer at a time, so concurrent writes will fail with a "database is locked" (SQLITE_BUSY) error.
There are two ways to enforce the single writer rule:
1. Use a mutex for write operations.
2. Set the maximum number of DB connections to 1.
Intuitively, the mutex approach seems better, because it does not limit the number of concurrent read operations. The benchmarks show the following results:
- GET: 2% better rps and 25% better p50 response time with mutex
- SET: 2% better rps and 60% worse p50 response time with mutex
Due to the significant p50 response time mutex penalty for SET, I've decided to use the max connections approach for now.
[1]: https://github.com/nalgeon/redka/blob/main/internal/sqlx/db....
You can check the documentation [1], only some rare edge cases return this error in WAL. We abuse our sqlite and I never saw it happen with a WAL db.
[1] https://www.sqlite.org/wal.html#sometimes_queries_return_sql...
Where are the benchmarks?