Does that just do Database Level Conflict Checking?

Yes. It acts exactly like WAL mode in that regard. Readers operate on a snapshot and are never blocked, writers block each other. Upgrading a reader to a writer fails immediately if there is a concurrent writer, however BEGIN IMMEDIATE transactions never do.

One of the issues I have with the mvsqlite approach (beyond not being able to convince myself that it's correct — I'm sure it is I'm just unsure I could replicate it faithfully) is that I don't think the VFS gets any peak at the BEGIN CONCURRENT syntax, so suddenly all your transactions can fail because of write-write conflicts. It's not opt-in.