[前回] MySQL8の同時実行制御検証(1)
はじめに
映画館予約システムを使って、SKIP LOCKEDオプションの基本機能を検証します。
| 座席番号 | 予約状況 |
|---|---|
| A1 | NO |
| A2 | YES |
| B1 | NO |
| ... | ... |
予約処理は、2ステップ必要です。
- 1. 空席を見つける
- 「予約状況がNOのレコード」はすべて「空席」とみなしてよいでしょうか?
- 同じ瞬間に別ユーザが予約中(ロック中)かも、このようなレコードはあきらめるしか。。。
- 2. 予約状況をYESに更新、予約確定
- 対象レコードに行ロックをかけ、他のユーザが予約できないように
ここで大事なのは、上記2ステップが1トランザクション内で完結することです。
Wikipediaで、トランザクションの定義、
分けることのできない一連の情報処理の一単位を意味する。
この一連の処理を分割して実行した場合、結果の整合性を保てなくなる。
早速、検証スタートします。
操作用に、端末三つ開いておきます(端末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ユーザによる同時予約の場合はどうなるでしょうか。
気になるので次回検証します、お楽しみに。