ApsaraDB RDS：Gunakan DMS untuk melepaskan kunci metadata

Informasi latar belakang

MySQL 5.5 atau versi lebih baru menggunakan penguncian metadata untuk memastikan konsistensi antara operasi DDL dan DML. Namun, penguncian metadata dapat menghalangi operasi berikutnya pada tabel, seperti operasi ALTER yang dilakukan saat operasi DML sedang berlangsung atau saat permintaan data dengan rentang waktu panjang sedang diproses.

Skenario

• Membuat dan menghapus indeks dari tabel.
• Mengubah struktur tabel.
• Mengelola tabel, termasuk operasi optimasi dan perbaikan.
• Menghapus tabel.
• Mendapatkan kunci tulis tingkat tabel.

Penyebab

• Permintaan untuk menanyakan data dengan rentang waktu panjang dari sebuah tabel sedang diproses.
• Transaksi yang dimulai secara eksplisit atau implisit tidak dikomit atau dibatalkan setelah terjadi kesalahan. Transaksi tersebut mencakup transaksi untuk menyelesaikan operasi penanyaaan.
• Transaksi penanyaaan dalam tabel gagal.

Prosedur

1. Masuk ke instance RDS Anda. Untuk informasi lebih lanjut, lihat Gunakan DMS untuk Masuk ke Instance ApsaraDB RDS.
2. Pada tab SQL Console, jalankan pernyataan SHOW FULL PROCESSLIST untuk melihat status semua thread di database.
3. Dalam output, periksa apakah Waiting for table metadata lock ditampilkan di kolom State untuk sejumlah besar baris. Status Waiting for table metadata lock menunjukkan bahwa sesi terblokir.
4. Identifikasi ID sesi yang terblokir.
   1. Lihat kolom Info dari sesi yang berada dalam status Waiting for table metadata lock session untuk mengidentifikasi tabel dari sesi tersebut. Contoh: sbtest2.
   2. Lihat kolom Info dari sesi lain untuk mengidentifikasi sesi yang melakukan operasi pada tabel sbtest2. Catat IDs dari sesi-sesi tersebut.
      Catatan Anda harus mengidentifikasi sesi yang selalu menduduki tabel, bukan sesi yang menunggu pelepasan kunci metadata. Status Waiting for table metadata lock menunjukkan bahwa sesi sedang menunggu pelepasan kunci metadata. Anda dapat membedakan kedua jenis sesi berdasarkan kolom State dan Info.
      Dalam contoh berikut, beberapa sesi berada dalam status Waiting for table metadata lock. Jika Anda mengamati pernyataan di kolom Info, Anda dapat menentukan bahwa sesi-sesi tersebut sedang menunggu untuk mengelola tabel sbtest2. Jika Anda mengamati informasi di kolom State dan Info dari sesi yang Id-nya adalah 267, Anda dapat menentukan bahwa sesi tersebut sedang mengelola tabel sbtest2 dan menghalangi operasi berikutnya.

      Catatan Status Sending data hanya disediakan sebagai referensi.
      
      Anda dapat menjalankan pernyataan berikut untuk menanyakan transaksi yang tertunda untuk jangka waktu lama. Untuk menghentikan sesi yang memegang kunci, Anda harus masuk ke database menggunakan akun yang memulai 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. Masukkan kill Id untuk menghentikan sesi yang ditentukan oleh parameter Id. Sebagai contoh, jalankan perintah kill 267. Setelah sesi dihentikan, kunci metadata dari sesi tersebut dilepaskan.

Apa yang harus dilakukan selanjutnya

• Kelola tabel selama jam-jam sepi, termasuk membuat dan menghapus indeks.
• Aktifkan mode autocommit.
• Tetapkan parameter lock_wait_timeout ke nilai yang lebih kecil.
• Gunakan event untuk menghentikan transaksi yang berjalan untuk jangka waktu lama. Dalam contoh berikut, transaksi yang berjalan lebih dari 60 menit dihentikan:

  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;