Hacker News Reader

Comment by duped | Hacker News Reader

What's wrong with PRAGMA journal_mode=WAL? It enables concurrent writes, at the expense of disk. Which to my understanding is the same as any other database backend with write ahead logging to enable concurrent writers.

Anecdotally, I've seen the WAL file grow way too large even after all writes have finished and should shrink, but that's manageable.

View on HN

WAL allows one writer concurrent with any number of readers. It does not allow two concurrent writers.

WAL does not help with "database locked" situations. At some point you will see them even with WAL enabled, and your application frontend code has to deal with the timeout and retry or whatever.

You just need to set busy_timeout > 0, and you'll basically never have database locked situations. It's just unfortunate, that it's not activated per default: https://sqlite.org/forum/info/7e456bf5544ab128

yeah, but that will slow down your app and may help in a short run, but you basically just shift the problem and make it even worse if your workload gets even higher.

You should only be waiting if someone else is writing. And yes, disallowing any concurrent writes, as SQLite does, is certainly going to be a bottleneck at some point, but unless you have a particularly write-heavy workload, it will probably get you pretty far.

Then use a different busy handler

I'm relatively new to the DB field, and have only really used SQLite; but it seems obvious to me that if you're doing a transaction, particularly a complicated one, then you should expect your transaction to fail occasionally due to concurrent changes, and be executing your transaction in a loop. This should be true for any database.

If you loop-retry all transactions which fail due to transitory effects, then you won't have a problem with "database locked" situations.

Abysmally documented, but this is what I use for golang + sqlite:

https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312...

EDIT: Typo

Reply on news.ycombinator.com