Yes, I know how it works. The big ones cache the data and write the transactions in the log, to be processed. So, while the actual access has locks, nothing keeps you from pumping all the tables full of data through multiple connections. Although trying to read such a table (or a query that accesses multiple of those tables) can take a while, as it first has to be indexed and it has to traverse the cache to get the data.
Of course, when you change the structure of the tables, things get complicated. That's why I think the SQLite error that "the schema has changed" in my case is wrong, because the structure doesn't change. It's just that the queries hold stale data. So, it seems SQLite only keeps a single state.
So, in my specific case, I think FireBird would work. But I'm not going to install a database server on each client. And if I'm going to use a central one, I prefer a PostgreSQL on an S3 slice. That way, I don't have to manage it.