[前回] 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ユーザによる同時予約の場合はどうなるでしょうか。
気になるので次回検証します、お楽しみに。

[次回] MySQL8の同時実行制御検証(3)