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

##### [前回] [MySQL8の同時実行制御検証(2)](https://qiita.com/mingchun_zhao/items/0d0f081115d3820af364)

## はじめに

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

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

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

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

検証スタートします、検証環境や事前準備は、[前回](https://qiita.com/mingchun_zhao/items/0d0f081115d3820af364)をご参照ください。

## 検証シナリオ

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を実行

```shell
$ 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を実行

```shell
$ 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の並列処理を検証してみます、お楽しみに。