InnoDBの通常のSELECTステートメントはロックを取得しません。よって、
ロッキングリードには、SELECT...と、SELECT...があります。従来のMySQLでは共有行ロックを取得するステートメントSELECT...が使われていました。MySQL 8.FOR SHAREに置き換わりました。
また、LOCK IN SHARE MODEはまだ使用可能ですが、NOWAITとSKIP LOCKEDオプションは指定できませんのでご注意ください。
MySQL 5.innodb_パラメータでデフォルト50秒です。ロジック上ロックが取得できなければステートメントを終了しても構わない場合であったとしても、
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に求めていた機能のひとつだと思います。このオプションを使ってパフォーマンスが良くなる処理は多いと思いますので、