I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions.
Additionally, sqlite has a sophisticated json extension.
> it can only ever do one write or read to the database predictably
WAL mode addresses this, allowing an arbitrary number of readers with a writer.
For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations.
More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...