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道普請便り
- 第171回 MyDumperを使ってみよう[その2]
- 第170回 MySQLのキーワードと予約語をテーブルに使用したい場合
- 第169回 pt-variable-advisorを使ってみよう
- 第168回 MyDumperを使ってみよう[その1]
- 第167回 M1搭載MacのDockerでMySQLを動かしてみる
- 第166回 Performance_schema関数
- 第165回 MySQLの圧縮いろいろ
- 第164回 admin_address,admin_portを設定しよう
- 第163回 sysbench 1.0を使ってMySQLの負荷テストをする
- 第162回 mysqlクライアントの履歴を確認してみる