これは、なにをしたくて書いたもの？
MySQL 8.0.1から、SELECT ... FOR UPDATEにSKIP LOCKEDというオプションがつけられるようになったみたいです。
このオプションを使うと、クエリーを実行した時にすでにロックされているレコードが結果セットから削除されるようです。
SELECT ... FOR UPDATE SKIP LOCKED
SKIP LOCKEDオプションは、NOWAITオプションとともにMySQL 8.0.1で追加されました。
InnoDB: InnoDB now supports NOWAIT and SKIP LOCKED options with SELECT ... FOR SHARE and SELECT ... FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction. SKIP LOCKED removes locked rows from the result set. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)
2017年のリリースなので、けっこう前ですね…。全然気づいてなかったです。
NOWAITオプションは今回は扱いませんが、クエリーが対象とするレコードがすでにロックされていた場合は失敗するようにする
オプションです。
ドキュメントはこちら。
MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.2.4 読取りのロック
説明としては、クエリーを実行した時にロックの獲得を待たず、すでにロックされている行は結果セットから削除するということが
書かれています。
A locking read that uses SKIP LOCKED never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.
適用対象は、行ロックのみです。
NOWAIT and SKIP LOCKED only apply to row-level locks.
また、ステートメントベースのレプリケーションでは安全ではありません。
Statements that use NOWAIT or SKIP LOCKED are unsafe for statement based replication.
このオプションの利用例としては、キューやチケット予約などが挙げられています。
MySQL :: MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows
Using SKIP LOCK For Queue Processing in MySQL
第123回 ロッキングリードのNOWAITとSKIP LOCKEDオプションについて | gihyo.jp
簡単に試してみましょう。
環境
今回の環境は、こちら。
MySQL localhost:3306 ssl SQL > select version(); +-----------+ | version() | +-----------+ | 8.0.32 | +-----------+ 1 row in set (0.0006 sec)
practiceというデータベース、kazuhira／passwordでログインできるユーザーを作成しているものとします。
また、トランザクション分離レベルはREAD COMMITTEDとします。
MySQL localhost:3306 ssl practice SQL > show variables like 'transaction_isolation'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.0047 sec)
SELECT ... FOR UPDATE SKIP LOCKEDを試す
では、SELECT ... FOR UPDATE SKIP LOCKEDを試してみましょう。
3つMySQLセッションを用意します。
#1 $ mysqlsh kazuhira@localhost:3306/practice --sql #2 $ mysqlsh kazuhira@localhost:3306/practice --sql #3 $ mysqlsh kazuhira@localhost:3306/practice --sql
こんなテーブルを作成。
create table queue( id int auto_increment, name varchar(20), entried_time datetime, processed boolean, primary key(id) );
ひとつ目のセッションで、データを入れてみます。
#1 start transaction; insert into queue(name, entried_time, processed) values('task1', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task2', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task3', now(), false); commit;
こうなりました。
#1 MySQL localhost:3306 ssl practice SQL > select * from queue; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 1 | task1 | 2023-04-15 14:35:14 | 0 | | 2 | task2 | 2023-04-15 14:35:16 | 0 | | 3 | task3 | 2023-04-15 14:35:18 | 0 | +----+-------+---------------------+-----------+ 3 rows in set (0.0004 sec)
2つ目のセッションで、こんなSQLを実行。select文にはfor update skip lockedを入れています。
#2 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0006 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where processed = false -> order by entried_time asc -> for update skip locked; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 1 | task1 | 2023-04-15 14:35:14 | 0 | | 2 | task2 | 2023-04-15 14:35:16 | 0 | | 3 | task3 | 2023-04-15 14:35:18 | 0 | +----+-------+---------------------+-----------+ 3 rows in set (0.0013 sec)
データが取得できました。
3つ目のセッションでも同じことをやってみます。
#3 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0004 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where processed = false -> order by entried_time asc -> for update skip locked; Empty set (0.0011 sec)
結果は0件です。
なお、この機能はトランザクション内でなければ効果がありません。
ここで1度、2つのセッションのトランザクションをロールバックしてみます。
#2 MySQL localhost:3306 ssl practice ★ SQL > rollback; Query OK, 0 rows affected (0.0006 sec) #3 MySQL localhost:3306 ssl practice ★ SQL > rollback; Query OK, 0 rows affected (0.0006 sec)
ここで1度データを削除します。
#1 MySQL localhost:3306 ssl practice SQL > truncate table queue; Query OK, 0 rows affected (0.3020 sec)
次は少し多めにデータを入れてみます。
#1 start transaction; insert into queue(name, entried_time, processed) values('task1', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task2', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task3', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task4', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task5', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task6', now(), false); select sleep(2); insert into queue(name, entried_time, processed) values('task7', now(), false); select sleep(2); commit;
こうなりました。
#1 MySQL localhost:3306 ssl practice SQL > select * from queue; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 1 | task1 | 2023-04-15 14:39:53 | 0 | | 2 | task2 | 2023-04-15 14:39:55 | 0 | | 3 | task3 | 2023-04-15 14:39:57 | 0 | | 4 | task4 | 2023-04-15 14:39:59 | 0 | | 5 | task5 | 2023-04-15 14:40:01 | 0 | | 6 | task6 | 2023-04-15 14:40:03 | 0 | | 7 | task7 | 2023-04-15 14:40:05 | 0 | +----+-------+---------------------+-----------+ 7 rows in set (0.0004 sec)
次に、2つ目のセッションでこんなSQLを実行してみます。
#2 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0006 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where processed = false -> order by entried_time asc -> limit 3 -> for update skip locked; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 1 | task1 | 2023-04-15 14:35:14 | 0 | | 2 | task2 | 2023-04-15 14:35:16 | 0 | | 3 | task3 | 2023-04-15 14:35:18 | 0 | +----+-------+---------------------+-----------+ 3 rows in set (0.0011 sec)
今回はlimitを入れて取得する件数を絞りました。
3つ目のセッションでも同じことをやってみます。
#3 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0005 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where processed = false -> order by entried_time asc -> limit 3 -> for update skip locked; Empty set (0.0011 sec)
結果はなんと0件です。limitで絞り込んだ結果に対してskip lockedで省かれるようですね。
またロールバックしておきます。
#2 MySQL localhost:3306 ssl practice ★ SQL > rollback; Query OK, 0 rows affected (0.0006 sec) #3 MySQL localhost:3306 ssl practice ★ SQL > rollback; Query OK, 0 rows affected (0.0006 sec)
2つ目のセッションと、3つ目のセッションで実行するクエリーを少し変えてみましょう。
2つ目のセッションでは、nameカラムで絞り込み。
#2 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0004 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where name in ('task1', 'task2', 'task3') and processed = false -> order by entried_time asc -> for update skip locked; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 1 | task1 | 2023-04-15 14:39:53 | 0 | | 2 | task2 | 2023-04-15 14:39:55 | 0 | | 3 | task3 | 2023-04-15 14:39:57 | 0 | +----+-------+---------------------+-----------+ 3 rows in set (0.0010 sec)
3つ目のセッションでは、2つ目のセッションとオーバーラップするように絞り込み。
#3 MySQL localhost:3306 ssl practice SQL > start transaction; Query OK, 0 rows affected (0.0005 sec) MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where name in ('task1', 'task2', 'task3', 'task4', 'task5', 'task6') and processed = false -> order by entried_time asc -> for update skip locked; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 4 | task4 | 2023-04-15 14:39:59 | 0 | | 5 | task5 | 2023-04-15 14:40:01 | 0 | | 6 | task6 | 2023-04-15 14:40:03 | 0 | +----+-------+---------------------+-----------+ 3 rows in set (0.0011 sec)
こうすると、確かに2つ目のセッションでロックしている範囲が省かれていますね。
2つ目のセッションで2件更新して、コミットしてみましょう。
#2 MySQL localhost:3306 ssl practice ★ SQL > update queue -> set processed = true -> where name in ('task1', 'task2'); Query OK, 2 rows affected (0.0035 sec) Rows matched: 2 Changed: 2 Warnings: 0 MySQL localhost:3306 ssl practice ★ SQL > commit; Query OK, 0 rows affected (0.0447 sec)
3つ目のセッションからは、更新されなかった3つ目のレコードが見えるようになります。
※トランザクション分離レベルがREAD COMMITTEDだからですね
MySQL localhost:3306 ssl practice ★ SQL > select id, name, entried_time, processed -> from queue -> where name in ('task1', 'task2', 'task3', 'task4', 'task5', 'task6') and processed = false -> order by entried_time asc -> for update skip locked; +----+-------+---------------------+-----------+ | id | name | entried_time | processed | +----+-------+---------------------+-----------+ | 3 | task3 | 2023-04-15 14:39:57 | 0 | | 4 | task4 | 2023-04-15 14:39:59 | 0 | | 5 | task5 | 2023-04-15 14:40:01 | 0 | | 6 | task6 | 2023-04-15 14:40:03 | 0 | +----+-------+---------------------+-----------+ 4 rows in set (0.0011 sec)
トランザクションが残っているのは3つ目のセッションだけなので、こちらをロールバック。
#3 MySQL localhost:3306 ssl practice ★ SQL > rollback; Query OK, 0 rows affected (0.0006 sec)
と、こんな感じでだいたい動作の雰囲気はわかったでしょうか。
まとめ
MySQL 8.0.1で追加されたSELECT ... FOR UPDATE SKIP LOCKEDを試してみました。
割とSQLに疎いので、そもそもこういう機能があることを知らなかったのと、MySQLにも追加されていたのも知らなかったです。
イメージだけで使ってlimitと組み合わせると思わぬ踏み抜きをやりそうな気はしましたが、そのあたりの特性には注意して使うところを
考えたいなと思います。
