As Johnathon pointed out, it's like properly using synchronized or volatile half the time. I don't call it sometimes not working as arbitrary. I call it expected for not following the rules of the system.
Which may be a technically valid argument against the compiler/database system, but it's not a valid argument for defending the system as a whole: if a standard allows arbitrary execution instead of bailing out on non-standard (ambiguous) input, it is unreliable.
Getting access to things like concurrent locks is HARD to get right. That is why there are so many simple languages that don't let you touch concurrency.
Doesn't mean there is no need for it in the world, and no one should be able to use it.
If I can't see the child, should I continue to give them direction, or tell them to stop?
In this use case, the database makes changes to data without knowing what is correct and what is harmful. That is not the user's fault. It's a code choice.
Like it was said a couple of time already, all of your complaints about C* would work just as well for locking mechanisms in most popular languages.