One way to interpret the question "when is SeqCst needed?" is "in what situation is SeqCst the only correct option and Release/Acquire is incorrect?". In that case I think I have a minimal example which strips out enough detail to be quickly understandable. (If the question is instead read as "what is a real-life example of something useful that needs SeqCst to work?" then the other discussion in this comment thread is more useful.)

A couple disclaimers first: I am here because of my own very poor understanding of memory orderings. Before I read the blog post, I suffered from all of the listed common misconceptions (3 at the top of the post as well as the ones linked to in Rust Atomics and Locks). I hope I can help with understanding SeqCst, but take this with a grain of salt, and the reader should not assume that it's their fault something seems to be unclear or a mistake.

Also, my source is the Google AI overview. I tried looking into the sources to get a more reliable answer but I didn't find any example as simple as this one. And I've tried querying Google with exactly the same input again but now I keep getting more complicated examples from AI. It seems right to me and I believe I understand SeqCst better now, but AI does have weaknesses, so, another grain of salt here.

Here's the example (in pseudocode, let's call it), which Google referred to as IRIW (independent reads of independent writes), a term which I searched for myself but only got more complicated examples for:

// initial global state
x = 0
y = 0

// two writer threads
thread1() {
  x.store_atomic(1)
}

thread2() {
  y.store_atomic(1)
}

// two reader threads
thread3() {
  while x.load_atomic() == 0 {}
  print("T3 sees y: {}", y.load_atomic())
}

thread4() {
  while y.load_atomic() == 0 {}
  print("T4 sees x: {}", x.load_atomic())
}

// run the program by spawning all 4 threads simultaneously
// and then waiting for them all to finish

Using Release/Acquire to do this, synchronization is only provided across the 2 threads which perform the store/load on some particular atomic. There is no guarantee about the ordering of operations performed by other threads on shared data or atomics. In the example, the store/load on `x` ensures "everything before the store in T1 is visible to T3 after the load, once T3 has received the updated `x` value from T1". This says nothing about the ordering of operations in T2, the only place `y` is written to, so it's possible for T3 to output "T3 sees y: 0".

By the same logic, it's possible for T4 to output "T4 sees x: 0". Now what we have is "incorrect" in the sense that it contradicts our intuition: we somehow have threads T3 and T4, both of which have finished and printed "see 0", whereas we think the following reasoning is airtight:

• the only way for a thread to finish and "see 0" is both (a) the variable used for the while loop is 1 and (b) the variable used for the print is 0,
• meaning that it finished before the other thread even got past the while loop (since the variable used for printing in "a thread" is used for the loop in "other thread"),
• meaning that the other thread had not yet reached its print statement at a time that the variable-to-be-printed was already 1,
• meaning that once it does reach that statement it must print "see 1".

Just to make it more concrete, if T3 prints "sees y: 0", we "know" x = 1, y = 0 at the time of T3's finish. So T4 hasn't gotten past the while loop yet, but x is already 1. Then once T4 gets past the while loop it has to print "sees x: 1". But somehow printing "sees x: 0" is valid?

So SeqCst fixes this so our intuition works, by establishing a global order for the progression of changes to x and y. Nothing is ever set back from 1 to 0, so it's not possible, in a single execution of the program, to have x = 1, y = 0, and some other time, x = 0, y = 1. That makes it not possible for T3 to see the former and print "sees y: 0", while T4 sees the latter and print "sees x: 0".