🐎

インデックスなし UPDATE が引き起こす MySQL のロック競合を検証する

に公開
MySQL
インデックス
ロック競合
tech

はじめに

「MySQL（InnoDB）において、以下の UPDATE 文を実行した際、
インデックスが貼られていないと行ロックではなく、テーブルロックのような挙動になる」
という説明を目にしました。

UPDATE orders
SET status = 'done'
WHERE user_id = 123;

WHERE 句で条件を指定しているにもかかわらず、本当にそのような挙動になるのだろうか？
と疑問に思ったため、実際に検証してみます。

参照系クエリの速度面では、
インデックスがない場合にフルスキャンとなりパフォーマンスが低下することは理解していましたが、更新時のロックについても同様に広範囲にロックをかけるという点は、これまで明確に意識できていませんでした。

事前準備

検証用テーブルを作成

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status VARCHAR(20),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ダミーデータを投入

orders テーブルにデータを投入します。
今回はロックの挙動を見るのが目的なので、データの内容自体は単純にしています。

INSERT INTO orders (user_id, status)
SELECT
  FLOOR(RAND() * 1000),
  'pending'
FROM
  (SELECT 1 FROM information_schema.tables LIMIT 5000) t;

※ information_schema.tables を使うことで、手軽に複数行を生成しています。

データ投入の確認

mysql> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|      330 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM orders LIMIT 3;
+----+---------+---------+---------------------+
| id | user_id | status  | created_at          |
+----+---------+---------+---------------------+
|  1 |     215 | pending | 2026-01-04 00:30:49 |
|  2 |     166 | pending | 2026-01-04 00:30:49 |
|  3 |     183 | pending | 2026-01-04 00:30:49 |
+----+---------+---------+---------------------+

想定通りのデータ投入ができています。

検証

インデックスなしの場合

セッションA（トランザクション開始）

START TRANSACTION;

UPDATE orders
SET status = 'done'
WHERE user_id = 123;

この状態で COMMIT せずに処理を止めておきます。

セッションB（別ターミナルで接続）

UPDATE orders
SET status = 'done'
WHERE user_id = 124;

すると、しばらく待つと以下のタイムアウトエラーが発生しました。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

これは、ロックを取得しようとして待機していたが、lock_wait_timeout を超えたため MySQL が処理を中断したことを意味します。

ロック状態の確認

SELECT * FROM performance_schema.data_lock_waits\G

*************************** 1. row ***************************
ENGINE: INNODB
REQUESTING_ENGINE_TRANSACTION_ID: 1852
REQUESTING_THREAD_ID: 62
BLOCKING_ENGINE_TRANSACTION_ID: 1851
BLOCKING_THREAD_ID: 53

performance_schema.data_lock_waits は「誰が」「どのロックを」「誰にブロックされて待っているか」を 1 行 = 1 ロック待ち で表すテーブルです。

※出力結果のうち必要な部分だけ抜粋しています。

この結果が示すこと

REQUESTING 側（待たされている）

  • Transaction ID 1852
    ロックを取得しようとしているトランザクション
  • Thread ID 62
    停止しているMySQLセッションB

BLOCKING 側（ロック保持者）

  • Transaction ID 1851
    先にロックを取得したトランザクション
  • Thread ID 53
    COMMITしていないセッションA

=> トランザクション 1852 がロックを取得しようとして待っている。
しかし、トランザクション 1851 がすでにそのロックを保持している。
そのため、UPDATE がブロックされている。

この理由

インデックスが存在しないため、MySQL は user_id = 123 を特定できず、
全表スキャンを行い、その過程で大量の行に排他ロックを取得します。

結果として、

  • セッションA が広範囲の行をロック
  • セッションB が別の user_id を更新しようとしても、同じロック範囲に衝突
    ロック待ちが発生します。

インデックスを追加して再検証

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

同じ手順で検証すると、ロック待ちは発生しなくなりました。
インデックスにより、対象となる user_id の行だけを即座に特定して、
必要最小限の行ロックのみを取得できるためだと思われます。

まとめ

  • InnoDB は行ロックだが、インデックスがないと実質的にテーブルロックのように見える
  • 更新条件に使うカラムには、適切なインデックスが必要

公式リンク

Discussion