Is it safe to have an application with multiple read/write threads who each have their own connection to a single sqlite database? Or is it better to have each thread send its read/write commands to a message queue where the main thread can read and process that?
Thanks,
Phil
Not related to threads SQLite isn’t going to handle multiple simultaneous read/writes well, it’s not a multi-user database.
Actually it does a decent job if you have many apps connected using separate connections
Now its not going to compete with the kind of heavy loads you can shove on Oracle, PostgreSQL, MS SQL etc it can handle a decent amount of concurrency as it sits.
It is serverless though so for concurrency you’re limited to apps/processes on the same machine
The guys at SQLite.org have documented “appropriate uses” and if you stick to that you’re unlikely to have issues
Orly?
I wonder where I picked up the notion.
[quote=303020:@Norman Palardy]Actually it does a decent job if you have many apps connected using separate connections
Now its not going to compete with the kind of heavy loads you can shove on Oracle, PostgreSQL, MS SQL etc it can handle a decent amount of concurrency as it sits.
It is serverless though so for concurrency you’re limited to apps/processes on the same machine
The guys at SQLite.org have documented “appropriate uses” and if you stick to that you’re unlikely to have issues[/quote]
SQLite v3 has gotten approval for use on board aircraft (for aircraft operations - not just things like entertainment) and they have very strict rules about the ability to have stability under all situations. And from what I remember when I talked to Dr Hipp (of SQLite fame) having a stable multiple app read/writing to the same database (datafile) must be very stable. Like @Norman Palardy said there is a “best practices” or “blue print” of the way to do it to achieve it.v
Now I have have an application that has two separate apps that write/read from the same database (file). I have a perl app that runs out of cron that does some web based pulling of data and placing data in the the database with the replace command. Then I have a Xojo app that reads/writes to the database every 100 milliseconds (was going to say constantly but technically it isnt). And never ever had a problem. Now that I think about it, I dont think I have turned on multiuser (WAL) in either app. Now that I write that, I think I need to go and check that out. I might need to fix it.
Helping others on teh forum this morning has lead to me seeing I have 2 different issues at hand (or potential issues).
It’s good to find out SQLite is more robust than I thought it was.
there is military devices that use SQLite. Cant say which ones but they do exist. It was a big win for Dr Hipp but cant talk about the win. I found out from someone in the government.
Wow, I can only imagine how hard it is to get certified for military/government use.
it is. and it is only certified to be used in certain places, doing certain things, using the “blue prints” from Dr Hipp.