3 ms·

I've had keyset paging fail for me in MySQL, once or twice in every ten thousand rows, in a "chaser" process that was trying to keep up with a table under heavy

I've had keyset paging fail for me in MySQL, once or twice in every ten thousand rows, in a "chaser" process that was trying to keep up with a table under heavy write load. I obtained data with holes in it; I think MySQL had allocated the autoincrement IDs to in-progress transactions but not written the rows.

lukaseder 13y ago
But then, MySQL failed you on keeping autoincremented IDs strictly and globally incrementing to all readers. That is not related to keyset paging. It just happens that you chose to `ORDER BY` a non-stable ordering criteria. In principle, you can order by any (possibly composite) key when performing keyset paging. Actually, all I've just said is equally true for `OFFSET` paging.
- JulianMorrison 13y ago
  No, they were stable all right, once the writes had settled down. The trouble is that sometimes it would write rows with an ascending ID sequence like 1 1,2 1,2,4 1,2,3,4 …and a read transaction that captured "1,2,4" would try reading the next ID > 4 and never see 3. Evidently it allocated the ID and space for row 3 before writing it and exposing it to the other transactions. And sometimes that turned into a race condition.
  - lukaseder 13y ago
    Aha, I get it now. Yes, that's a little awkward. Although, 3 would re-appear when going back again but that might be even more confusing. But with OFFSET, you'd just have (frequently) shifting windows, right?
    - JulianMorrison 13y ago
      I was trying to stream the table entries into a "tallying" type process. And of course it came out with the wrong numbers. Frustrating. I finally replaced the whole "chaser" process with a repeated full table scan with SQL aggregate functions. Wildly inefficient, but reliable.
schrodinger 13y ago
Good point, yea identity values are generated immediately within a transaction, but may not be committed until later. There is no guarantee that a higher identity value means that the row was committed later than a lower one.
- lukaseder 13y ago
  One Schrodinger's cat in a box is another Schrodinger's identity in a commit. You just cannot be sure ;-) (sorry, couldn't resist)

lukaseder 13y ago

But then, MySQL failed you on keeping autoincremented IDs strictly and globally incrementing to all readers. That is not related to keyset paging. It just happens that you chose to `ORDER BY` a non-stable ordering criteria. In principle, you can order by any (possibly composite) key when performing keyset paging. Actually, all I've just said is equally true for `OFFSET` paging.

JulianMorrison 13y ago
No, they were stable all right, once the writes had settled down. The trouble is that sometimes it would write rows with an ascending ID sequence like 1 1,2 1,2,4 1,2,3,4 …and a read transaction that captured "1,2,4" would try reading the next ID > 4 and never see 3. Evidently it allocated the ID and space for row 3 before writing it and exposing it to the other transactions. And sometimes that turned into a race condition.
- lukaseder 13y ago
  Aha, I get it now. Yes, that's a little awkward. Although, 3 would re-appear when going back again but that might be even more confusing. But with OFFSET, you'd just have (frequently) shifting windows, right?
  - JulianMorrison 13y ago
    I was trying to stream the table entries into a "tallying" type process. And of course it came out with the wrong numbers. Frustrating. I finally replaced the whole "chaser" process with a repeated full table scan with SQL aggregate functions. Wildly inefficient, but reliable.

schrodinger 13y ago

Good point, yea identity values are generated immediately within a transaction, but may not be committed until later. There is no guarantee that a higher identity value means that the row was committed later than a lower one.

lukaseder 13y ago
One Schrodinger's cat in a box is another Schrodinger's identity in a commit. You just cannot be sure ;-) (sorry, couldn't resist)