MySQL のロック待機とデッドロック問題の分析

序文：

MySQL の運用と保守のプロセスにおいて、ロック待機とデッドロックの問題は DBA と開発者にとって非常に頭の痛い問題です。このような問題は、業務のロールバック、フリーズ、その他の障害を引き起こす可能性があります。特に、混雑したシステムでは、デッドロックが発生すると、影響はさらに深刻になります。この記事では、ロック待機とデッドロックとは何か、そしてそのような問題を分析し対処する方法を学びましょう。

1. ロック待機とデッドロックを理解する

ロック待機またはデッドロックが発生する理由は、データベースへのアクセスにロックが必要であるためです。では、なぜロックが必要なのかと疑問に思うかもしれません。その理由は、同時更新シナリオでデータの正確性を確保し、データベーストランザクションの分離を確保するためです。

図書館から「High Performance MySQL」という本を借りたいシナリオを想像してください。誰かが事前に本を借りることを防ぐために、事前に予約（ロック）することができます。このロックを追加するにはどうすればよいでしょうか。

ロックライブラリ（データベースレベルのロック）
すべてのデータベース関連ブックをロックする（テーブルレベルロック）
MySQL関連の書籍のみをロックする（ページレベルロック）
本「High Performance MySQL」のみをロックする（行レベルロック）

ロックの粒度が細かくなるほど、同時実行レベルが高くなり、実装が複雑になります。

ロック待機はトランザクション待機とも呼ばれます。後から実行されるトランザクションは、先に処理されたトランザクションがロックを解放するのを待ちます。ただし、待機時間が MySQL のロック待機時間を超えると、この例外がトリガーされます。待機タイムアウト後のエラーメッセージは、「ロック待機タイムアウトを超えました...」です。

デッドロックは、2 つのトランザクションが互いに同じリソースのロック解除を待機し、無限ループが発生する場合に発生します。デッドロックが発生すると、「ロックを取得しようとしたときにデッドロックが見つかりました...」というエラーメッセージがすぐに報告されます。

2. 現象の再発と治療

MySQL 5.7.23（分離レベルはRR）を例に、上記2つの異常現象を再現してみます。

mysql> テーブル test_tb\G の作成を表示します
************************** 1. 行 ****************************
       テーブル: test_tb
テーブルの作成: CREATE TABLE `test_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(50) NOT NULL デフォルト ''
  `col2` int(11) NOT NULL デフォルト '1',
  `col3` varchar(20) NOT NULL デフォルト ''
  主キー (`id`)、
  キー `idx_col1` (`col1`)
) エンジン=InnoDB AUTO_INCREMENT=4 デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

mysql> test_tb から * を選択します。
+----+------+------+------+
| id | 列1 | 列2 | 列3 |
+----+------+------+------+
| 1 | fdg | 1 | abc |
| 2 | a | 2 | fg |
| 3 | ghrv | 2 | rhdv |
+----+------+------+------+
セット内の 3 行 (0.00 秒)

# トランザクション 1 は最初に mysql> begin; を実行します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> select * from test_tb where col1 = 'a' for update;
+----+------+------+------+
| id | 列1 | 列2 | 列3 |
+----+------+------+------+
| 2 | a | 2 | fg |
+----+------+------+------+
セット内の 1 行 (0.00 秒)

# トランザクション 2 を実行し、mysql> begin;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> test_tb を更新し、col2 を 1 に設定します。ただし、col1 は 'a' です。
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。

上記の例外が発生する理由は、トランザクション 2 がトランザクション 1 の行ロックを待機しているが、トランザクション 1 がコミットされておらず、待機タイムアウトによってエラーが発生するためです。 InnoDB 行ロック待機タイムアウトは、innodb_lock_wait_timeout パラメータによって制御されます。このパラメータのデフォルト値は 50 秒です。つまり、デフォルトでは、トランザクション 2 は 50 秒間待機します。行ロックがまだ取得されていない場合は、待機タイムアウト例外が報告され、ステートメントはロールバックされます。

バージョン 5.7 では、ロック待機が発生すると、information_schema 内のいくつかのシステムテーブルを表示して、トランザクションステータスを照会できます。

innodb_trx 現在実行中のすべてのトランザクション。
innodb_locks 現在発生しているロック。
innodb_lock_waits ロック待機対応

# ロック待機が発生すると、innodb_trx テーブル内のすべてのトランザクションを表示できます。
 # trx_state 値が LOCK WAIT の場合、トランザクションは待機状態にあることを意味します。mysql> select * from information_schema.innodb_trx\G
************************** 1. 行 ****************************
                    トランザクションID: 38511
                 trx_state: ロック待機
               開始日時: 2021-03-24 17:20:43
     trx_requested_lock_id: 38511:156:4:2
          trx_wait_started: 2021-03-24 17:20:43
                trx_weight: 2
       trx_mysql_スレッドID: 1668447
                 trx_query: test_tb を更新し、col2 を 1 に設定し、col1 を 'a' に設定します。
       trx_operation_state: インデックス読み取り開始
         使用中のtrxテーブル: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           ロックされた行数: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: 繰り返し読み取り
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
************************** 2. 行 ****************************
                    トランザクションID: 38510
                 trx_state: 実行中
               開始日時: 2021-03-24 17:18:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 4
       trx_mysql_スレッドID: 1667530
                 trx_query: NULL
       trx_operation_state: NULL
         使用中のtrxテーブル: 0
         trx_tables_locked: 1
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           ロックされた行数: 3
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: 繰り返し読み取り
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
セット内の 2 行 (0.00 秒)

# innodb_trx フィールド値の意味 trx_id: トランザクション ID。
trx_state: トランザクションのステータス。RUNNING、LOCK WAIT、ROLLING BACK、COMMITTING などの状態が含まれます。
trx_started: トランザクションの開始時刻。
trx_requested_lock_id: 現在ロックを待機しているトランザクションの ID。INNODB_LOCKS テーブルと JOIN すると、より詳細な情報を取得できます。
trx_wait_started: トランザクションが待機を開始する時刻。
trx_weight: トランザクションの重み。
trx_mysql_thread_id: PROCESSLIST テーブルと結合できるトランザクション スレッド ID。
trx_query: トランザクションによって実行される SQL ステートメント。
trx_operation_state: トランザクションの現在の操作状態。
trx_tables_in_use: 現在のトランザクションによって実行された SQL で使用されるテーブルの数。
trx_tables_locked: 現在 SQL を実行している行ロックの数。
trx_lock_structs: トランザクションによって保持されるロックの数。
trx_isolation_level: 現在のトランザクションの分離レベル。

# sys.innodb_lock_waits ビューはトランザクションの待機状態も表示し、接続を切断する SQL を提供します。
mysql> sys.innodb_lock_waits\G から * を選択します
************************** 1. 行 ****************************
                待機開始: 2021-03-24 17:20:43
                    待機時間: 00:00:22
               待機時間秒数: 22
                ロックされたテーブル: `testdb`.`test_tb`
                ロックされたインデックス: idx_col1
                 ロックタイプ: レコード
              待機中のトランザクションID: 38511
         待機中のtrx開始: 2021-03-24 17:20:43
             待機中のtrxの年齢: 00:00:22
     待機中の trx 行ロック: 1
   待機中の trx 行の変更: 0
                 待機中のpid: 1668447
               waiting_query: test_tb を更新し、col2 を 1 に設定し、col1 を 'a' にします。
             待機ロックID: 38511:156:4:2
           待機ロックモード: X
             ブロッキング_trx_id: 38510
                ブロッキングpid: 1667530
              ブロッキングクエリ: NULL
            ブロッキングロックID: 38510:156:4:2
          ブロッキングロックモード: X
        ブロッキング_trx_started: 2021-03-24 17:18:54
            ブロッキング_trx_age: 00:02:11
    ブロック_trx_行ロック: 3
  ブロッキング_trx_rows_modified: 0
     sql_kill_blocking_query: クエリ 1667530 を強制終了
sql_kill_blocking_connection: 1667530 を強制終了する

sys.innodb_lock_waits ビューは、トランザクションの待機ステータスを統合し、ブロックの原因を強制終了する kill ステートメントを提供します。ただし、リンクを削除するかどうかについては、依然として総合的な検討が必要です。

デッドロックはロック待ちとは少し異なります。デッドロック現象も簡単に再現してみましょう。

# 2 つのトランザクションを開きます # トランザクション 1 が実行されます mysql> update test_tb set col2 = 1 where col1 = 'a';
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

# トランザクション 2 mysql> update test_tb set col2 = 1 where id = 3;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

# トランザクション 1 に戻り、Enter キーを押します。このステートメントはロック待機状態です。mysql> update test_tb set col1 = 'abcd' where id = 3;
クエリは正常、1 行が影響を受けました (5.71 秒)
一致した行: 1 変更された行: 1 警告: 0

# トランザクション 2 に戻り、再度実行します。このとき、2 つは互いに待機状態となり、デッドロックが発生します。mysql> update test_tb set col3 = 'gddx' where col1 = 'a';
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

デッドロックが発生すると、ロールバックするトランザクションが選択されます。デッドロックの原因を調べるには、show engine innodb status を実行してデッドロックログを表示します。デッドロックログとビジネスロジックに基づいて、デッドロックの原因をさらに特定できます。

実際のアプリケーションでは、デッドロックを回避するように努める必要があります。次の点から始めることができます。