SQLite Forum

Multiple read and write calls of web service to access SQLite

Multiple read and write calls of web service to access SQLite

(1) By abigale (abigalexinli) on 2020-07-23 22:25:08 [link] [source]

I have a web service that provide access to a SQLite database. There could be multiple calls at any time, some are readers and some are writers. I would like the reads to be in real time and the writes can wait in a queue to be executed one by one. The write don't have to be concurrent but have to be reliable. I see SQLite supports multiple concurrent reads and one write at a time and multiple writes can queue up.

I have a few questions below. I am sorry if some questions are naive.

  1. How do I implement multiple read and write? Do I just use WAL? Do I have to implement a queue to hold all the writes myself? Is there an example somewhere?

  2. I see from https://www.sqlite.org/wal.html that WAL only support this when all the processes are on the same host computer, but not over a network. Since my database is accessed by a web service, that means it won't support multiple concurrent read, right? Is there a way to make it work for my case?

  3. If there is a way to make it work, how to make sure the writes are reliable? (I mean, even when the queue is long, all writes will still be reliably conducted.)

Thanks in advance!

(2) By Warren Young (wyoung) on 2020-07-23 22:41:05 in reply to 1 [source]

The high-level answer to your questions is to set appropriate timeouts so that SQLite's normal retry behaviors always result in proper write queueing without losing data, up to the point that your app is overloaded, so that you start running the timers out.

Since that latter basically amounts to a DoS attack, dealing with that case is best done outside of SQLite, not by setting the timers to stupid-long values.