MySQL道普請便り

第123回　ロッキングリードのNOWAITとSKIP LOCKEDオプションについて

MySQL, SKIP LOCKED, MySQL 8.0

この記事を読むのに必要な時間：およそ 3 分

InnoDBの通常のSELECTステートメントはロックを取得しません。よって，同一のトランザクション内にSELECTしたデータを使用して更新する際に，間に別のトランザクションがすでに同じ行を更新していると，ロストアップデートといったアノマリーが起こったりします。それを防ぐために，ロッキングリードを使用することでデータを保護することが可能です。

ロッキングリードには，排他行ロックを取得するためのステートメントSELECT...FOR UPDATEと，共有行ロックを取得するためのステートメントSELECT...FOR SHAREがあります。従来のMySQLでは共有行ロックを取得するステートメントSELECT...LOCK IN SHARE MODEが使われていました。MySQL 8.0からはこれがFOR SHAREに置き換わりました。

また，下位互換のためLOCK IN SHARE MODEはまだ使用可能ですが，後述のNOWAITとSKIP LOCKEDオプションは指定できませんのでご注意ください。

MySQL 5.7とそれ以前のロッキングリードの問題点として，ブロックしているトランザクションが行ロックを解放するまで待機する必要がありました。最大待機時間はinnodb_lock_wait_timeoutパラメータでデフォルト60秒です。ロジック上ロックが取得できなければステートメントを終了しても構わない場合であったとしても，途中でステートメント終了させるような機能は提供されていませんでした。

MySQL 8.0からは，ロッキングリードに対してNOWAITとSKIP LOCKEDオプションを指定することが可能になりました。ロックしようとした行がすでにロックされていたときに，ステートメントがすぐに戻るようにしたり，ロックされた行を結果セットから除外しても問題ない場合は，行ロックが解放されるのを待つことがないようにするオプションです。

今回は，MySQL 8.0から使用できるNOWAITとSKIP LOCKEDオプションについて，使いどころを混じえて紹介したいと思います。

NOWAITオプション

NOWAITオプションは，FOR UPDATEまたはFOR SHAREの後にNOWAITと記述します。これは行ロックの取得に待機しません。ステートメント実行時に，取得しようとした行に他のトランザクションがロックを保持している場合は，エラーを返します。

以下は，他のトランザクションがロックを保持しているために，ロックを取得できなかったときに返るエラーです。

mysql> SELECT * FROM t0 WHERE id=1 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
Error (Code 3572): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
Error (Code 1030): Got error 203 - 'Do not wait for lock' from storage engine

SKIP LOCKEDオプション

SKIP LOCKEDオプションは，FOR UPDATEまたはFOR SHAREの後にSKIP LOCKEDと記述します。これも行ロックの取得を待機しません。このステートメントはロックされた行が結果セットから削除されます。単一行のロック競合においては，別トランザクションがロックを保持していると，ステートメントにはEmptyが返ります。複数のセッションがキューのようなテーブルに同時にアクセスするときに，ロックの競合を回避するために使用されます。

tx1> SELECT * FROM t0 WHERE id=1 FOR UPDATE SKIP LOCKED;
+----+------+
| id | id2  |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

tx2> SELECT * FROM t0 WHERE id=1 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

SKIP LOCKEDオプションの使いどころ

従来のMySQLでは十分にパフォーマンスが出なかった処理が，SKIP LOCKEDを使用することで改善する例を紹介したいと思います。

たとえば，5000枚のチケットを用意して，先着順にユーザIDをセットすることで予約するような仕組みを考えます。

mysql> CREATE TABLE `reserve_ticket` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `ticket_type` int NOT NULL,
  `user_id` bigint DEFAULT NULL,
  `update_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tickettype_userid` (`ticket_type`,`user_id`)
) ENGINE=InnoDB

mysql> SELECT COUNT(*),ticket_type,user_id FROM reserve_ticket WHERE ticket_type=1200 GROUP BY 2,3 ;
+----------+-------------+---------+
| COUNT(*) | ticket_type | user_id |
+----------+-------------+---------+
|     5000 |        1200 |    NULL |
+----------+-------------+---------+

上記のようなテーブルを用意して，ticket_type=1200でuser_idをNULLにしたデータ（チケット）を事前に5000行（枚）INSERTしておきます。そこから先着順にuser_idがNULLのものを自身のuser_idに上書きする処理です。従来のMySQLでは以下のようなUPDATE文を実行していました。

UPDATE reserve_ticket SET user_id=? WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1;

このステートメントは，単体で実行する分にはレイテンシは低く問題はありません。しかし，同時実行数が上がるにつれてレイテンシは高くなり，高負荷の原因となる処理になります。最悪の場合はMySQLがストールする可能性もあります。

mysqlslapを使って実行速度を見てみましょう。mysqlslapの使用方法については，第23回　mysqlslapを使って負荷テストをしてみようをご参照ください。

同時実行数を50スレッドにして，5000ステートメント実施（すべてのチケットの予約）が完了するまでの時間を計測してみます。

$ SQL=`cat << EOS
BEGIN;
UPDATE reserve_ticket SET user_id=1 WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1;
COMMIT;
EOS`;
mysqlslap --host=hostname  -p --port=3306 --user=test  --query="$(echo $SQL)" --concurrency=50 --number-of-queries=5000 --create-schema=tt

Benchmark
        Average number of seconds to run all queries: 84.505 seconds
        Minimum number of seconds to run all queries: 84.505 seconds
        Maximum number of seconds to run all queries: 84.505 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

結果は84.505秒で完了しました。さらに同時実行数を上げると実行時間はもっと大きくなります。

続いて，SKIP LOCKEDとUPDATEを使用したステートメントで試してみます。

BEGIN;
SELECT @id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE reserve_ticket SET user_id=? WHERE id = @id;
COMMIT;

前述のUPDATEの条件をSELECTに書き換えて，FOR UPDATE SKIP LOCKEDを使用してプライマリキーを取得します。そのプライマリーキーを使って更新する流れになります。

$ SQL=`cat << EOS
BEGIN;
SELECT @id := id FROM reserve_ticket WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1 FOR UPDATE SKIP LOCKED;
UPDATE reserve_ticket SET user_id=1 WHERE id = @id;
COMMIT;
EOS`; mysqlslap --host=hostname  -p --port=3306 --user=test --query="$(echo $SQL)" --concurrency=50 --number-of-queries=5000 --create-schema=tt

Benchmark
        Average number of seconds to run all queries: 2.132 seconds
        Minimum number of seconds to run all queries: 2.132 seconds
        Maximum number of seconds to run all queries: 2.132 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100

このテストは2.132秒で完了しました。

結果をまとめると，以下のようになります。UPDATEよりもSKIP LOCKED＋UPDATEを使用したほうが，パフォーマンスが改善することがわかります。

タイプ 実行時間
UPDATE 84.505秒
SKIP LOCKED＋UPDATE 2.132秒

まとめ

今回はMySQL 8.0からのNOWAITとSKIP LOCKEDオプションについて紹介しました。みんながMySQLに求めていた機能のひとつだと思います。このオプションを使ってパフォーマンスが良くなる処理は多いと思いますので，積極的に使っていきたい機能です。

MySQL, SKIP LOCKED, MySQL 8.0

著者プロフィール

北川健太郎（きたがわけんたろう）

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで，好きなOracleDatabaseの機能はログオントリガー。

Twitter：@keny_lala