Working on github.com/backtrace-labs made me learn wayy too much about sqlite.
For example: this niche correctness issue
Conversation
Replying to
It's not well known you need BEGIN IMMEDIATE TRANSACTION instead of BEGIN TRANSACTION for read/write transactions if you're doing things in parallel.
In WAL mode, another writer can write while you're reading, and then your transaction isn't allowed to upgrade to write lock.
1
1
It immediately gives a busy error when your delayed write transaction attempts to upgrade to a write lock but a parallel write occurred potentially invalidating your reads. It's quite an annoying gotcha if you don't realize what's happening since it looks like it's timing out.
1
1
Replying to
That failure mode can only happen in WAL mode.
With a rollback journal, active read transactions prevent writers from committing a transaction, so there can't be any conflict.
2
Replying to
I treat WAL mode as the default and the traditional rollback journal as something to use in exceptional cases even though those aren't the actual defaults.
I only tend to want the traditional rollback journal for databases generated entirely in advance.
1
WAL also avoids the issue of readers blocking writes indefinitely. It's fast enough that lack of fine-grained locking isn't usually an issue for me.
Online backup API also needs a read lock for the whole operation unless you allow it to be interrupted by writes indefinitely.
1
1
WAL makes SQLite so much more usable for all kinds of use cases where it previously would have been ridiculous to use it instead of PostgreSQL.
I think people run into that delayed write transaction issue and think they're getting timeouts far before there's any scaling issue.
Replying to
Yeah, agreed… but once you tag all potential transactions that can write, I feel like VFS-level MVCC avoids a lot of the ops issue with WAL mode, like huge (much larger than the db file itself) WAL files that never shrink.