MySQL道普請便り
第123回 ロッキングリードのNOWAITとSKIP LOCKEDオプションについて
InnoDBの通常のSELECTステートメントはロックを取得しません。よって，
ロッキングリードには，SELECT...と，SELECT...があります。従来のMySQLでは共有行ロックを取得するステートメントSELECT...が使われていました。MySQL 8.FOR SHAREに置き換わりました。
また，LOCK IN SHARE MODEはまだ使用可能ですが，NOWAITとSKIP LOCKEDオプションは指定できませんのでご注意ください。
MySQL 5.innodb_パラメータでデフォルト60秒です。ロジック上ロックが取得できなければステートメントを終了しても構わない場合であったとしても，
MySQL 8.NOWAITとSKIP LOCKEDオプションを指定することが可能になりました。ロックしようとした行がすでにロックされていたときに，
今回は，NOWAITとSKIP LOCKEDオプションについて，
NOWAITオプション
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オプションは，
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では十分にパフォーマンスが出なかった処理が，
たとえば，
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 | +----------+-------------+---------+
上記のようなテーブルを用意して，
UPDATE reserve_ticket SET user_id=? WHERE ticket_type=1200 AND user_id IS NULL LIMIT 1;
このステートメントは，
mysqlslapを使って実行速度を見てみましょう。mysqlslapの使用方法については，
同時実行数を50スレッドにして，
$ 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.
続いて，
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.
結果をまとめると，
| タイプ | 実行時間 |
|---|---|
| UPDATE | 84. |
| SKIP LOCKED＋UPDATE | 2. |
まとめ
今回はMySQL 8.NOWAITとSKIP LOCKEDオプションについて紹介しました。みんながMySQLに求めていた機能のひとつだと思います。このオプションを使ってパフォーマンスが良くなる処理は多いと思いますので，
バックナンバー
MySQL道普請便り
- 第127回 CLONEプラグインを導入しよう
- 第126回 MySQLへの書き込みを制限するさまざまな方法
- 第125回 phpMyAdminでDockerで建てたMySQLにアクセスする
- 第124回 trigger
- 第123回 ロッキングリードのNOWAITとSKIP LOCKEDオプションについて
- 第122回 DockerでMySQLをもっと便利に活用してみる
- 第121回 event_scheduler
- 第120回 events_statements_summary_by_digestテーブルのパラメータ
- 第119回 group_concat()を利用してgroup byを更に便利に使う
- 第118回 MySQL Routerを使って負荷分散を検証する