いいねで順ソートするときに、モレや重複が生じてしまう問題について

Question

前提・実現したいこと

コメントをいいね順に取得しつつ、ページネーションを実装したいです。

発生している問題

しかしページネーション実行中にいいね数が更新された場合、ページネーションの取得内容にモレや重複が生じてしまいます。

例えば次の流れです。

該当のソースコード

まずコメントテーブルの CREATE と、初期値の INSERT をこうします。
like_count がソート対象のカラムです。

# コメントテーブル
CREATE TABLE `comments` (
  `ID`         bigint(20) NOT NULL AUTO_INCREMENT,
  `like_count` bigint(20) NOT NULL DEFAULT 0, 
  `created_at` datetime   NOT NULL,
  `updated_at` datetime   NOT NULL,
  PRIMARY KEY (`ID`)
);

# 初期値
INSERT INTO comments (`ID`,`like_count`,`created_at`,`updated_at`)
VALUES
(1,30,'2022-01-01 01:00:00','2022-01-01 01:00:00'),
(2,30,'2022-01-02 01:00:00','2022-01-02 01:00:00'),
(3,30,'2022-01-03 01:00:00','2022-01-03 01:00:00'),
(4,50,'2022-01-04 01:00:00','2022-01-04 01:00:00'),
(5,70,'2022-01-05 01:00:00','2022-01-05 01:00:00'),
(6,20,'2022-01-06 01:00:00','2022-01-06 01:00:00');

ここから2件ずつ取得するとして下記の SELECT を流すと３ページに分割されます。

SELECT ID, like_count, updated_at
FROM comments
GROUP BY ID
ORDER BY like_count DESC, ID DESC
-- ページネーション指定
# LIMIT 0, 2; # 1ページ目 →【ID=5,4 が取得される】
# LIMIT 2, 2; # 2ページ目 →【ID=3,2 が取得される】
# LIMIT 4, 2; # 3ページ目 →【ID=1,6 が取得される】

しかし、１ページ目の読み込み直後に下記の UPDATE がされた場合、2ページ目で
【ID=3,2 が取得される】 でなく
【ID=2,1 が取得される】 になってしまい、ID=3 が取得からモレるという問題です。

UPDATE `comments`
SET like_count = 20, updated_at = '2022-02-01 01:00:01'
WHERE ID = 4;

以上の問題はこちらで実行して頂けますのでよろしければどうぞ。
http://sqlfiddle.com/#!9/3d1ac7/1

試したこと

まず検索で次の記事を見つけましたが、いずれもソートカラムが可変でないケースに限るように見受けられます。

■Pagination(ページネーション)がずれる、抜ける問題を解決する
https://qiita.com/marnie_ms4/items/1dc51042844c85c30550

■次ページの取得
https://use-the-index-luke.com/ja/sql/partial-results/fetch-next-page

続いて OFFSET を用いず次のように WHERE でのアプローチを試みました。?には直前ページの最後のコンテンツのlike_countを入れ、ページ訪問日時をupdate_atに入れます。これにより重複は防げますが、新たなモレが生じてしまい実現できませんでした。

WHERE
( like_count = ? AND ID < ? ) OR 
( like_count < ? AND updated_at < ? ) 

補足情報（FW/ツールのバージョンなど）

MySQL は 5.7 を使用しております。
宜しくお願い申し上げます。

Comments

[yambejp]

見る感じ「そういうもの」だと思いますが何が問題なのでしょうか？

[nikuatsu]

「1ページ目を読み、2ページ目に行く前」というタイミングで like_count の UPDATE が実行されると、モレや重複や生じることを問題視しています。

モレの例は質問にある通りですが、念のためご回答への返信 2022/02/10 14:20 に記載致しました。

重複の例は（あなたほどの方なら想像に難くないとは思いますがこれも念のため）ご回答への返信 2022/02/10 14:28 に記載致しました。

Answer 1

思いついた方法として、
like数をあまり頻繁に更新しないようにして、更新時に前回のlike数も記録しておく。
ページング中に更新が入ったら、前回のlike数を参照する。
ページング中に2回以上更新が入る場合、諦める。
というのはいかがでしょう。

---

もう1つ案です。

likeが減ることは無いとして、ページを進む場合に限って、
「likeが前回取得したページの末尾以上 and 更新日時が前回取得した最終更新日時より新しい」
を条件に取得し、(SQL外の処理で)その中から今までに表示したものを除外すれば、漏れ分を抽出できそうです。

対象が多く更新が頻繁な場合には無駄なデータ処理が多くなりますが、上の案と違いリアルタイムのlike数を取れますし更新が複数入っても大丈夫です。

Comments

[nikuatsu]

ありだと思います。ありがとうございます。参考にさせて頂きます。

[nikuatsu]

もう1つの案ありがとうございます。参考にさせて頂きます。

Answer 2

見る感じ「そういうもの」だと思いますが何が問題なのでしょうか？
トランザクションで更新をとめるのは論外ですし
更新されても古い状態のデータを見続けるのは合理的ではありません。

あえてやるなら、次のページに移るときに最終更新日時を拾ってきて
「このデータは検索中に更新されました」とメッセージをだすくらいでしょうか

Comments

[nikuatsu]

>何が問題なのでしょうか？

ある人がページネーションを実行するにあたって取得されるコメントは、次のように分割されるのが本来意図している機能です。

# LIMIT 0, 2; # 1ページ目 →【ID=5,4 が取得される】
# LIMIT 2, 2; # 2ページ目 →【ID=3,2 が取得される】
# LIMIT 4, 2; # 3ページ目 →【ID=1,6 が取得される】

しかし「1ページ目を読み、2ページ目に行く前」というタイミングで件のUPDATEが実行されると、本来の意図と異なり、2ページ目の内容が次のようになってしまいます。

# LIMIT 2, 2; # 2ページ目 →【ID=2,1 が取得される】

つまり ID=3 が取得からモレるという問題です。

[nikuatsu]

また重複の問題も想像に難くないと思いますが、先のタイミングでのUPDATEがもし

UPDATE `comments`
SET like_count = 20, updated_at = '2022-02-01 01:00:01'
WHERE ID = 5;

だった場合に、3ページ目で ID=5 が再び取得されてしまいます。ID=5 は1ページ目で取得済みであるのに重複してしまうのです。