Can open SQLite connections become corrupted or otherwise unusable over time?
(1) By anonymous on 2022-09-06 15:53:36 [source]
Hello,
If that question concerned a client-server database system operating over a network, then the answer would most certainly be yes.
But what about SQLite? Say my application opens a database from file on local disk and establishes one or more connections. The application is the only process that works with the database. Can my application be sure that, once opened and until explicitly closed, the connections cannot go corrupt or become otherwise unusable?
Is it a good practice to attempt to open a new, clean connection on every query?
Thank you.
(2.1) By Ryan Smith (cuz) on 2022-09-06 17:01:04 edited from 2.0 in reply to 1 [link] [source]
Is it a good practice to attempt to open a new, clean connection on every query?
No, that's the opposite of good practice.
...the connections cannot go corrupt or become otherwise unusable?
The question sounds very absolutist, so I'll say this:
If the question is merely whether a connection to a single file that is opened, and the file itself is not touched by another connection on a somewhat stable OS... then sure, nothing bad can happen to it, you should be able to read/write from/to it until our Sun becomes too hot.
If your question tries to establish absolutes... then I can name a myriad of things that might cause problems, anything from unstable OSes, or File Systems, or using it on containerized storage, or bad cable connection to drives, or cosmic rays, power-dips or being close to strong magnetic flux or RF inducing heavy machinery... the list goes on, but you get the idea: It should be just fine in "normal" operation in a "normal" environment.
EDIT: Stephen's answer is even better, explaining why reconnecting per query is a bad idea. I'll also add that you should probably, in your software, detect file access failures (perhaps subsequent to getting read/write errors from SQLite's API) and then try to reconnect, if/when needed. Not much more that can be done.
(3) By Stephan Beal (stephan) on 2022-09-06 16:53:12 in reply to 1 [link] [source]
Can my application be sure that, once opened and until explicitly closed, the connections cannot go corrupt or become otherwise unusable?
These are fallible computers running fallible software on fallible storage media. There are no guarantees.
Is it a good practice to attempt to open a new, clean connection on every query?
No, that's bad practice - it's a huge performance hit. An sqlite connection will never go corrupt just from sitting around doing its job. If it gets corrupt, its likely either because of storage failure (be it hardware or another process modifying the db), hardware failure (e.g. RAM), or a your own app corrupted its own memory.
(4) By Donal Fellows (dkfellows) on 2022-09-07 10:18:30 in reply to 1 [link] [source]
Is it a good practice to attempt to open a new, clean connection on every query?
Great Scott, no Marty!
I have one connection per thread in my main thread pool, with that connection persisting while the thread to which it is bound endures. That's definitely fast and works really well. (Classic connection pools don't work that way, but that's because they really assume they're holding network connections.)