DMS を使用したメタデータのロック解除 - ApsaraDB RDS - Alibaba Cloud ドキュメントセンター

このトピックでは、データ管理 (DMS) を使用して、テーブルに対する後続の操作をブロックするメタデータロックを解放する方法について説明します。

背景情報

MySQL 5.5は、メタデータロックを使用して、DDLとDML操作の一貫性を確保します。ただし、メタデータロックは、DML操作の進行中または長い時間範囲にわたるデータクエリが処理されている間に実行されるALTER操作など、テーブルに対する後続の操作をブロックする可能性があります。

シナリオ

テーブルからインデックスを作成し、その後削除します。
テーブルの構造を変更します。
最適化および修復操作を含むテーブルを管理します。
テーブルを削除します。
テーブルレベルの書き込みロックを取得します。

原因

テーブルから長い期間が対象となるデータを照会するリクエストが処理されています。
明示的または暗黙的に開始されたトランザクションは、エラー発生後にコミットまたはロールバックされません。トランザクションは、クエリ動作を完了するためのトランザクションを含む。
テーブル内のトランザクションの照会に失敗しました。

手順

RDSインスタンスにログインします。詳細については、「DMSを使用したApsaraDB RDS For MySQLインスタンスへのログイン」をご参照ください。
[SQLコンソール] タブで、[SHOW FULL PROCESSLIST] ステートメントを実行して、データベース内のすべてのスレッドのステータスを表示します。
出力で、多数の行の [状態] 列に [テーブルメタデータロック待ち] が表示されているかどうかを確認します。 テーブルメタデータロック待ち状態は、セッションがブロックされていることを示します。
ブロックされているセッションのIDを特定します。
1. [Waiting for table metadata lock session] 状態にあるセッションの [Info] 列を表示して、セッションのテーブルを識別します。例: sbtest2
2. 他のセッションの [情報] 列を表示して、sbtest2テーブルで操作を実行するセッションを識別します。セッションのIDを記録します。
  説明メタデータロックのリリースを待機しているセッションではなく、常にテーブルを占有するセッションを特定する必要があります。 テーブルメタデータロック待ち状態は、セッションがメタデータロックの解放を待っていることを示します。 [状態] 列と [情報] 列に基づいて、2種類のセッションを区別できます。
  次の例では、一部のセッションがテーブルメタデータロック待ち状態になっています。 Info列のステートメントを確認すると、これらのセッションがsbtest2テーブルの管理を待機していると判断できます。 Idが267のセッションの [状態] および [情報] 列の情報を確認すると、セッションがsbtest2テーブルを管理しており、後続の操作をブロックしていると判断できます。
  説明 Sending data状態は参照用にのみ提供されます。
  次のステートメントを実行して、長期間保留中のトランザクションを照会できます。ロックを保持しているセッションを終了するには、セッションを開始するアカウントを使用してデータベースにログインする必要があります。
```
information_schema.innodb_trx iからconcat('kill ',i.trx_mysql_thread_id,';') を選択し、
  (select 
         id, time
     から
         information_schema.processlist
     where
         time = (select 
                 max(time)
             から
                 information_schema.processlist
             where
                 state = 'Waiting for table metadata lock'
                     ('alter' 、'optim' 、'repai' 、'lock '、'drop' 、'creat' 、'trunc')) の部分文字列 (info、1、5) p
  where timestampdiff(second, i.trx_started, now()) > p.time
  とi.trx_mysql_thread_idが存在しない (connection_id(),p.id); 
```
kill Idと入力して、Idパラメーターで指定されたセッションを終了します。たとえば、kill 267コマンドを実行します。セッションが終了すると、セッションのメタデータロックが解除されます。

次のステップ

オフピーク時間帯にテーブルを管理します (インデックスの作成や削除など)。
自動コミットモードを有効にします。
lock_wait_timeout パラメーターを小さな値に設定します。

イベントを使用して、長期間実行されているトランザクションを終了します。次の例では、60分以上実行されているトランザクションは終了します。

create event my_long_running_trx_monitor
60分ごとのスケジュール
開始 '2015-09-15 11:00:00'
完了時に有効にする
始める
  declare v_sql varchar(500);
  declare no_more_long_running_trx integer default 0; 
  declare c_tid cursor for
    select concat ('kill ',trx_mysql_thread_id,';') 
    from information_schema.innodb_trx 
    where timestampdiff(minute,trx_started,now()) >= 60;
  declare continue handler for not found
    set no_more_long_running_trx=1;
  open c_tid;
  repeat
    fetch c_tid into v_sql;
 set @v_sql=v_sql;
 prepare stmt from @v_sql;
 execute stmt;
 deallocate prepare stmt;
  until no_more_long_running_trx end repeat;
  close c_tid;
終了;