[mastre_]

I have one of the simplest toy services you can envision and am already running into a problem. Basically, I'm logging a temperature from a sensor to a SQLite db every minute or so (whenever it sends the reading). Sometimes I run a query manually thru the SQLite CLI to look into the data -- the query tabulates about 1.5 years' worth of data, by month and with averages, and can take about 20-30s as it's all running on an RPi3. As far as I can tell SQLite locks the entire table instead of just the records involved in the read, if one of my readings, new data to be inserted, happens to come in during me running the query I get "database locked" and the write fails.

Yes this is a heavy query that would not make it to a production system, still I am surprised that load placed on a sqlite db by "hundreds of thousands of concurrent users" would not surface problems due to this simple detail.

[jasonjayr]

Have you tried journal_mode=wal?

https://sqlite.org/pragma.html#pragma_journal_mode

This lets sqlite read w/o a write lock (among other things)

Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.

[mastre_]

Will give that a shot, thanks!

[philipyoungg]

Please update us about this!