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
I'm pretty sure the best way to enable concurrent reads and writes for the application I see will be to add read-only snapshots at the VFS level. Pessimistic concurrency control is fine when it's only for writers.
1
Replying to
WAL mode + online backup API gives you concurrent read-only snapshots since it only holds a read lock. Can get a lot of mileage out of the online backup API feature.
sqlite.org/backup.html