ApsaraDB RDS:Mengatasi kegagalan database yang disebabkan oleh kunci metadata

Latar Belakang

MySQL 5.5 memperkenalkan kunci metadata (MDL) untuk memastikan konsistensi antara operasi Data Definition Language (DDL) dan Data Manipulation Language (DML). Namun, pemblokiran dapat terjadi dalam beberapa skenario, seperti ketika operasi ALTER dilakukan selama operasi DML berlangsung atau ketika operasi ALTER dicoba saat kueri jangka panjang sedang aktif.

Skenario

• Membuat atau menghapus indeks.

• Mengubah struktur tabel.

• Melakukan operasi pemeliharaan tabel, seperti optimize table atau repair table.

• Menghapus tabel.

• Mengambil write lock tingkat tabel.

Penyebab

• Kueri jangka panjang sedang aktif pada tabel tersebut.

• Transaksi yang dimulai secara eksplisit atau implisit belum di-commit atau di-rollback. Misalnya, kueri telah selesai tetapi transaksinya tetap terbuka.

• Terdapat transaksi yang mencakup kueri gagal pada tabel tersebut.

Langkah-langkah

1. Masuk ke database ApsaraDB RDS menggunakan DMS.

2. Pada SQL Console, jalankan perintah show full processlist untuk melihat status semua thread database.

3. Periksa apakah kolom State berisi banyak nilai Waiting for table metadata lock. Jika status Waiting for table metadata lock muncul, berarti terjadi pemblokiran.

4. Cari session ID dari sesi yang menyebabkan pemblokiran.

   1. Untuk sesi dengan status Waiting for table metadata lock, periksa kolom Info untuk mengidentifikasi tabel yang ingin diaksesnya, misalnya sbtest2.

   2. Tinjau kolom Info dari sesi lain untuk menemukan sesi yang sedang mengoperasikan tabel sbtest2. Catat Id sesi tersebut.

      Catatan

      Anda perlu menemukan sesi yang benar-benar memegang kunci pada tabel tersebut, bukan sesi yang sedang menunggu kunci. Sesi yang menunggu kunci akan menampilkan status Waiting for table metadata lock. Anda dapat mengidentifikasi sesi pemblokir dengan menganalisis kolom State dan Info.

      Sebagai contoh, untuk sesi yang memiliki State berupa Waiting for table metadata lock, Anda dapat mengetahui dari perintah di kolom Info-nya bahwa sesi tersebut perlu mengoperasikan tabel sbtest2. Di antara sesi-sesi lain yang juga perlu mengoperasikan tabel sbtest2, Anda dapat mengetahui dari kolom State sesi dengan Id 267 bahwa sesi tersebut sedang mengoperasikan tabel sbtest2 dan menyebabkan pemblokiran.

      Catatan

      Status Sending data hanyalah contoh. Status aktual sesi pemblokir dapat bervariasi.

      Anda juga dapat menjalankan kueri berikut untuk menemukan transaksi jangka panjang. Jika pengguna yang memulai pernyataan pemblokir berbeda dari pengguna saat ini, Anda harus masuk sebagai pengguna tersebut untuk menghentikan sesi tersebut.

      select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,
        (select 
               id, time
           from
               information_schema.processlist
           where
               time = (select 
                       max(time)
                   from
                       information_schema.processlist
                   where
                       state = 'Waiting for table metadata lock'
                           and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p
        where timestampdiff(second, i.trx_started, now()) > p.time
        and i.trx_mysql_thread_id  not in (connection_id(),p.id);

5. Jalankan kill <session ID> (misalnya, kill 267) untuk menghentikan sesi dan melepaskan kunci metadata.

Rekomendasi

• Lakukan operasi seperti membuat atau menghapus indeks selama jam sepi.

• Aktifkan autocommit.

• Atur parameter lock_wait_timeout ke nilai yang lebih rendah.

• Pertimbangkan penggunaan event untuk secara otomatis menghentikan transaksi jangka panjang. Sebagai contoh, event berikut menghentikan transaksi apa pun yang telah berjalan lebih dari 60 menit.

  create event my_long_running_trx_monitor
  on schedule every 60 minute
  starts '2015-09-15 11:00:00'
  on completion preserve enable do
  begin
    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;
  end;