But that does not carry over to writes, since we want our transactions to
change the current state of the database, not some old snapshot.

I do want to emphasize that doing this in the primary-key workloads I've discussed so far violates both Snapshot Isolation *and* Repeatable Read. It's not how these consistency models work in abstract, or how other major databases work in practice. Whether MySQL users *want* this weird behavior is, well... I guess that's up to y'all, haha. ;-)

 Consider a
simple example like this, where two transactions T2 and T3 update the same
row in parallel:

  T1: BEGIN
  T1: INSERT INTO t1 VALUES (1,1), (2,2), (3,3)
  T1: COMMIT

  T2:   BEGIN
  T2:   SELECT * FROM t1                 # S1: (2,2) ?

  T3:     BEGIN
  T3:     UPDATE t1 SET b=12 where a=2
  T3:     COMMIT

  T2:   SELECT * FROM t1 WHERE a=2       # S2: (2,2) ? (2,12) ?
  T2:   UPDATE t1 SET b=-1 WHERE b>=10   # U1: update the row?
  T2:   SELECT * FROM t1 WHERE a=2       # S3: (2,2) ? (2,-1) ?
  T2:   COMMIT

From a usability point of view, what should the result of transaction T2 be,
assuming "REPEATABLE READ" mode?

If you interpret MySQL REPEATABLE READ as SI, T2 must observe (2, 2). Its snapshot must have been taken before T3 committed, since it performed a read before T3 started. Its update affects nothing, it selects (2,2), and commits successfully.

If you interpret MySQL REPEATABLE READ as RR, and interpret `a` as a primary key... this might come down to how you interpret the results of selects *not* by primary key: whether they're purely a predicate read, or also manifest as item reads. If we go with the item read interpretation, T2's first select should observe (1, 1), (2, 2), (3, 3)--that seems obvious. This means T1 wr T2 and T3 rw T2, since T3 writes the second version of the tuple (2, 12) and T2 observed the previous version. These are item dependencies, not just predicate dependencies, since we actually read the tuple values.

T2's second select is a primary-key read, and it must select (2, 2). If it observes (2, 12), that would imply T2 wr T3, and we'd have a G2-item cycle between T2 and T3: T3 both fails to observe, and also observes, T2. You might also count that as fractured read, which would violate read atomic.

T2's update I'm less sure of. A predicate write without any actual item reads is unconstrained in RR, so it's free to either update or not update the row--I think either is legal. The following SELECT should, I think, observe either (2,2) if the update fails (otherwise we have G2-item), and (2, -1) if the update succeeds (otherwise we violate internal consistency).

From my (limited) reading of Kyle's analysis, the discrepancies he finds all
seem to involve this basic kind of read/write mix. It would be interesting
to know if using FOR UPDATE on the SELECTs would remove these descrepancies,
or if there are more subtle issues (/bugs) remaining around this.

Ah, there's a key difference here. Your example involves predicates, and RR explicitly does not constrain predicates. My report involves only access by primary key, and there RR is quite clear: MySQL's behavior is illegal.

--Kyle