การใช้กลไกการล็อคเอ็นจิ้น MySQL InnoDB อย่างชาญฉลาดเพื่อแก้ไขปัญหาการหยุดชะงัก

*** (1) กำลังรอการล็อคนี้ที่จะได้รับ:

RECORD LOCKS space id 0 no page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locks rec but not gap Waiting

Record lock , ฮีปหมายเลข 92 บันทึกทางกายภาพ: n_fields 11; บิตข้อมูล 0

0: len 8; hex 7; hex 00000d4004 0110; @ ;; 3: เลขฐานสิบหก 8000000000050b2; เลขฐานสิบหก 80000000005426; ฉ ;; 7: ฐานสิบหก 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: เลน 8; ฐานสิบหก 800000000004e24; เช่น N$;;

** * (2) ธุรกรรม:

ธุรกรรม 0 677833454, ใช้งาน 0 วินาที, หมายเลขกระบวนการ 11397, รหัสเธรด OS 344086 อัปเดตหรือลบ, เธรดประกาศภายใน

ตาราง InnoDB 499 mysql ที่ใช้งาน 1, ล็อค 1

3 โครงสร้างล็อค, ขนาดฮีป 320, เลิกทำรายการบันทึก 1

รหัสเธรด MySQL 84, รหัสแบบสอบถาม 162348739 dcnet03 dcnet กำลังอัปเดตการ

อัปเดต ชุด TSK_TASK STATUS_ID=1067,UPDATE_TIME=ตอนนี้ () โดยที่ ID ใน (9921180)

*** (2) ถือ LOCK(S):

RECORD LOCKS space id 0 หมายเลขหน้า 849384 n บิต 208 ดัชนี `PRIMARY` ของตาราง `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X ล็อค rec แต่ไม่มีช่องว่าง

ล็อคบันทึก ฮีปหมายเลข 92 บันทึกทางกายภาพ: n_fields 11 ; รูปแบบกะทัดรัด

; 8; hex 800000000097629c; asc b ;; 1: len 6; hex 8000000000050b2; ค P ;; 4: เลน 8 ; ฐานสิบหก 80000000000502a; asc P*;; 0; asc uploadfire.com/hand.php;; 8: เลน 8; ฐานสิบหก 800000000000042b; 8; hex 800000000004e24; asc N$ ;;

*** (2) กำลังรอการล็อคนี้:

รหัสพื้นที่ 0 หน้า 843102 n บิต 600 ดัชนี `KEY_TSKTASK_MONTIME2` ของตาราง `dcnet_db/TSK_TASK` trx id 0 67783345 4 lock_mode X ล็อค rec แต่ไม่รอ

การล็อค, ฮีป 395 บันทึกทางกายภาพ: n_fields 3; บิตข้อมูล 0

0: ฐานสิบหก 80000000; 0097629c; asc ข ;;

*** เราย้อนกลับการทำธุรกรรม (1)

ปัญหาการหยุดชะงักนี้เกี่ยวข้องกับตาราง TSK_TASK ซึ่งใช้ในการบันทึกงานการตรวจสอบระบบ ต่อไปนี้เป็นฟิลด์และดัชนีที่เกี่ยวข้อง:

ID

: คีย์หลัก

;

สถานะงาน

ดัชนี: KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)

การวิเคราะห์แสดงให้เห็นว่าทั้งสองคำสั่งที่เกี่ยวข้องไม่ควรเกี่ยวข้องกับบันทึก TSK_TASK เดียวกัน แล้วเหตุใดจึงทำให้เกิดการหยุดชะงัก

หลังจากสืบค้นเอกสารประกอบเว็บไซต์อย่างเป็นทางการของ MySQL ฉันพบว่าสิ่งนี้เกี่ยวข้องกับกลไกการจัดทำดัชนีของ MySQL เอ็นจิ้น InnoDB ของ MySQL ใช้การล็อคระดับแถว ความเข้าใจเดิมของฉันคือบันทึกถูกล็อคโดยตรง แต่จริงๆ แล้วไม่เป็นเช่นนั้น

ประเด็นสำคัญมีดังนี้:

แทนที่จะล็อกบันทึก ดัชนีจะถูกล็อก

ระหว่างการดำเนินการ UPDATE และ DELETE MySQL ไม่เพียงแต่ล็อกบันทึกดัชนีทั้งหมดที่สแกนโดยเงื่อนไข WHERE แต่ยังล็อกค่าคีย์ที่อยู่ติดกันด้วย ซึ่งเรียกว่าคีย์ถัดไป การล็อค

ตัวอย่างเช่น คำสั่ง UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 จะล็อคบันทึกทั้งหมดด้วยคีย์หลักที่มากกว่าหรือเท่ากับ 1000 ก่อนที่คำสั่งจะเสร็จสมบูรณ์ คุณจะไม่สามารถดำเนินการกับบันทึกที่มีคีย์หลักเท่ากันได้ ถึง 10,000

เมื่อดัชนีที่ไม่ใช่คลัสเตอร์ ( เมื่อบันทึกดัชนีที่ไม่ใช่คลัสเตอร์ จะต้องล็อกบันทึกดัชนีคลัสเตอร์ที่เกี่ยวข้องด้วยเพื่อให้การดำเนินการที่เกี่ยวข้องเสร็จสมบูรณ์

การวิเคราะห์คำสั่ง SQL ทั้งสองที่มีปัญหาเกิดขึ้น การค้นหาปัญหาไม่ใช่เรื่องยาก:

เมื่อ "อัปเดต TSK_TASK ตั้งค่า STATUS_ID=1064,UPDATE_TIME=now () โดยที่ STATUS_ID=1061 และ MON_TIME