The SQLite documentation itself mentions write concurrency as a limitation.
> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
It goes on to say that other databases provide more concurrency.
> However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
Yes: sqlite3 only allows a single open write transaction per database file. Whether this matters or not depends on your application. For a large class of applications, this isn't a real issue at all. By way of example: until a few months ago, we were concurrently merging state updates from tens of thousands of VMs across our global worker fleet into a single SQLite database. As has been said across this thread, SQLite works well as a backend for read-heavy applications. A great many CRUD apps are read-heavy!
If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
That's great it worked for you, but that's not a website backend. It would be foolish to run HN on SQLite for instance. I would go as far as to call it a vulnerability in such context, due to how easily it could be DoS'd.
Maybe you don't understand SQLite's locking issues? Having a bunch of different files that can be written to concurrently sounds like a better solution than SQLite for a social network backend.
> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
It goes on to say that other databases provide more concurrency.
> However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
https://www.sqlite.org/faq.html#q5