MySQL 8のSKIP LOCKED・NOWAIT（ロッキングリードオプション）を試してみる

行ロックを利用してレコードを参照する方法をロッキングリードと呼びます。ロッキングリードを利用することで参照レコードの情報を保護できます。

ロッキングリードには共有ロックを利用するSELECT ... LOCK IN SHARE MODE（MySQL 8以前）・SELECT ... FOR SHARE（MySQL 8）と、排他ロックを利用するSELECT ... FOR UPDATEがあります。

MySQL 8.0からロッキングリードにSKIP LOCKEDとNOWAITのオプションが追加され、ロッキングリードの挙動を変更できるようになりました。¹

今回はMySQL 8で追加されたロッキングリードオプションであるSKIP LOCKEDとNOWAITについて紹介します。

ロッキングリードオプションの挙動

SKIP LOCKEDとNOWAITの挙動は以下の通りです。

ロッキングリードオプションの使い方

ロッキングリードオプションはSQLの最後に追記します。
たとえばSELECT ... FOR UPDATEでNOWAITを利用したい場合は、SELECT ... FOR UPDATE NOWAITとなります。
なお、SELECT ... LOCK IN SHARE MODEではロッキングオプションは利用できません。

ロッキングオプションの挙動の確認

以下のようなusersテーブルを作成し、ロッキングオプションの挙動を確認してみます。

> SELECT * FROM users\G;
*************************** 1. row ***************************
        id: 1
 last_name: Franecki
first_name: Cherri
       age: 21
*************************** 2. row ***************************
        id: 2
 last_name: Conroy
first_name: Columbus
       age: 18
2 rows in set (0.00 sec)

トランザクション内で排他ロックを作成するUPDATEを実行します。
排他ロックのSQLが実行されているターミナルを『セッションA』と呼ぶことにします。

セッションA

-- トランザクション開始
-- 『BEGING』のかわりに『START TRANSACTION』でもOK
> BEGIN;
Query OK, 0 rows affected (0.01 sec)

-- トランザクション内でid=1のageを21から22に更新する
> UPDATE users SET age = 22 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

『セッションA』で排他ロック中のレコードを別ターミナル『セッションB』からアクセスしたときの挙動について検証していきます。

ロッキングリードオプションなしの場合

排他ロック中のレコードにアクセスするとロック待ちになります。

セッションB

> SELECT * FROM users WHERE id = 1 FOR UPDATE \G;
-- 結果が返ってこない

SKIP LOCKEDの場合

SKIP LOCKEDを利用すると、ロック待ちのレコードへのアクセスはスキップされます。
ロック待ちにならないため結果がすぐに返ってきます。

セッションB

-- ロック中のid = 1の取得はスキップされる
> SELECT * FROM users WHERE id = 1 FOR UPDATE SKIP LOCKED \G;
Empty set (0.00 sec)

-- ロックされていないレコードは取得できる
> SELECT * FROM users FOR UPDATE SKIP LOCKED \G;
*************************** 1. row ***************************
        id: 2
 last_name: Conroy
first_name: Columbus
       age: 18

NOWAITの場合

NOWAITを利用すると、ロック待ちのレコードへのアクセスはエラーになります。
ロック待ちにならないためエラーがすぐに表示されます。

セッションB

-- ロック待ちのレコードにアクセスした場合はエラーになる
> SELECT * FROM users WHERE id = 1 FOR UPDATE NOWAIT \G;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

-- アクセス対象のレコードの一部がロック待ちでもエラーになる
> SELECT * FROM users FOR UPDATE NOWAIT \G;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

さいごに

Twitter(@nishina555)やってます。フォローしてもらえるとうれしいです！

参考

• MySQL8で、排他ロックされたレコードがスキップできるようになった。
• 第123回 ロッキングリードのNOWAITとSKIP LOCKEDオプションについて
• MySQL 8.0 で NOWAIT / SKIP LOCKED（いまさら）