MyISAM has reader/writer table locks[1] which meant that nobody could read while someone was writing to a table.
SQLite can be ran in two modes 1) database reader/writer lock or 2) in WAL mode[2] where readers do not block writers and writers do not block readers. Writers block each other though so there can only be one concurrent writer. WAL mode provides concurrency superior to what MyISAM did.
1. The locks could sometimes be bypassed by inserts, but not reliably so.
2. http://www.sqlite.org/wal.html
Sure but WAL in SQLite is 3 years old. Wordpress is 10 years old. MySQL with MyISAM is a lot older than that. I personally happen to think almost everything about Wordpress is wrong. It's just hard to blame them they didn't start or subsequently adopt SQLite. I doubt it's ever been as much as on their radar.
I believe you can approximate table-level locks in sqlite by splitting your database into multiple files. Transactions across multiple databases are atomic (provided you don't use WAL), so you're still in a fairly safe place. However, I'm not sure how practical it is to split a database into lots of files; or whether the sqlite engine deals efficiently with queries that span many databases, for instance - I've never actually done this.
But if you really need a particular table to be writable without locking the rest of the database, it's probably a fairly decent workaround.
Agreed, and I do not think the lack of SQLite support in Wordpress has anything to do with what SQLite can or cannot do. Only that it was written by LAMP developers who do not care for databases other than MySQL.
SQLite can be ran in two modes 1) database reader/writer lock or 2) in WAL mode[2] where readers do not block writers and writers do not block readers. Writers block each other though so there can only be one concurrent writer. WAL mode provides concurrency superior to what MyISAM did.