One 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.
yes, 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.
