Comment by duped
2 years ago
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.
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.
2 replies →
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