MySQL8の同時実行制御検証(3)

[前回] MySQL8の同時実行制御検証(2)

はじめに

前回は、二人同時に予約ボタンを押した場合、遅延なく制御できるか確認しました。
今回は、100人同時に予約ボタンを押した、といった想定です。
前回同様、映画館予約を例に、SKIP LOCKEDオプションを検証します。

座席番号(seat_id)	予約状況(ordered)
A1	NO
A2	YES
B1	NO
...	...

おさらいとして、予約処理は1トランザクションで完結、2ステップで構成されます。

1. 空席を見つける
2. 予約状況をNOからYESに、予約確定

検証スタートします、検証環境や事前準備は、前回をご参照ください。

検証シナリオ

100人が同時に「予約」ボタンを押した場合、

1. 100人全員が予約できるか(1万自由席)
2. 予約までどれだけ待たされるか

を確認します。
比較のため、下記二つのトランザクションをそれぞれ実行します。

• トランザクション1
  SKIP LOCKED オプションあり、空席見つけてから、予約状況をYESに変更

START TRANSACTION;
SELECT @seat:=seat_id FROM theater WHERE ordered="NO" LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE theater SET ordered="YES" WHERE seat_id=@seat;
COMMIT;

• トランザクション2
  SKIP LOCKED オプションなし、空席見つけてから、予約状況をYESに変更

START TRANSACTION;
SELECT @seat:=seat_id FROM theater WHERE ordered="NO" LIMIT 1 FOR UPDATE;
UPDATE theater SET ordered="YES" WHERE seat_id=@seat;
COMMIT;

100人が同時にトランザクション1を実行

• mysqlコマンドで、全席の予約状況をNOに初期化

mysql> UPDATE theater SET ordered='NO';

• shellスクリプトで、100セッションから同時にトランザクション1を実行

$ for ((i=1;i<=100;i++));do time mysql -uuser -pxxx test -e 'START TRANSACTION; SELECT @seat:=seat_id FROM theater WHERE ordered="NO" LIMIT 1 FOR UPDATE SKIP LOCKED; UPDATE theater SET ordered="YES" WHERE seat_id=@seat; COMMIT;' >>query1.log & done 2> time1.log

• 100人全員予約できたか確認

mysql> SELECT COUNT(*) FROM theater WHERE ordered='YES';
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+

全員予約できています。

• 100人それぞれの予約にかかった時間を確認

time1.logにtimeコマンドによる計測時間が出力されています。

... ...

real    0m0.051s
user    0m0.006s
sys     0m0.000s

real    0m0.072s
user    0m0.005s
sys     0m0.000s
... ...

一番長く待たされた人は、82ミリ秒でした。

$ grep real time1.log | cut -c 8-12 | sort | tail -1
0.082

• 座席番号の予約順を確認

座席がどのような順序で予約されたか、query1.logに記録されています。

... ...
@seat:=seat_id
1
@seat:=seat_id
12
@seat:=seat_id
5
... ...

座席順には関係なく予約されていく様子が窺えます。

100人が同時にトランザクション2を実行

• mysqlコマンドで、全席の予約状況をNOに初期化

mysql> UPDATE theater SET ordered='NO';

• shellスクリプトで、100セッションから同時にトランザクション2を実行

$ for ((i=1;i<=100;i++));do time mysql -uuser -pxxx test -e 'START TRANSACTION; SELECT @seat:=seat_id FROM theater WHERE ordered="NO" LIMIT 1 FOR UPDATE; UPDATE theater SET ordered="YES" WHERE seat_id=@seat; COMMIT;' >>query2.log & done 2> time2.log

• 100人全員予約できたか確認

mysql> SELECT COUNT(*) FROM theater WHERE ordered='YES';
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+

こちらも全員予約できました。

• 100人それぞれの予約にかかった時間を確認

time2.logにtimeコマンドによる計測時間が出力されています。

... ...
real    0m0.646s
user    0m0.006s
sys     0m0.000s

real    0m0.661s
user    0m0.006s
sys     0m0.000s
... ...

一番長く待たされた人は、661ミリ秒でした。

$ grep real time2.log | cut -c 8-12 | sort | tail -1
0.661

• 座席番号の予約順を確認

座席がどのような順序で予約されたか、query2.logに記録されています。

... ...
@seat:=seat_id
1
@seat:=seat_id
2
@seat:=seat_id
3
... ...

座席が順を追って予約されていく様子が窺えます(一つずつ処理が終わるまで待つ)。

検証結果の考察

SKIP LOCKEDオプションあり・なしで、検証結果を比較すると

• 100人全員が予約できたかについて
  • 両者ともOK
• 予約までどれだけ待たされたかについて
  • SKIP LOCKEDにより、待ち時間がなんと1/8まで短縮されています(0.082s/0.661s)

おわりに

100ユーザによる同時予約で、SKIP LOCKEDオプションの効果抜群でした。
次回は別のテーマでMySQL 8の並列処理を検証してみます、お楽しみに。