4 ms·
> Only send writes from a single process depending on your use case, using multiple writer processes can be fine, e.g. multiple writer processes storing period
by tkhattra 4y ago
> Only send writes from a single process
depending on your use case, using multiple writer processes can be fine, e.g. multiple writer processes storing periodic metrics (once per minute) into a single sqlite database. the occasional "database is locked" error (it does happen a few times a day) is handled by simply returning and trying again at the next interval. that's fine for this particular use case - the metrics for the last interval are just delayed a bit.
- bawolff 4y agoOne thing that confused me initially with sqlite - if you want it to wait for writing locks to be free instead of erroring on write contention, you have to start write transactions with BEGIN IMMEDIATE. Since if you start with a read transaction that gets upgraded to write in the middle of it, you can't really wait for locks because you cant have a consistent snapshot of an old version of the DB that is read/write.
- tkhattra 4y agoyes, we use BEGIN IMMEDIATE with a 2 second busy timeout. that has a worst case wait time of 2 seconds, but the 95th percentile wait time is around 100 to 1000 microseconds and the 99th percentile is around 1 to 10 milliseconds. for a high throughput and/or low latency service, a worst case wait time of 2 seconds might be unacceptable, but it's fine in our case.