LoginSignup
0
0

More than 1 year has passed since last update.

@chii-08(ちい)

select for updateのロックとインデックス

Posted at

select for update

業務でMySQLのタイムアウトエラーが多発することがあった。
対象のクエリはselect for updateを使用していたため、select for update のロックの挙動を調査した。
理解できてなかった部分もあったため調査した結果をまとめる。

環境

MySQLのバージョンは5.7.34

テーブルとデータ準備

下記のようなPKであるIDとuser_idカラムで構成されるreservationテーブルで挙動を確認する。

mysql> SHOW CREATE TABLE reservation;
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                             |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reservation | CREATE TABLE `reservation` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `idx_user_country` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 |
+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

データをINSERTした。
user_idが5,6,7のレコードは存在しない。

mysql> SELECT * FROM reservation;
+----+---------+
| ID | user_id |
+----+---------+
|  1 |       1 |
|  2 |       2 |
|  3 |       3 |
|  4 |       4 |
|  5 |       8 |
|  6 |       9 |
|  7 |      10 |
|  9 |      10 |
| 10 |      11 |
+----+---------+
9 rows in set (0.00 sec)

インデックスが貼られているカラムを検索条件とする

範囲検索でない かつ 結果が存在する

-- トランザクションA
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM reservation WHERE user_id = 1 FOR UPDATE;
+----+---------+
| ID | user_id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.01 sec)

トランザクションAを終了させない状態で、下記を実行してみる。

-- SELECTしてみる
mysql> SELECT * FROM reservation WHERE user_id = 1; -- 成功
+----+---------+
| ID | user_id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

-- INSERTしてみる
mysql> INSERT INTO reservation VALUES (DEFAULT, 1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -- 失敗

-- トランザクションを貼ってSELECT
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT * FROM reservation WHERE user_id = 1; -- 成功
+----+---------+
| ID | user_id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

-- トランザクションを貼ってSELECT FOR UPDATE
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql>  SELECT * FROM reservation WHERE user_id = 1 FOR UPDATE; -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

範囲検索でない かつ 結果が存在しない

トランザクションA

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM reservation WHERE user_id = 5 FOR UPDATE;
Empty set (0.01 sec)

トランザクションAを終了させない状態で、下記を実行してみる。

-- SELECTしてみる
mysql> SELECT * FROM reservation WHERE user_id = 5; -- 成功
Empty set (0.01 sec)

-- 同じuser_idのレコードをINSERTしてみる
mysql> INSERT INTO reservation VALUES (default, 5); -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 異なるuser_id（同じギャップのuser_id）にINSERTしてみる
mysql> INSERT INTO reservation VALUES (default, 6); -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 異なるuser_id（同じギャップではないuser_id）にINSERTしてみる
mysql> INSERT INTO reservation VALUES (default, 12); -- 成功
Query OK, 1 row affected (0.00 sec)

-- トランザクションを貼ってSELECT FOR UPDATE
mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM reservation WHERE user_id = 5 FOR UPDATE; -- 成功
Empty set (0.01 sec)

mysql>  SELECT * FROM reservation WHERE user_id = 6 FOR UPDATE; -- 成功
Empty set (0.00 sec)

mysql>  SELECT * FROM reservation WHERE user_id = 10 FOR UPDATE; -- 成功
+----+---------+
| ID | user_id |
+----+---------+
|  7 |      10 |
|  9 |      10 |
+----+---------+
2 rows in set (0.00 sec)

インデックスが貼られていないカラムを検索条件とする

ここまでで、インデックスが貼られたuser_idを検索条件とした動きを確認できたので、インデックスが貼られていない場合の挙動はどうなるのか確認する。

準備

インデックスが貼られていない場合の挙動はどうなるのか確認するため、削除フラグカラムを追加する。

mysql> ALTER TABLE reservation ADD COLUMN `is_deleted` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `user_id`;
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE reservation;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                       |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reservation | CREATE TABLE `reservation` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `is_deleted` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_user_country` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

is_deletedの値を適当に変更する。

mysql> UPDATE reservation SET is_deleted = 1 WHERE id IN (1, 3, 6, 9, 10);
Query OK, 5 rows affected (0.06 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM reservation;
+----+---------+------------+
| ID | user_id | is_deleted |
+----+---------+------------+
|  1 |       1 |          1 |
|  2 |       2 |          0 |
|  3 |       3 |          1 |
|  4 |       4 |          0 |
|  5 |       8 |          0 |
|  6 |       9 |          1 |
|  7 |      10 |          0 |
|  9 |      10 |          1 |
| 10 |      11 |          1 |
| 14 |      12 |          0 |
+----+---------+------------+
10 rows in set (0.01 sec)

準備ができたのでロックを確認していく。

範囲検索でない かつ 結果が存在する

-- トランザクションA
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM reservation WHERE is_deleted = 1 FOR UPDATE;
+----+---------+------------+
| ID | user_id | is_deleted |
+----+---------+------------+
|  1 |       1 |          1 |
|  3 |       3 |          1 |
|  6 |       9 |          1 |
|  9 |      10 |          1 |
| 10 |      11 |          1 |
+----+---------+------------+
5 rows in set (0.01 sec)

トランザクションAを終了させない状態で、下記を実行してみる。

-- SELECTしてみる
mysql> SELECT * FROM reservation WHERE is_deleted = 1; -- 成功
+----+---------+------------+
| ID | user_id | is_deleted |
+----+---------+------------+
|  1 |       1 |          1 |
|  3 |       3 |          1 |
|  6 |       9 |          1 |
|  9 |      10 |          1 |
| 10 |      11 |          1 |
+----+---------+------------+
5 rows in set (0.00 sec)

-- INSERTしてみる
mysql> INSERT INTO reservation VALUES (DEFAULT, 1, 1); -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- トランザクションを貼ってSELECT
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM reservation WHERE is_deleted = 1; -- 成功
+----+---------+------------+
| ID | user_id | is_deleted |
+----+---------+------------+
|  1 |       1 |          1 |
|  3 |       3 |          1 |
|  6 |       9 |          1 |
|  9 |      10 |          1 |
| 10 |      11 |          1 |
+----+---------+------------+
5 rows in set (0.00 sec)

-- トランザクションを貼ってSELECT FOR UPDATE
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM reservation WHERE is_deleted = 1 FOR UPDATE; -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- 検索条件を変えてSELECT FOR UPDATE
mysql> SELECT * FROM reservation WHERE is_deleted = 0 FOR UPDATE; -- 失敗
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

EXPLAIN結果

インデックスが貼られているクエリと貼られていないクエリでEXPLAIN結果を確認する

インデックスが貼られたカラムを検索条件とした場合

mysql> EXPLAIN SELECT * FROM reservation WHERE user_id = 5;+----+-------------+-------------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | reservation | NULL       | ref  | idx_user_country | idx_user_country | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

（当然だが）user_idに貼られたインデックスが使われている

インデックスが貼られていないカラムを検索条件とした場合

mysql> EXPLAIN SELECT * FROM reservation WHERE is_deleted = 0 FOR UPDATE;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | reservation | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

（当然だが）インデックスは使われていない

結果

インデックスが貼られているカラムを検索条件とする場合（つまり検索にインデックスが使われる場合）

  • 基本的にSELECT結果で検出されたレコードの行ロックを取得する
  • 範囲検索の場合はギャップロックを取得する

インデックスが貼られていないカラムを検索条件とする場合（つまり検索にインデックスが使われない場合）

  • 全行のロックを取得する（！！）

まとめ

今回私が調査した問題は、インデックスが使用されないクエリを発行していたため発生していた。
SELECT FOR UPDATEはの使い方

  • インデックスが確実に使われるようなクエリとする
  • 上記が難しい場合は、結果が存在する場合のみ発行するようにする。

参考

公式ページでも解説されている。

検索で検出されたインデックスレコードについては、それらの行に対して UPDATE ステートメントを発行した場合と同じように、行および関連するインデックスエントリがロックされます。 他のトランザクションは、これらの行の更新、SELECT ... FOR SHARE の実行、または特定のトランザクション分離レベルでのデータの読取りをブロックされます。 一貫性読み取りでは、読み取られたビュー内に存在するレコードに設定されたロックはすべて無視されます。 (古いバージョンのレコードはロックできません。レコードのインメモリーコピー上の Undo ログに適用することで、再構築されます。)
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking-reads.html

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0