Hacker News Reader

Comment by nalgeon | Hacker News Reader

Yeah, it's explained in the code[1]

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....

View on HN

This is really not true in WAL mode with synchronous NORMAL, this was only true with the default journal mode and a lot of people are misusing sqlite because of that. You still have one writer at a time but you wont get the SQLITE_BUSY error.

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...

How about having two pools, one for writes only, and the other one for reads? SQLite allows you to open the DB more than in one thread per application, so you can have a read pool and a write pool with SetMaxConnections(1) for better performance. This of course also means that reads should be handled separately from writes in the API layer too.

Thought about it, decided to start with simpler and good enough option. The goal here is not to beat Redis anyway.

Well I agree, that's a good starting point. You probably won't be able to beat Redis with SQLite anyway :), although given that WAL mode allows for concurrent reads it might give it a large enough performance boost to match Redis in terms of QPS if the concurrency is high enough.

for what it's worth, the two pool approach is suggested here by a collaborator to github.com/mattn/go-sqlite3: https://github.com/mattn/go-sqlite3/issues/1179#issuecomment...

Have you tried setting BUSY_TIMEOUT and benched it? I believe it would be better too.

https://www.sqlite.org/pragma.html#pragma_busy_timeout

> The benchmarks show the following results

Where are the benchmarks?

Reply on news.ycombinator.com