I think the issue here is depending on transactions/concurrency in a locally running SQLite

The fact that SQLite supports those is of course great, but maybe there are easier ways of serializing access to it.