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