Kasusnya sebagai berikut:
Saat menggunakan Show innodb status untuk memeriksa status mesin, ditemukan masalah deadlock:
*** (1) TRANSAKSI:
TRANSAKSI 0 677833455, AKTIF 0 detik, no proses 11393, id thread OS 278546 mulai membaca indeks
tabel mysql digunakan 1, terkunci 1
LOCK WAIT 3 lock struct(s), heap size 320
MySQL thread id 83, query id 162348740 dcnet03 dcnet Mencari baris untuk update
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () di mana STATUS_ID=1061 dan SENIN_TIME
*** (1) MENUNGGU KUNCI INI DIBERIKAN:
RECORD LOCKS space id 0 halaman no 849384 n bits 208 indeks `PRIMARY` tabel `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec tetapi tidak gap waiting
Record lock , heap no 92 REKAM FISIK: n_fields 11; format ringkas; bit info 0
0: len 8; hex 800000000097629c; 110; ;; 3: len 8; hex 8000000000050b2; asc P;; 4: len 8; hex 8000000000502a; f ;; 7: lensa 23; heksa 75706c6f6164666972652e636f6d2f6
8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;;9
: len 4; hex 474bfa 2b; asc N$;;
* (2) TRANSAKSI:
TRANSAKSI 0 677833454, AKTIF 0 detik, no proses 11397, id thread OS 344086 diperbarui atau dihapus, thread dideklarasikan di dalam
tabel mysql InnoDB 499 digunakan 1, terkunci 1
3 struct kunci, ukuran heap 320, batalkan entri log 1
ID thread MySQL 84, id kueri 162348739 dcnet03 dcnet Memperbarui
pembaruan TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () di mana ID di (9921180)
*** (2) TAHAN KUNCI:
ID ruang RECORD LOCKS 0 halaman no 849384 n bit 208 indeks `PRIMARY` dari tabel `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X mengunci rekaman tetapi tidak celah
Kunci rekaman, no tumpukan 92 CATATAN FISIK: n_fields 11 ; format info ringkas 0
0: len 8; heksa 800000000097629c; asc b;; c P ;; 4: len 8 ; 0; asc uploadfire.com/hand.php;; 8: len 8;
6
;
54
lock_mode X mengunci rec tetapi tidak menunggu celah
Rekam kunci, tumpukan no 395 REKAM FISIK: n_fields 3; format kompak; bit info 0
0: hex 8000000000000425; 97629c; ;;
*** KAMI ROLL BACK TRANSACTION (1)
Masalah kebuntuan ini melibatkan tabel TSK_TASK,
yang digunakan untuk menyimpan tugas pemantauan sistem. Berikut ini adalah bidang dan indeks yang relevan:
ID
: kunci utama;
Status tugas;
indeks: KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME).
Analisis menunjukkan bahwa kedua pernyataan yang terlibat tidak boleh melibatkan catatan TSK_TASK yang sama, jadi mengapa hal ini menyebabkan kebuntuan?
Setelah menanyakan dokumentasi situs resmi MySQL, saya menemukan bahwa ini terkait dengan mekanisme pengindeksan MySQL. Mesin InnoDB MySQL menggunakan kunci tingkat baris. Pemahaman awal saya adalah bahwa catatan dikunci secara langsung, tetapi sebenarnya tidak demikian.
Poin utamanya adalah sebagai berikut:
alih-alih mengunci catatan, indeks dikunci
selama operasi UPDATE dan DELETE, MySQL tidak hanya mengunci semua catatan indeks yang dipindai oleh kondisi WHERE, tetapi juga mengunci nilai kunci yang berdekatan, yang disebut kunci berikutnya. mengunci;
misalnya, pernyataan UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 akan mengunci semua record dengan kunci utama lebih besar dari atau sama dengan 1000. Sebelum pernyataan selesai, Anda tidak dapat mengoperasikan catatan dengan kunci utama sama dengan 1000. hingga 10.000;
ketika indeks non-cluster ( Ketika catatan indeks non-cluster dikunci, catatan indeks cluster terkait juga perlu dikunci untuk menyelesaikan operasi terkait.
Menganalisis dua pernyataan SQL di mana masalah terjadi, tidak sulit untuk menemukan masalahnya:
ketika "perbarui TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () di mana STATUS_ID=1061 dan MON_TIME
Dengan asumsi bahwa "perbarui TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () di mana ID di (9921180)" dijalankan hampir bersamaan, pernyataan ini pertama-tama mengunci indeks cluster (kunci utama). juga diperlukan untuk mengunci bagian tertentu dari KEY_TSKTASK_MONTIME2.
Dengan cara ini, pernyataan pertama mengunci catatan KEY_TSKTASK_MONTIME2 dan menunggu indeks kunci utama, sedangkan pernyataan kedua mengunci catatan indeks kunci utama dan menunggu catatan KEY_TSKTASK_MONTIME2.
Penulis memecahkan masalah kebuntuan dengan memisahkan pernyataan pertama:
pertama temukan ID yang memenuhi syarat: pilih ID dari TSK_TASK di mana STATUS_ID=1061 dan MON_TIME < date_sub(now(), INTERVAL 30 menit); lalu perbarui status: perbarui TSK_TASK set STATUS_ID= 1064 dimana ID di (….)
Pada titik ini, masalah kebuntuan telah terpecahkan sepenuhnya.