I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/
(On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)
(On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)