แก้ปัญหา Deadlock และ Lock wait timeout บน MySQL

ข้อความ Lock wait timeout exceeded และ Deadlock found when trying to get lock เป็นสองอาการที่ทำให้แอปพลิเคชันล้มเหลวเป็นช่วง ๆ โดยหาสาเหตุยาก เพราะเกิดเฉพาะตอนที่มีผู้ใช้หลายคนทำงานพร้อมกัน และไม่เกิดเลยตอนทดสอบ

คู่มือนี้อธิบายว่าทั้งสองอย่างต่างกันอย่างไร วิธีอ่านรายงานที่ InnoDB สร้างให้ และแนวทางแก้ที่ต้นเหตุ ซึ่งมักไม่ใช่การเพิ่มเวลารอ

สิ่งที่ต้องเตรียม

• MySQL 8.0 หรือ MariaDB พร้อมสิทธิ์ผู้ดูแล
• สิทธิ์ดู Log ของแอปพลิเคชันเพื่อหาว่าคำสั่งใดล้มเหลว

ขั้นตอนที่ 1: เข้าใจความต่างของสองอาการ

• Lock wait timeout ทรานแซกชันหนึ่งรอล็อกที่อีกทรานแซกชันถืออยู่นานเกินกำหนด ระบบยอมแพ้และคืนค่าผิดพลาด มักแปลว่ามีทรานแซกชันที่เปิดค้างไว้นานเกินไป
• Deadlock สองทรานแซกชันต่างรอล็อกของกันและกันเป็นวงกลม ไม่มีทางออกเอง InnoDB ตรวจพบและยกเลิกตัวหนึ่งทันที มักแปลว่าลำดับการเข้าถึงข้อมูลไม่สอดคล้องกัน

ข้อสำคัญคือ Deadlock ไม่ใช่เรื่องผิดปกติเสมอไป ระบบที่มีการทำงานพร้อมกันสูงจะเจอบ้างเป็นธรรมดา สิ่งที่แอปต้องมีคือการลองใหม่อัตโนมัติ ไม่ใช่การกำจัด Deadlock ให้เป็นศูนย์

ขั้นตอนที่ 2: อ่านรายงาน Deadlock ล่าสุด

mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/TRANSACTIONS/p'

ในรายงานจะเห็นสองส่วนคือ *** (1) TRANSACTION และ *** (2) TRANSACTION สิ่งที่ต้องอ่าน

• บรรทัดหลัง *** (1) WAITING FOR THIS LOCK TO BE GRANTED บอกว่าทรานแซกชันแรกรอล็อกอะไร
• บรรทัดหลัง *** (2) HOLDS THE LOCK(S) บอกว่าทรานแซกชันที่สองถืออะไรอยู่
• บรรทัด *** WE ROLL BACK TRANSACTION (N) บอกว่าตัวไหนถูกยกเลิก
• คำสั่ง SQL ของแต่ละตัวซึ่งอยู่ในรายงานด้วย นี่คือสิ่งที่นำไปแก้ที่โค้ด

เปิดให้บันทึก Deadlock ทุกครั้งลง Error Log เพื่อให้ย้อนดูได้ ไม่ใช่เห็นแค่ครั้งล่าสุด

mysql -e "SET GLOBAL innodb_print_all_deadlocks = ON;"
sudo tail -100 /var/log/mysql/error.log | grep -A30 -i deadlock

ตั้งให้ถาวรในไฟล์คอนฟิก

[mysqld]
innodb_print_all_deadlocks = ON

ขั้นตอนที่ 3: หาว่าใครกำลังล็อกอยู่ตอนนี้

-- ทรานแซกชันที่กำลังทำงาน
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- ใครรอใคร บน MySQL 8.0
SELECT r.trx_id AS waiting_trx,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

คอลัมน์ age_sec คือสิ่งที่ต้องดูก่อน ทรานแซกชันที่เปิดค้างเกินหนึ่งนาทีคือความผิดปกติเกือบทุกครั้ง

เมื่อจำเป็นต้องหยุดทรานแซกชันที่ค้างเพื่อให้ระบบกลับมาทำงาน

KILL 12345;

แต่นี่คือการแก้อาการ ต้องหาต้นเหตุต่อว่าทำไมจึงค้าง

ขั้นตอนที่ 4: แก้ที่ต้นเหตุ — เรียงลำดับการเข้าถึงให้เหมือนกัน

Deadlock ส่วนใหญ่เกิดจากสองกระบวนการเข้าถึงแถวเดียวกันในลำดับที่ต่างกัน

-- กระบวนการ A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- กระบวนการ B ทำสลับลำดับ จึงเกิด Deadlock
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

ทางแก้คือบังคับให้ทุกจุดในโค้ดเข้าถึงเรียงตาม id จากน้อยไปมากเสมอ

START TRANSACTION;
SELECT id FROM accounts WHERE id IN (1,2) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ขั้นตอนที่ 5: ลดขนาดและระยะเวลาของทรานแซกชัน

กฎที่สำคัญที่สุดคืออย่าทำงานอื่นระหว่างที่ทรานแซกชันเปิดอยู่ โดยเฉพาะการเรียก API ภายนอก การส่งอีเมล หรือการอ่านไฟล์ เพราะล็อกจะถูกถือไว้ตลอดเวลานั้น

-- แย่ ถือล็อกไว้ระหว่างรอ API ภายนอก
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100;
-- เรียก API ชำระเงินตรงนี้ ใช้เวลา 3 วินาที
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

-- ดี แยกการทำงานภายนอกออกมานอกทรานแซกชัน
START TRANSACTION;
UPDATE orders SET status = 'processing' WHERE id = 100;
COMMIT;
-- เรียก API ตรงนี้
START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE id = 100;
COMMIT;

ขั้นตอนที่ 6: ให้แอปลองใหม่อัตโนมัติ

Deadlock ที่เกิดเป็นครั้งคราวเป็นเรื่องที่รับได้ ตราบใดที่แอปลองใหม่ให้ ตรวจรหัสข้อผิดพลาดแล้วลองซ้ำสองถึงสามครั้งพร้อมหน่วงเวลาเล็กน้อย

// ตัวอย่างแนวคิด
for (let i = 0; i < 3; i++) {
  try {
    await runTransaction()
    break
  } catch (e) {
    // 1213 คือ Deadlock, 1205 คือ Lock wait timeout
    if (![1213, 1205].includes(e.errno) || i === 2) throw e
    await sleep(50 * Math.pow(2, i))
  }
}

ขั้นตอนที่ 7: ตรวจค่าที่เกี่ยวข้อง

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SHOW VARIABLES LIKE 'transaction_isolation';

ค่าเริ่มต้นของ innodb_lock_wait_timeout คือ 50 วินาที ซึ่งนานเกินไปสำหรับเว็บ ลดลงเหลือ 10-15 วินาทีทำให้ผู้ใช้ได้รับข้อผิดพลาดเร็วขึ้นแทนที่จะค้างรอ และช่วยให้ปัญหาถูกค้นพบเร็ว

[mysqld]
innodb_lock_wait_timeout = 15

อย่าปิด innodb_deadlock_detect การปิดทำให้ระบบไม่ตรวจจับและปล่อยให้รอจนหมดเวลาแทน ซึ่งแย่กว่าในเกือบทุกกรณี

ขั้นตอนที่ 8: เพิ่ม Index เพื่อลดขอบเขตการล็อก

InnoDB ล็อกแถวผ่าน Index ถ้า Query ไม่มี Index ที่ใช้ได้ มันจะล็อกทุกแถวที่สแกนผ่าน ซึ่งอาจเป็นทั้งตาราง

-- ไม่มี Index บน status จึงล็อกทั้งตาราง
UPDATE orders SET note = 'x' WHERE status = 'pending';

CREATE INDEX idx_orders_status ON orders (status);

นี่คือเหตุผลที่ปัญหา Deadlock จำนวนมากหายไปเองหลังเพิ่ม Index ที่ถูกต้อง

ปัญหาที่พบบ่อย

เกิด Lock wait timeout ทั้งที่ไม่มีใครใช้ระบบ

มีทรานแซกชันค้างจากการเชื่อมต่อที่ไม่ถูกปิด ตรวจด้วยคำสั่งในขั้นตอนที่ 3 แล้วดูที่ age_sec มักเกิดจากแอปที่ไม่ได้ commit หรือ rollback เมื่อเกิดข้อผิดพลาด

รายงาน Deadlock ไม่แสดงคำสั่ง SQL

คำสั่งที่แสดงคือคำสั่งล่าสุดของทรานแซกชันนั้นเท่านั้น ไม่ใช่ทั้งหมด เปิด innodb_print_all_deadlocks และดู Log ของแอปประกอบเพื่อให้เห็นภาพเต็ม

Deadlock เกิดบ่อยขึ้นหลังเพิ่มจำนวนผู้ใช้

เป็นธรรมชาติของการทำงานพร้อมกันที่มากขึ้น ให้แน่ใจว่าแอปลองใหม่อัตโนมัติ ลดขนาดทรานแซกชัน และตรวจว่าทุก UPDATE และ DELETE มี Index รองรับ

KILL แล้วทรานแซกชันยังไม่หายไป

ระบบกำลังย้อนกลับข้อมูลที่ทำไปแล้ว ซึ่งอาจใช้เวลานานกว่าตอนทำ ดูความคืบหน้าได้จาก SHOW ENGINE INNODB STATUS ในส่วน TRANSACTIONS อย่ารีสตาร์ตฐานข้อมูลระหว่างนี้

ต้องการให้ทีมวิศวกรช่วยตรวจสอบปัญหาฐานข้อมูลที่เกิดเป็นช่วง ติดต่อ THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/