yes but no... most database allow for at least as many parallel and concurrent writes as there are tables at a minimum.

The "lock on write" problem is that in MySQL i could run a OLAP pipeline for a few hours and have a fully functioning database with degraded perfomance, on SQLite the same pipeline would lock the database for the full hour. (there are surely ways to solve this (eg using the main db as read-only and a secondary db for writes or splitting the writes in incremental transaction), but it is not a "myth".