Everytime I try to use SQLite I run into db locking issues where I seemingly have to try to run my query in a retry loop. Am I doing something wrong or does SQLite just not play nice in multi threaded contexts?
In that sense, I have the impression SQLite is different from other database software. You can usually get by with Postgres or MySQL (after they are set up) without looking at their docs.
I spent several hours reading the SQLite docs. It wasn't wasted time: actually learning SQLite made me a better professional. But for those thinking of using it on their side (or main) project, definitely understand how it works and what are the trade-offs involved.
Now, to answer your question specifically: it depends on a few factors. You can have concurrent readers (wal). With newer developments (wal2 + begin concurrent), you can also have concurrent writes as long as they happen at different pages.
If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely.
If you are blindly doing multi-threaded connections without understanding the implications, you also risk corrupting the database entirely.
This is true for the vast majority of cases, although there's at least one documented scenario where using modern SQLite coupled with an old threading implementation (the one that predates NPTL in Linux) may lead to database corruption.
I guess that's why I said "blindly": as a way to incentivize OP to look this up. It's not that the SQLite database is fragile, but rather that it expects to be used in a certain way, and if you don't, you risk corrupting it.
https://www.postgresql.org/docs/15/mvcc-serialization-failur...