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

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

## はじめに

映画館予約システムを使って、SKIP LOCKEDオプションの基本機能を検証します。

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

予約処理は、2ステップ必要です。

- 1\. 空席を見つける
    - 「予約状況がNOのレコード」はすべて「空席」とみなしてよいでしょうか? 
    - 同じ瞬間に別ユーザが予約中(ロック中)かも、このようなレコードはあきらめるしか。。。
- 2\. 予約状況をYESに更新、予約確定
    - 対象レコードに行ロックをかけ、他のユーザが予約できないように
   
ここで大事なのは、上記2ステップが1トランザクション内で完結することです。
[Wikipedia](https://ja.wikipedia.org/wiki/%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3#%E3%82%B3%E3%83%B3%E3%83%94%E3%83%A5%E3%83%BC%E3%82%BF%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%E3%81%AB%E3%81%8A%E3%81%91%E3%82%8B%E3%83%88%E3%83%A9%E3%83%B3%E3%82%B6%E3%82%AF%E3%82%B7%E3%83%A7%E3%83%B3%E5%87%A6%E7%90%86)で、トランザクションの定義、

```
分けることのできない一連の情報処理の一単位を意味する。
この一連の処理を分割して実行した場合、結果の整合性を保てなくなる。
```


早速、検証スタートします。
操作用に、端末三つ開いておきます(端末1、端末2、端末3)

## 検証環境

```
CPU: Intel(R) Core(TM) 3.30GHz(4コア8スレッド)
メモリ: 16 GB
OS: Ubuntu 20.04 LTS
DB: MySQL 8.0.28
```

ちなみに、Ubuntuは、Windows 10で以下のように管理者権限で導入しました。

```
> wsl --install -d Ubuntu-20.04
```

## 事前準備

端末1で実施します。

### MySQL インストール

```
$ sudo apt install mysql-server
$ sudo service mysql start
$ sudo mysql_secure_installation
```


### テーブル作成

- mysqlコマンドからMySQL接続
```
$ sudo mysql -u user -p
```

- データベース作成
```
CREATE DATABASE test;
USE test
```

- テーブル作成
```
CREATE TABLE theater (
  seat_id INT PRIMARY KEY,
  ordered ENUM('YES', 'NO') DEFAULT 'NO'
);
```

### 1万レコードINSERT


- cte_max_recursion_depthの制限を緩和
```
set @@cte_max_recursion_depth=1000000;
```

- INSERT

```
INSERT INTO theater (seat_id)
WITH RECURSIVE my_cte AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT 1 + n FROM my_cte WHERE n < 10001
)
SELECT * FROM my_cte;
```

## ユーザAが予約開始

端末2で実施します。

- mysqlコマンドからMySQL接続
```
$ sudo mysql -u user -p
mysql> USE test
```

- トランザクション開始
```
mysql> START TRANSACTION;
```

- seat_id=1のレコードを検索します
```
mysql> SELECT * FROM theater WHERE seat_id = '1' FOR UPDATE SKIP LOCKED;
+---------+---------+
| seat_id | ordered |
+---------+---------+
|       1 | NO      |
+---------+---------+
1 row in set (0.00 sec)
```

結果、1レコードヒットしました。
`SKIP LOCKED`オプションついているので、行ロックかかります。
※ 注意: 排他制御を確認したいので、コミットは待ってください。

## 同時に、ユーザBが予約開始

端末3で実施します。

- mysqlコマンドからMySQL接続
```
$ sudo mysql -u user -p
mysql> USE test
```

- トランザクション開始
```
mysql> START TRANSACTION;
```

- seat_id=1のレコードを検索します
```
mysql> SELECT * FROM theater WHERE seat_id = '1' FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
```

あれー、存在するはずのレコードがヒットしません。
理由は、ユーザAが予約中でロックされているため、ユーザBの予約ではスキップされてしまったからです。
肝心なのは、ユーザAが行ロック解除するまで待たずに、すぐ問い合わせ結果が返された点です(SKIP LOCKED オプションのおかげ)。


## ロック状態を確認します

端末1で実施します。

- mysqlコマンドからMySQL接続
```
$ sudo mysql -u user -p
```

- performance_schema.data_locks を確認
```
mysql> SELECT object_name, index_name, lock_type, lock_mode, lock_data
       FROM performance_schema.data_locks WHERE object_name = 'theater';
+-------------+------------+-----------+---------------+-----------+
| object_name | index_name | lock_type | lock_mode     | lock_data |
+-------------+------------+-----------+---------------+-----------+
| theater     | NULL       | TABLE     | IX            | NULL      |
| theater     | PRIMARY    | RECORD    | X,REC_NOT_GAP | 1         |
+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)
```

すると、theaterテーブルに行ロック(lock_type=RECORD)が排他モード(lock_mode=X)で存在。

- 端末2で、予約状況をYESに変更してから、コミット実行します。
```
UPDATE theater SET ordered='YES' WHERE seat_id='1';
COMMIT;
```

- 再度、端末1で performance_schema.data_locks を確認

```
mysql> SELECT object_name, index_name, lock_type, lock_mode, lock_data
       FROM performance_schema.data_locks WHERE object_name = 'theater';
Empty set (0.00 sec)
```

今度は、トランザクション終了したため、ロック消えました。

## おわりに

SKIP LOCKEDを用いて、2ユーザ間でロック待機せず排他制御行えることを確認できました。
100ユーザによる同時予約の場合はどうなるでしょうか。
気になるので次回検証します、お楽しみに。

##### [次回] [MySQL8の同時実行制御検証(3)](https://qiita.com/mingchun_zhao/items/42555a503776cfcdfce9)