The timing of this example is tricky because the two update statements execute concurrently (which is only possible under read committed isolation; under serializable isolation it's much more like what you're describing).

Here's a full timeline in PG (U1 for first update, U2 for second update):

0. U1 begins executing, establishes read snapshot, starts pg_sleep(5).

1. U2 runs to completion.

2. U1 wakes up after 5 sec, scans `player` using snapshot from step 0.

3. U1 filters `team = Gophers`, gets 4, 5, 6.

4. U1 locks 4, 5, 6.

5. U1 performs EvalQualPlan: re-scans latest version of those locked rows, which sees U2's write to 4 but not to 3.

6. U1 performs EvalQualPlan: re-filters those locked rows using latest version, gets 5, 6.

7. U1 writes new versions.

CRDB is easier to reason about: after U1 wakes up from the sleep, it sees that it conflicts with U2 and simply retries the entire statement.