MySQL & MariaDBの世界

MySQLのロックを確認・解除する方法｜デッドロック回避のベストプラクティス

MySQL
佐川 直弘 | Naohiro Sagawa
By 佐川 直弘 | Naohiro Sagawa (運営者)
※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　
目次

1. はじめに

MySQLはデータベース管理システムとして広く使用されていますが、複数のクエリが同じデータにアクセスする際にはロック機構が働きます。ロックはデータの一貫性を保つために不可欠ですが、誤った管理をするとデッドロックやパフォーマンスの低下を引き起こす原因になります。

この記事では、MySQLにおけるロックの基本概念を理解し、ロック状態を確認する方法、解除方法、デッドロック対策 について詳しく解説します。

この記事で学べること

  • MySQLのロックの種類とその影響
  • バージョンごとのロック確認方法
  • 安全なロック解除の手順
  • デッドロックを防ぐための実践的アドバイス

それでは、まず MySQLのロックの基本概念 について説明していきます。

2. MySQLのロックの基本概念

データベースにおける「ロック」とは、複数のトランザクションが同時にデータを変更しようとした際に、データの整合性を保つためにアクセスを制限する仕組みです。ロックが適切に管理されていないと、パフォーマンスの低下やデッドロックが発生 する可能性があります。

2.1 ロックの主な種類

MySQLでは、データの保護レベルに応じて いくつかのロックの種類 が存在します。

行ロック（Row Lock）

  • 特定の行のみをロックし、他のトランザクションの影響を最小限に抑える。
  • InnoDBエンジンのみ対応。
  • SELECT ... FOR UPDATE や SELECT ... LOCK IN SHARE MODE を使用すると発生。

テーブルロック（Table Lock）

  • テーブル全体をロックするため、複数のクエリが同時に実行できなくなる。
  • MyISAMエンジンで使用されることが多い。
  • LOCK TABLES 文を使用すると発生。

意図的ロック（Intention Lock）

  • 行ロックとテーブルロックが競合しないように調整するためのロック。
  • InnoDBでのみ使用され、自動的に管理される。

デッドロック（Deadlock）

  • 複数のトランザクションが互いにロックを待っている状態。
  • 適切なトランザクション設計をしないと、処理が進まなくなる。

2.2 具体的なロックの発生例

ロックがどのように発生するのか、具体的なSQLクエリを用いて説明します。

行ロックの発生

以下のSQLを実行すると、特定の行がロックされます。

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- このトランザクションがCOMMITまたはROLLBACKされるまで他のセッションがこの行を更新できない

この状態で、別のセッションが同じ行を更新しようとするとロック待ち（ロック競合）が発生します。

テーブルロックの発生

テーブル全体をロックする場合は、以下のコマンドを使用します。

LOCK TABLES products WRITE;
-- すべての読み書きが完了するまで他のセッションがproductsテーブルを変更不可

このロックが解除されるまでは、他のユーザーが products テーブルのデータを変更できません。

デッドロックの発生

デッドロックが発生する典型的な例を示します。

-- セッション1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- セッション2の処理を待機中...

-- セッション2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- セッション1の処理を待機中...

-- セッション1（さらに次のSQLを実行）
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- ここでデッドロック発生

このように、お互いに相手のロックを解除するのを待っている状態 になると、デッドロックが発生します。

3. MySQLのロック状態を確認する方法（バージョン別）

ロックが発生しているかどうかを調べるには、MySQLのバージョンごとに適したコマンドを実行する必要があります。

3.1 MySQL 5.6以前のロック確認方法

MySQL 5.6以前では、SHOW ENGINE INNODB STATUS\G; を使用してロック情報を確認できます。

SHOW ENGINE INNODB STATUS\G;

このコマンドを実行すると、現在発生しているロックの詳細情報が表示されます。

3.2 MySQL 5.7のロック確認方法

MySQL 5.7以降では、sys.innodb_lock_waits テーブルを利用するのが簡単です。

SELECT * FROM sys.innodb_lock_waits;

このテーブルを確認することで、どのトランザクションがロック待ち状態になっているかを把握できます。

3.3 MySQL 8.0以降のロック確認方法

MySQL 8.0以降では、performance_schema.data_locks を活用するとより詳細な情報を取得できます。

SELECT * FROM performance_schema.data_locks;

また、ロックを保持しているセッションを特定するには、以下のSQLを使用します。

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <プロセスID>;

これにより、ロックを発生させているプロセスを突き止めることができます。

4. MySQLのロックを解除する方法（リスクを明記）

MySQLでロックが発生した場合、適切に解除しないと処理が滞り、データベースのパフォーマンスが低下する可能性があります。
このセクションでは、ロックの解除方法とそのリスクについて詳しく解説します。

4.1 ロックを保持しているセッションの特定方法

ロックを解除する前に、どのセッションがロックを保持しているのかを特定する必要があります。以下のSQLを使用すると、ロックが発生しているセッションを確認できます。

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

このクエリを実行すると、ロックを待機しているセッションがリストアップされます。

また、MySQL 8.0以降では、以下のクエリで詳細なロック情報を取得できます。

SELECT * FROM performance_schema.data_locks;

4.2 KILL コマンドを使用したロック解除

ロックを保持しているセッションを特定した後、そのプロセスを強制終了することでロックを解除できます。

1. ロックを保持しているプロセスを確認

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. KILL コマンドでセッションを終了

KILL <プロセスID>;

例えば、ID=12345 のプロセスを強制終了する場合、以下のように実行します。

KILL 12345;

⚠️ KILL コマンドのリスク

  • 強制終了されたトランザクションはROLLBACKされる
  • 例えば、途中で終了した UPDATE 文の変更内容は破棄される可能性がある。
  • アプリケーションエラーの原因になることもある
  • もし KILL を頻繁に実行する必要があるなら、アプリケーション側の設計を見直すべき。

4.3 ROLLBACK によるロック解除（安全な方法）

KILL コマンドを使用する前に、可能であれば ロックを発生させているトランザクションを手動で終了する 方法を試してください。

1. まず、現在のセッションを確認

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. 問題のあるトランザクションが見つかったら ROLLBACK を実行

ROLLBACK;

この方法ならデータの一貫性を維持したまま、ロックを解除できます。

4.4 SET innodb_lock_wait_timeout でロック解除を自動化

ロックの解除を手動で行うのではなく、ロック待機時間を設定することで 一定時間ロックが解除されなければ自動的にタイムアウトさせる 方法もあります。

SET innodb_lock_wait_timeout = 10;

この設定を行うと、10秒以内にロックが解除されない場合は、エラーを返してトランザクションが自動的に終了します。

5. MySQLロックに関する注意点とベストプラクティス

ロックを適切に管理することで、デッドロックやパフォーマンス低下のリスクを軽減 できます。以下に、ロックを最小限に抑え、効率的に管理するためのベストプラクティスを紹介します。

5.1 デッドロックを防ぐ方法

デッドロックを防ぐためには、以下のポイントを意識しましょう。

1. トランザクションの実行順序を統一する

  • 例えば、複数のテーブルを更新する場合は 必ず同じ順番で更新 する。
  • 例：
-- OK: 常に orders → customers の順番で更新
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: 実行順が異なるとデッドロックの原因に

-- セッション1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- セッション2（逆の順番で実行するとデッドロックの可能性）
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. 短いトランザクションを意識する

  • 可能な限り速やかに COMMIT または ROLLBACK する
  • 長時間実行されるトランザクションは他のプロセスに影響を与えるため避ける。

3. 適切なインデックスを設定する

  • 適切なインデックスを作成することで 不要なロックを回避 できる。
  • 例：orders テーブルに customer_id のインデックスを追加することで、特定の行のみをロックする ようになる。
CREATE INDEX idx_customer_id ON orders (customer_id);

6. まとめ

  • MySQLのロックには 行ロック、テーブルロック、意図的ロック などがあり、適切に管理しないとデッドロックやパフォーマンス低下の原因になる。
  • ロック状態を確認する方法は MySQLのバージョンによって異なる ため、自分の環境に適した方法を選択する。
  • ロックを解除する際は慎重に！
  • KILL コマンドを使う前に、ROLLBACK を試す
  • SET innodb_lock_wait_timeout を設定して自動的にロックを解除する
  • デッドロックを防ぐためには、トランザクションの実行順序を統一 し、短いトランザクションを意識する ことが重要。

7. FAQ（よくある質問）

Q1. MySQLのロック状態を確認する最も簡単なコマンドは？

  • A1. MySQL 8.0以降なら SELECT * FROM performance_schema.data_locks; を使うと簡単にロック状態を確認できます。

Q2. デッドロックが発生した場合、どう対処すればよいですか？

  • A2. まず SHOW ENGINE INNODB STATUS\G; を実行し、デッドロックの原因を特定しましょう。その後、トランザクションの実行順序を見直すことで回避できます。

Q3. KILL コマンドを使うとデータが破損する可能性はある？

  • A3. 強制終了すると、未完了のトランザクションが ROLLBACK されるため、データの一貫性に影響を与える可能性があります。慎重に使用してください。

Q4. デッドロックを防ぐには？

  • A4. 以下の方法が有効です：
  • トランザクションの実行順序を統一
  • 短いトランザクションを意識
  • 適切なインデックスを設定

Q5. ロックを減らしてMySQLのパフォーマンスを向上させるには？

  • A5.
  • 適切なインデックスを設計 し不要なロックを減らす
  • 短いトランザクションを意識 しロック時間を短縮
  • テーブル全体のロック（LOCK TABLES）を避ける
  • リードレプリカを活用 し、読み取り処理を分散