As I understand it, there can be concurrent writes as long as they don't touch the same data (the same file system pages, to be exact). Also, the actual COMMIT part is still serialized and you need to begin your transactions with BEGIN CONCURRENT. If two transactions do conflict, the later one will be forced to ROLLBACK although you can still try again. It is up to the application to do this.

See also https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...

This type of limitation is exactly why I would recommend "normal" server-based databases like Postgres or MySQL for the vast majority of web backends.

Also just a note: BEGIN CONCURRENT is not in mainline SQLite releases. You need to build your own from a branch. Not a huge deal but just something to note.