SQLite’s BEGIN CONCURRENT enhancement allows multiple writers to process write transactions at the same time, as long as the database is in “wal” or “wal2” mode. However, COMMIT commands are still serialized. When a write transaction is initiated with “BEGIN CONCURRENT”, the database is not locked until a COMMIT is executed. This means that multiple transactions started with “BEGIN CONCURRENT” can proceed concurrently. The system uses optimistic page-level-locking to prevent conflicting concurrent transactions from being committed. If a transaction being committed conflicts with other concurrent transactions, an SQLITE_BUSY_SNAPSHOT error occurs and the transaction cannot be committed. To maximize concurrency, it is important to have a large number of non-conflicting transactions. It is also recommended to use efficient locking mechanisms and avoid monotonic values for INTEGER PRIMARY KEY fields to improve concurrency. Additionally, certain types of indexes may need to be rethought to increase concurrency provided by page-level-locking.
https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md