That assumption is only true if the DB is continually used by different applications. The reality is more likely a large number of applications used by a relatively small set of users, so most of the time there won't be any application writing, and it would be extremely rare for multiple applications to write at the same time.

In reality sometimes access via an API is way too slow or is lacking flexibility.

And I don't get this: "(e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks)"

Isn't this why transactions exist in the first place?

While MVCC avoids deadlocks, you'll still get a lot of failed transactions if they all operate on the same data at the same time, which you'll have to retry, but I'll take that over data inconsistency

(But indeed, with uncoordinated development, deadlocks can occur. Still better than data corruption.)