:Tangani tunggu kunci baris dan timeout di RDS for MySQL

Deskripsi masalah

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Penyebab

Dalam kondisi normal, sesi yang memegang kunci baris eksklusif menyelesaikan transaksinya dengan cepat dan melepaskan kunci tersebut melalui commit atau rollback. Sesi yang menunggu mendapatkan kunci sebelum timeout terjadi dan dapat melanjutkan proses. Timeout tunggu kunci terjadi ketika sebuah sesi memegang kunci baris lebih lama dari yang diizinkan oleh innodb_lock_wait_timeout.

Pola umum:

• Koneksi idle dengan transaksi terbuka: Aplikasi membuka transaksi dan melakukan beberapa pekerjaan, tetapi tidak pernah melakukan commit atau rollback—sehingga kunci tetap dipegang tanpa batas waktu.

• DML batch berdurasi panjang: Perintah UPDATE atau DELETE besar memegang kunci baris dalam periode yang lama saat memproses banyak baris.

• Sesi aplikasi terputus: Sesi database klien aplikasi terputus tanpa instans mendeteksi pemutusan koneksi tersebut, sehingga transaksi dan kuncinya tidak dilepaskan.

Solusi

Langkah-langkah berikut hanya berlaku saat tunggu kunci baris sedang terjadi. Nilai default innodb_lock_wait_timeout di RDS for MySQL adalah 50 detik, sehingga sulit mengamati tunggu kunci secara langsung. Untuk mereproduksi masalah ini di lingkungan pengujian, atur innodb_lock_wait_timeout ke nilai yang besar—jangan ubah pengaturan ini di lingkungan produksi.

1. Masuk ke instans RDS for MySQL menggunakan DMS.

2. Jalankan kueri berikut untuk mengidentifikasi sesi mana yang memblokir sesi lain, beserta kueri yang diblokir dan durasi blocker telah berjalan:

   SELECT
     r.trx_mysql_thread_id    AS waiting_thread,
     r.trx_query              AS waiting_query,
     b.trx_mysql_thread_id    AS blocking_thread,
     b.trx_query              AS blocking_query,
     b.trx_started            AS blocking_started,
     TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_duration_sec
   FROM information_schema.INNODB_LOCK_WAITS w
   JOIN information_schema.INNODB_TRX b ON w.blocking_trx_id  = b.trx_id
   JOIN information_schema.INNODB_TRX r ON w.requesting_trx_id = r.trx_id;

   Hasilnya menampilkan ID thread dan kueri saat ini dari sesi yang menunggu maupun sesi yang memblokir. Gunakan blocking_duration_sec untuk mengukur berapa lama blocker telah memegang kunci.

   Untuk tampilan yang lebih detail, kueri masing-masing tabel secara terpisah:

   -- Semua transaksi aktif
   SELECT * FROM information_schema.INNODB_TRX;
   
   -- Transaksi yang menunggu kunci
   SELECT * FROM information_schema.INNODB_LOCK_WAITS;
   
   -- Kunci yang sedang dipegang
   SELECT * FROM information_schema.INNODB_LOCKS;

3. Blocker adalah sesi yang memegang kunci sehingga mencegah operasi Data Manipulation Language (DML) di sesi lain. Jika rollback transaksi pada blocker dapat diterima, hentikan sesi tersebut menggunakan ID thread dari Langkah 2:

   KILL <blocking_thread_id>;

   Perintah ini akan melakukan rollback transaksi terbuka milik blocker dan melepaskan kuncinya, sehingga sesi yang menunggu dapat melanjutkan proses.

4. Jika masalah berlanjut atau Anda tidak dapat mengidentifikasi akar penyebabnya, lihat Lock blocking untuk menggunakan halaman statistik lock blocking guna menemukan blocker jangka panjang dan melihat detail sesi.

Cegah pengulangan

Menghentikan sesi yang memblokir merupakan tindakan darurat. Untuk mencegah timeout tunggu kunci terjadi kembali, atasi penyebab mendasarnya:

• Lakukan commit atau rollback transaksi segera: Pertahankan transaksi tetap singkat. Hindari membiarkan transaksi terbuka saat menunggu logika aplikasi atau input pengguna.

• Pecah operasi batch besar menjadi chunk yang lebih kecil: Alih-alih menjalankan satu perintah UPDATE atau DELETE besar, proses baris dalam batch kecil dan lakukan commit setelah setiap batch untuk melepaskan kunci secara bertahap.

• Tambahkan indeks yang sesuai: Pemindaian tabel pada kolom tanpa indeks akan mengakuisisi lebih banyak kunci baris daripada yang diperlukan. Tambahkan indeks pada kolom yang digunakan dalam klausa WHERE pernyataan DML untuk mengurangi jumlah baris yang dikunci.

• Deteksi koneksi idle di lapisan aplikasi: Konfigurasikan timeout kolam koneksi dan aktifkan TCP keepalive agar database dapat mendeteksi klien yang terputus dan segera melepaskan kuncinya.

Berlaku untuk

ApsaraDB RDS for MySQL