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

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

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

สิ่งที่ต้องเตรียม (Lock wait timeout)

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

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

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

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

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

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;

// ตัวอย่างแนวคิด
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))
  }
}

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

[mysqld]
innodb_lock_wait_timeout = 15

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

CREATE INDEX idx_orders_status ON orders (status);