こちらに引き続き、以前サポートされた機能の再発掘です。MySQL 8.0.1 でサポートされた機能なので CHECK 制約よりも古いです（笑）。
※配布開始 1 周年を迎えた「MySQL 8.0 の薄い本」を、8.0.20 対応版改訂を機にリニューアルし、実行例を増やすための記事です。はい。
NOWAIT / SKIP LOCKEDの使いどころ
トランザクション内で、
- テーブルにある行の情報を取得する
- その行に対して更新を掛ける
場合には、
-
SELECT ... FOR UPDATEでロックを獲得する - （取得した情報を加工するなどして）
UPDATEする
という流れになりますが、他のトランザクションが先に当該行のロックを獲得していた場合、
- 先行して行ロックを獲得したトランザクションがロックを開放する
- ロックがタイムアウトする
まで待たされることになります。
- 航空機や鉄道・バス、劇場・映画館などの指定席を確保する
- 一点モノの商品を購入する
ような処理の場合、ロックが解放されるまで（またはタイムアウトするまで）待たされるよりも対象行のロックが獲得できなかったことを即時に返してほしいケースもあります。
そんなときは、SELECT ... FOR UPDATEに
-
NOWAIT（ロックが獲得できなかったときは即時にエラーを返す） -
SKIP LOCKED（即時に獲得可能なロックのみ獲得して結果を返す）
のいずれかを付加して実行すると便利です（かもしれません）。
使用例
航空機の座席を指定するケースをイメージしてテーブルをデータを用意してみます。
※面倒なので 1 便分だけです。
テーブル・データ
mysql> USE skiplock_test;
Database changed
mysql> CREATE TABLE seat
-> (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> flight_date DATE NOT NULL,
-> flight_number VARCHAR(10) NOT NULL,
-> seat_number VARCHAR(10) NOT NULL,
-> reserved BOOLEAN NOT NULL DEFAULT false,
-> UNIQUE (flight_date, flight_number, seat_number)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM seat ORDER BY id;
+----+-------------+---------------+-------------+----------+
| id | flight_date | flight_number | seat_number | reserved |
+----+-------------+---------------+-------------+----------+
| 1 | 2020-05-15 | RAC 801 | A2 | 0 |
| 2 | 2020-05-15 | RAC 801 | A3 | 0 |
| 3 | 2020-05-15 | RAC 801 | A4 | 0 |
| 4 | 2020-05-15 | RAC 801 | A5 | 0 |
| 5 | 2020-05-15 | RAC 801 | A6 | 0 |
| 6 | 2020-05-15 | RAC 801 | A7 | 0 |
| 7 | 2020-05-15 | RAC 801 | A8 | 0 |
| 8 | 2020-05-15 | RAC 801 | A9 | 0 |
| 9 | 2020-05-15 | RAC 801 | A10 | 0 |
| 10 | 2020-05-15 | RAC 801 | A11 | 0 |
| 11 | 2020-05-15 | RAC 801 | A12 | 0 |
| 12 | 2020-05-15 | RAC 801 | A13 | 0 |
| 13 | 2020-05-15 | RAC 801 | C2 | 0 |
| 14 | 2020-05-15 | RAC 801 | C3 | 0 |
| 15 | 2020-05-15 | RAC 801 | C4 | 0 |
| 16 | 2020-05-15 | RAC 801 | C5 | 0 |
| 17 | 2020-05-15 | RAC 801 | C6 | 0 |
| 18 | 2020-05-15 | RAC 801 | C7 | 0 |
| 19 | 2020-05-15 | RAC 801 | C8 | 0 |
| 20 | 2020-05-15 | RAC 801 | C9 | 0 |
| 21 | 2020-05-15 | RAC 801 | C10 | 0 |
| 22 | 2020-05-15 | RAC 801 | C11 | 0 |
| 23 | 2020-05-15 | RAC 801 | C12 | 0 |
| 24 | 2020-05-15 | RAC 801 | C13 | 0 |
| 25 | 2020-05-15 | RAC 801 | H1 | 0 |
| 26 | 2020-05-15 | RAC 801 | H2 | 0 |
| 27 | 2020-05-15 | RAC 801 | H3 | 0 |
| 28 | 2020-05-15 | RAC 801 | H4 | 0 |
| 29 | 2020-05-15 | RAC 801 | H5 | 0 |
| 30 | 2020-05-15 | RAC 801 | H6 | 0 |
| 31 | 2020-05-15 | RAC 801 | H7 | 0 |
| 32 | 2020-05-15 | RAC 801 | H8 | 0 |
| 33 | 2020-05-15 | RAC 801 | H9 | 0 |
| 34 | 2020-05-15 | RAC 801 | H10 | 0 |
| 35 | 2020-05-15 | RAC 801 | H11 | 0 |
| 36 | 2020-05-15 | RAC 801 | H12 | 0 |
| 37 | 2020-05-15 | RAC 801 | H13 | 0 |
| 38 | 2020-05-15 | RAC 801 | K1 | 0 |
| 39 | 2020-05-15 | RAC 801 | K2 | 0 |
| 40 | 2020-05-15 | RAC 801 | K3 | 0 |
| 41 | 2020-05-15 | RAC 801 | K4 | 0 |
| 42 | 2020-05-15 | RAC 801 | K5 | 0 |
| 43 | 2020-05-15 | RAC 801 | K6 | 0 |
| 44 | 2020-05-15 | RAC 801 | K7 | 0 |
| 45 | 2020-05-15 | RAC 801 | K8 | 0 |
| 46 | 2020-05-15 | RAC 801 | K9 | 0 |
| 47 | 2020-05-15 | RAC 801 | K10 | 0 |
| 48 | 2020-05-15 | RAC 801 | K11 | 0 |
| 49 | 2020-05-15 | RAC 801 | K12 | 0 |
| 50 | 2020-05-15 | RAC 801 | K13 | 0 |
+----+-------------+---------------+-------------+----------+
50 rows in set (0.00 sec)
ここでまず、1 つ目のトランザクションで「K3」席の行ロックを獲得します。
トランザクション1
mysql> USE skiplock_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM seat WHERE flight_date = '2020-05-15' AND flight_number = 'RAC 801'
-> AND seat_number = 'K3' AND reserved IS false FOR UPDATE;
+----+-------------+---------------+-------------+----------+
| id | flight_date | flight_number | seat_number | reserved |
+----+-------------+---------------+-------------+----------+
| 40 | 2020-05-15 | RAC 801 | K3 | 0 |
+----+-------------+---------------+-------------+----------+
1 row in set (0.00 sec)
続いて、2 つ目のトランザクションでNOWAITを指定して、同じ席の行ロックの獲得を試みます。
トランザクション2（NOWAIT）
mysql> USE skiplock_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM seat WHERE flight_date = '2020-05-15' AND flight_number = 'RAC 801'
-> AND seat_number = 'K3' AND reserved IS false FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
NOWAITの場合、対象行のロックを獲得できなかった場合は即時にエラーが返ります。
さらに、3 つ目のトランザクションでSKIP LOCKEDを指定して、同じ席を含む複数行のロックの獲得を試みます。
トランザクション3（SKIP_LOCKED）
mysql> USE skiplock_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM seat WHERE flight_date = '2020-05-15' AND flight_number = 'RAC 801'
-> AND seat_number LIKE '_3' AND reserved IS false FOR UPDATE SKIP LOCKED;
+----+-------------+---------------+-------------+----------+
| id | flight_date | flight_number | seat_number | reserved |
+----+-------------+---------------+-------------+----------+
| 2 | 2020-05-15 | RAC 801 | A3 | 0 |
| 14 | 2020-05-15 | RAC 801 | C3 | 0 |
| 27 | 2020-05-15 | RAC 801 | H3 | 0 |
+----+-------------+---------------+-------------+----------+
3 rows in set (0.00 sec)
「K3」席以外の行ロックが獲得できたので、3 席分の行が結果として（即時に）返ってきました（この場合はエラーにはなりません）。
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）