See I don't buy that it's not "the main one we care about". What kind of insane software is expecting Sequential Consistency for shared mutable data across processes? That's what I still don't have a clear picture of. I think what I've seen here is merely a race condition, which is just not a big deal.

Humans can't reason about data races because they're contrary to our normal understanding of the world, but an ordinary race condition isn't like that. Earlier I saw there was the nice cheese at the grocery store, but I wasn't sure if they had fresh bread so... I kept walking, when I reached the bread aisle I bought bread and then I went back but nope, somebody else bought the last of the nice cheese. Oh well. Race condition.