> (e.g. if you have multiple applications writing to your database in transactions then you're virtually guaranteed to get deadlocks).

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.