MySQL道普請123回のUPDATE前にロッキングリードの話を読んでの感想と実験（追試）の結果

　gihyo.jpの連載「MySQL道普請便り」の最新回（第123回）は、ロッキングリード(SELECT ～ FOR UPDATE)のつかいどころのお話が興味深かったです。

　乱暴に要約すると、例えば 5000枚のチケットを捌くUPDATEは、いきなり UPDATE するのではなくて、SELECT ～ FOR UPDATE でロックを取ってからUPADTEすると、同時実行数が大きくなっても遅くならないぞ、という内容です。人気のあるチケットの販売って、瞬時に大量の更新が走りますからね。　まさにチケットを捌くと、あっという間に砂漠みたいに何もなくなります。ひと仕事終えた人たちはデザートでもどうぞ。砂漠だけに。

　想像ですが、これは、いきなりUPDATEをするとロック競合が発生してしまって、待ちがどんどん大きくなっていくところを、敢えて、より「軽い」SELECTでロックだけ取って（取れない場合は待たずにすぐにやめる）、その後にロックが取れた人だけ悠然とUPDATEをすることで、ロック待ちが発生しなくなる、とか、そんな感じなのですかねぇ。

試してみた

　ということで、自分のところでも試してみました。8.0.20 on linux。

テーブル作成。

DROP TABLE IF EXISTS reserve_ticket;
CREATE TABLE `reserve_ticket` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `ticket_type` int NOT NULL,
  `user_id` bigint DEFAULT NULL,
  `update_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tickettype_userid` (`ticket_type`,`user_id`)
) ENGINE=InnoDB;

　データ作成の前に、cte_max_recursion_depth の最大値が1000では足りないので、上げておきます。

mysql> SELECT  @@cte_max_recursion_depth;
+---------------------------+
| @@cte_max_recursion_depth |
+---------------------------+
|                      1000 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SET @@cte_max_recursion_depth = 10000;

　データ挿入（まだ売れていないチケット5000枚）と確認。

INSERT INTO reserve_ticket (ticket_type, update_at)
  WITH RECURSIVE t AS (SELECT 1 as i UNION ALL SELECT i+1 FROM t WHERE i<5000 )
  SELECT 1200, null FROM t;

COMMIT;

SELECT * FROM reserve_ticket;

　bash のほうで、以下の2つのSQLを、mysqlslap を使って実行。
SQL1が単純にUPDATEをするもの、SQL2がロッキングリードをしてからUPDATEするもの。

SQL1=`cat << EOS
BEGIN;
UPDATE reserve_ticket SET user_id=1 WHERE ticket_type=1200 AND user_id IS NULL;
COMMIT;
EOS`;

mysqlslap --host=localhost  -p --port=3306 --user=root  --query="$(echo $SQL1)" --concurrency=50 --number-of-queries=5000 --create-schema=test

SQL2=`cat << EOS
BEGIN;
SELECT @id := id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE;
UPDATE reserve_ticket SET user_id=1 WHERE id = @id;
COMMIT;
EOS`;

mysqlslap --host=localhost  -p --port=3306 --user=root  --query="$(echo $SQL2)" --concurrency=50 --number-of-queries=5000 --create-schema=test

実行は、毎回、テーブルを DROP/CREATE して発売前チケット5000枚をINSERTしてから行いました。
　結果は、、、、、、

　
　
SQL1（単純UPDATE）（2回やりました）：

Benchmark
        Average number of seconds to run all queries: 5.894 seconds
        Minimum number of seconds to run all queries: 5.894 seconds
        Maximum number of seconds to run all queries: 5.894 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

Benchmark
        Average number of seconds to run all queries: 7.749 seconds
        Minimum number of seconds to run all queries: 7.749 seconds
        Maximum number of seconds to run all queries: 7.749 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

　
　SQL2（SELECTでロック取ってからUPDATE）（2回やりました）：

Benchmark
        Average number of seconds to run all queries: 23.950 seconds
        Minimum number of seconds to run all queries: 23.950 seconds
        Maximum number of seconds to run all queries: 23.950 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

Benchmark
        Average number of seconds to run all queries: 24.234 seconds
        Minimum number of seconds to run all queries: 24.234 seconds
        Maximum number of seconds to run all queries: 24.234 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

　あれっ！？　あれれっ？？？？

ということで、今日は「ちょっとそのまま動かしてみるくらいなら時間取れるかな」という感じで始めたものなので、これを追求する時間は取れず。　必要なコマンド類は上に全部揃っていますので、どなたか続きをお願いします（笑）。
　少し考えてみた程度では、何が道普請の実験結果との相違に寄与しているのか、まったく見当もつかず。
バージョン？　サーバのパラメタ？　他に動いているプロセス？　メモリに入っちゃうとかそういうやつ？　操作した人の思いやりの心？　ストレージ（HDD/SSD）？　うーむ。

　ということで、煮え切らないまま、今日の日記はおしまいです。

追記

　という日記を公開したら、道普請著者の北川さんから速攻でコメントいただきました。

あー！！すいません！！道普請記事のmysqlslapサンプルにlimit 1が抜けてしまっていました！！
— kentarokitagawa (@keny_lala) 2020年6月16日

　ぱっと見て「あ、ここおかしいわ」って分かるの、（自分の記事とはいえ）すごいです。ちょっと感動しました。　
　そんなわけで、「遅くなってほしかったクエリ」は、最初の一発で5000件すべてupdateして、残りは（nullの行がひとつもないので）何もしていないという実験を行っていたのでした。そりゃ速い（笑）。
　
　
　LIMIT 1 を追加しての再実験です。

SQL1b=`cat << EOS
BEGIN;
UPDATE reserve_ticket SET user_id=1 WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1;
COMMIT;
EOS`;

mysqlslap --host=localhost  -p --port=3306 --user=root  --query="$(echo $SQL1b)" --concurrency=50 --number-of-queries=5000 --create-schema=test

　
　
　結果は（これも2回実行）：

Benchmark
        Average number of seconds to run all queries: 23.837 seconds
        Minimum number of seconds to run all queries: 23.837 seconds
        Maximum number of seconds to run all queries: 23.837 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

Benchmark
        Average number of seconds to run all queries: 24.036 seconds
        Minimum number of seconds to run all queries: 24.036 seconds
        Maximum number of seconds to run all queries: 24.036 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

　おお！！！　「SELECT～FOR UPDATE」と同じくらいの時間になりました！！！
・・・って、これじゃぁまだ足りないんですよね。もっと競合起こして遅くなりまくる状況を再現したいので、たぶん私の実験では、遅くなれてない（変な言い方ですが）ような気がします。この状況での律速って何なんだろう。　メモリでもストレージでもないような気がしていますが。。

追追記

　という追記を終えて公開した途端、twitterで更なる情報をいただきました。

そして、mysqlslapのSELECT->UPDATEのほうもいちばん大事なSKIP LOCKEDの記述が抜けておりました。。。なんてことだ....お恥ずかしい限りです。。
— kentarokitagawa (@keny_lala) 2020年6月16日

　おいこら！！！　いや、、、もう、北川さんに言っているというよりも、記事の内容を理解して、自分で気づけよ！！というツッコミです。　ここがキモじゃないですか！読んで、内容理解してないってことじゃないですか（自分に対して）。

　ということで、気を取り直して、SELECTでロック取得できなかった時にはスキップしちゃう（その結果、UPDATEのWHEREにマッチするものがないのでUPDATEはすぐ終わる）という、本来やりたかった実験です。

SQL2b=`cat << EOS
BEGIN;
SELECT @id := id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE reserve_ticket SET user_id=1 WHERE id = @id;
COMMIT;
EOS`;

mysqlslap --host=localhost  -p --port=3306 --user=root  --query="$(echo $SQL2b)" --concurrency=50 --number-of-queries=5000 --create-schema=test

　
　
　結果（これもテーブル作り直して2回実行）：　

Benchmark
        Average number of seconds to run all queries: 3.544 seconds
        Minimum number of seconds to run all queries: 3.544 seconds
        Maximum number of seconds to run all queries: 3.544 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

Benchmark
        Average number of seconds to run all queries: 3.714 seconds
        Minimum number of seconds to run all queries: 3.714 seconds
        Maximum number of seconds to run all queries: 3.714 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

　やったね！たったの4秒弱で完了するようになりました。
著者じきじきのtwitterサポートをいただき、ありがとうございます！