Clojurians Log v2

apologies, yes, that was quite a derail on my part 😅

vulnerable in that we saw it exhibit that behaviour

so: • tx 1 starts, increments the auto-inc serial counter • tx 2 starts, increments it again. tx2 sees the uncommitted version of the counter • tx 2 finishes writing its operations and commits • our indexer looked at the table, saw tx-id 2, indexes that, sets 'latest completed tx' • tx 1 commits • our indexer never sees tx 1, because we were then querying for tx > 2

ok so it still stays uniquely incremented except it's not in the order you expected it to be right?

not in the order the transactions commit, yeah

and that's even if you set the txs to be 'serializable'

(it's documented as such, just that it caught us out)

apologies, yes, that was quite a derail on my partnononooo... it's absolutely on the rails getting "time" right is everything in fact, I do not want to try this in PG (or any other client-server Current DB) because their transaction model is complex and nondeterministic (because of whatever concurrency control method they have) SQLite in WAL mode is dead-simple, which guarantees me monotonic auto-inc serial primary key in the lynchpin "world facts" table. • serial writes • in-process with the application • nonblocking concurrent reads

basically, by having all the parts separate (write queue, transactor, and data itself) /but unified in process/, i can solve the problem by avoiding the problem for as long as one machine's compute allows for a bog-standard SaaS app, this might work surprisingly well with a tenant-per-db model ... any especially busy tenant can get its own box

writer -> WAL -> DB -> .sqlite file
                 /|\
                readers

nb. I do this with awareness of how SQLite's rowid increment behaves https://sqlite.org/autoinc.html ... I can even avoid saying AUTOINCREMENT because the world-facts table is append-only