SQLite allows multiple writers. The constraint is that only one of how writers can be actively writing at any moment in time. If there are multiple processes wanting to write, they take turns. SQLite prevents two or more writes from running concurrently, so there is nothing the application needs to do to implement this, other than responding to SQLITE_BUSY replies from failed (concurrent) write attempts and retrying after a short delay.

Why this constraint? Because SQLite is serverless. There is no central server available to coordinate concurrent writes.

At the lowest level of the stack, every database engine has this same constraint, as there is only one wire connecting the CPU to the SSD, and you cannot send multiple writes over the same wire at the same time. But in a client/server database, the server (in cooperation with the filesystem) is at hand to serialize the writes and prevent problems in ways that are not possible without a server. The server creates the illusion of concurrent writes by multiplexing the single write wire efficiently and making that multiplexing transparent to the application.