Conversation

Working on github.com/backtrace-labs made me learn wayy too much about sqlite. For example: this niche correctness issue

github.com

The change counter in the sqlite header isn't *always* updated · Issue #10 · jlongster/absurd-sql

I see "Once we’ve grabbed a readwrite transaction, we can read some bytes from the file which represent SQLite’s “change counter”. If that counter is the same as what we had when we reques...

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.

Replying to

and

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.

2:35 PM · Aug 13, 2021Twitter Web App

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.

Replying to

and

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.

Show replies