TLDR; A deadlock is not a database failure any more than “row not found” is not a database failure. Modern database will detect a deadlock in nearly all cases and return an error to you - but careful app design will avoid deadlocks in most cases. Avoiding deadlocks does not mean you can avoid unexpected database errors at any time.
As @outlog say, it depends on the guarantees you require. A lot of applications consider the database as the source of truth as a given point in time. Since there is typically concurrent access and update to a database then the guarantees you require need to be considered in that context. Its not typically a major issue if the count of the number of comments on a post in a social system isn’t accurate. But as you said, when money goes missing its bad.
Typically what you are looking for is an appropriate level of transaction isolation. That means what state does the data base needs to be in when a transaction starts to update it. And what state is visible to other transactions when I am doing the update.
There isn’t a one size fits all and so while “optimistic locking” (which isn’t really a lock in the database sense at all) is a common pattern its definitely not appropriate for all use cases.
