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?
SQLite is a great but complex software. In order to properly use it, you do need to read the documentations, guides and resources available at the official website. If you don't, you will shoot yourself in the foot.
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.
> SQLite has always been able to handle multiple processes and threads
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.
This is true for every SQL database. Push conflicting transactions hard enough and you'll need a retry loop. In Postgres you'll see row-level MVCC detecting a write conflict and have the exact same end result. SQLite's locking is just coarser grained, and tends to trigger with less load.
