3 ms·
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 happe
by 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 agoNo, 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 agoAha, 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 agoI 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.