【MySQL】ロックはインデックスレコード上に設定されるので、たとえレコードが取得できないwhere条件でもロックが設定される具体例
mysqlのドキュメントに
SQL ステートメントの処理時にスキャンされるすべてのインデックスレコード上に、レコードロックが設定されます。 行を除外する WHERE 条件がステートメント内に存在するかどうかは、関係ありません。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html
とあります。
これについて、簡単で具体的な例を紹介したいと思います。
各種設定
| バージョン | エンジン | トランザクション分離レベル |
|---|---|---|
| 8.0.32 | InnoDB | REPEATABLE-READ |
スキーマ&データ
スキーマ
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| deleted_at | datetime | YES | | NULL | |
| created_at | datetime | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | NULL | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+
データ
id 3が論理削除されているケース
+----+--------------------------+---------------------+---------------------+---------------------+
| id | name | deleted_at | created_at | updated_at |
+----+--------------------------+---------------------+---------------------+---------------------+
| 1 | 本 | NULL | 2025-03-26 23:07:36 | NULL |
| 2 | 石鹸 | NULL | 2025-03-26 23:10:10 | NULL |
| 3 | バスケットボール | 2025-03-30 20:21:51 | 2025-03-26 23:10:19 | 2025-03-30 20:21:51 |
| 4 | Tシャツ | NULL | 2025-03-26 23:10:37 | NULL |
| 5 | タンクトップ | NULL | 2025-03-26 23:10:44 | NULL |
| 6 | テレビ | NULL | 2025-03-26 23:10:54 | NULL |
+----+--------------------------+---------------------+---------------------+---------------------+
id 3のロックを取得する
実行SQL
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from items where id = 3 and deleted_at is null for update;
Empty set (0.00 sec)
for updateで排他ロック取得を狙っています。
Empty setとなりwhereの条件に当てはまるレコードが見つかりませんでした。
この状態でロック獲得ができているのか確認するために、
保持およびリクエストされたデータロックを取得できるdata_locks テーブルを使用します。
実行SQL
mysql> select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA, INDEX_NAME from performance_schema.data_locks;
+-------------+-----------+---------------+-------------+-----------+------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | INDEX_NAME |
+-------------+-----------+---------------+-------------+-----------+------------+
| items | TABLE | IX | GRANTED | NULL | NULL |
| items | RECORD | X,REC_NOT_GAP | GRANTED | 3 | PRIMARY |
+-------------+-----------+---------------+-------------+-----------+------------+
2 rows in set (0.01 sec)
OBJECT_NAME
はロックされたテーブルの名前が表示されます。今回だと
→itemsテーブル
LOCK_TYPE
はInnoDB の場合、行レベルロックの場合は RECORD、テーブルレベルロックの場合は TABLE と表示されます。今回だと
→行ロック(RECORD)
LOCK_MODE
はロックのリクエスト方法が表示されます。今回だと
→排他ロック(X[,GAP])
LOCK_STATUS
はロックリクエストのステータスを表しており、
InnoDB の場合、許可される値は、GRANTED (ロックが保持されている) および WAITING (ロックが待機されている)と表示されます。今回だと
→GRANDTED(ロックが保持されている)
LOCK_DATA
はInnoDB の場合、LOCK_TYPE が RECORD の場合は値が表示され、それ以外の場合は NULL 値が表示されます。値に関して、主キーインデックに設定されたロック対象のレコードの主キーの値が表示される。今回だと
→3
INDEX_NAME
はロックされたインデックスの名前が表示されます。今回だと
PRIMARY(プライマリーインデックス)
上記結果より、
インデックスに対してロック獲得を行なっているため、
そのインデックス条件に当てはまらないdeleted_atカラムの条件は、
ロック獲得の条件において無視されています。
改めてmysqlドキュメントの引用
SQL ステートメントの処理時にスキャンされるすべてのインデックスレコード上に、レコードロックが設定されます。 行を除外する WHERE 条件がステートメント内に存在するかどうかは、関係ありません。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html
おまけ(インデックスを作成していないカラムを条件にロック取得を試みる)
ステートメントに適したインデックスがなく、MySQL がステートメントを処理するためにテーブル全体をスキャンする必要がある場合は、テーブルのすべての行がロックされます。その結果、そのテーブルへのほかのユーザーによるすべての挿入がブロックされます。 クエリーで不必要に複数の行がスキャンされないように、適切なインデックスを作成することが重要です。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html
上記に加えて
実際には、InnoDB は常にインデックス (GEN_CLUST_INDEX) を作成するため、INDEX_NAME は InnoDB テーブルに対して NULL 以外です。
https://dev.mysql.com/doc/refman/8.0/ja/innodb-locks-set.html
つまりインデックスを作成されていないカラムを条件にしても、InnoDBを使っていればNULL以外。
クラスタインデックスの生成に関しては以下ドキュメントを参照。
テーブル上で PRIMARY KEY を定義すると、InnoDB ではそれがクラスタ化されたインデックスとして使用されます。
実際にやってみます。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from items where name = 'バスケットボール' for update;
+----+--------------------------+---------------------+---------------------+---------------------+
| id | name | deleted_at | created_at | updated_at |
+----+--------------------------+---------------------+---------------------+---------------------+
| 3 | バスケットボール | 2025-03-30 20:21:51 | 2025-03-26 23:10:19 | 2025-03-30 20:21:51 |
+----+--------------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
select OBJECT_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA, INDEX_NAME from performance_schema.data_locks;
+-------------+-----------+-----------+-------------+------------------------+------------+
| OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | INDEX_NAME |
+-------------+-----------+-----------+-------------+------------------------+------------+
| items | TABLE | IX | GRANTED | NULL | NULL |
| items | RECORD | X | GRANTED | supremum pseudo-record | PRIMARY |
| items | RECORD | X | GRANTED | 2 | PRIMARY |
| items | RECORD | X | GRANTED | 4 | PRIMARY |
| items | RECORD | X | GRANTED | 5 | PRIMARY |
| items | RECORD | X | GRANTED | 6 | PRIMARY |
| items | RECORD | X | GRANTED | 1 | PRIMARY |
| items | RECORD | X | GRANTED | 3 | PRIMARY |
+-------------+-----------+-----------+-------------+------------------------+------------+
8 rows in set (0.01 sec)
となり、1からsupremum pseudo-record (インデックレコードの上限を表す疑似レコード)までのロックを取得しているため、
別トランザクションからレコードの追加しようとするとにWAITING、ロック解放待ち状態となりました。
つまりトランザクションにおいてロックを獲得時 以降、
別トランザクションの影響例えば、
nameカラムが更新されない、かつレコードが追加されないことを保証しています。
Discussion