SQLite in WAL mode allows single concurrent writer and unlimited readers. Which is completely fine for a wide range of production use cases.

In practice, I've found that most SQLite write operations take low-milliseconds to complete - so even under a very heavy write load (assuming WAL mode) you're unlikely to see any contention between writes.

For my own stuff I maintain an in-memory Python queue of write operations and apply them in order against a single write connection - while running numerous other read-only connections to serve SELECTs.

SQLite is not at the top of the charts, but there's a looong ways to drop below it among the wide variety of desirable properties that it offers.