home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

Debug Lock Wait Timeout Exceeded ใน MySQL ด้วย SHOW ENGINE INNODB STATUS

Lock Wait Timeout Exceeded เกิดเมื่อ Transaction รอ Lock นานเกิน innodb_lock_wait_timeout (Default: 50 วินาที)

การ Debug: SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

Output Sections ที่สำคัญ

1. TRANSACTIONS - ดู Transaction ที่ Block/Others

------------
TRANSACTIONS
------------
Trx id counter 21000
---TRANSACTION 283438498772800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 12345, ACTIVE 30 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 1234, query id 100 localhost root
Trx read view will not see trx with id >= 12346
TABLE LOCK table `business`.`accounts` trx id 12345 lock_mode X
RECORD LOCKS space id 123 page no 5 n bits 72 index PRIMARY of table `business`.`accounts`
trx id 12345 lock_mode X locks rec but not gap
Record lock, heap no 2
------------------

2. SEMAPHORES - Mutex/RW-lock Contention

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3, signal count 3
RW-shared spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl

3. FILE I/O - Disk Bottleneck

--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
Pending normal aio reads: [0, 0, 0, 0], aio writes: [0, 0, 0, 0]
0.00 reads/s, 0.00 writes/s, 0.00 fsyncs/s

4. BUFFER POOL - Memory Pressure

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 8585216
Buffer pool size 512
Free buffers 251
Database pages 256
Buffer pool hit rate 1000 / 1000

วิธีแก้ Lock Wait Timeout

1. หา Blocking Query

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
WHERE LOCK_TRX_ID IN (SELECT TRX_ID FROM INFORMATION_SCHEMA.INNODB_TRX WHERE TRX_STATE = 'LOCK WAIT');

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

2. เพิ่ม Timeout (Temporary)

SET GLOBAL innodb_lock_wait_timeout = 120;  -- 2 นาที
SET SESSION innodb_lock_wait_timeout = 120;

3. Optimize Query/Index (Root Cause)

  • เพิ่ม Index ให้ Query เร็วลง → Lock น้อยลง
  • ลด Transaction Scope (Commit เร็วขึ้น)
  • หลีกเลี่ยง Long-running Transaction
  • ใช้ Row-level Locking อย่างถูกต้อง (Index บน FK, WHERE)

4. Kill Blocking Transaction

KILL blocking_thread_id;

Prevention

  • Monitor Innodb_row_lock_waits, Innodb_row_lock_time_avg
  • Set innodb_lock_wait_timeout ให้เหมาะสม (50-120s)
  • ใช้ SELECT ... FOR UPDATE SKIP LOCKED (MySQL 8.0+) สำหรับ Queue Processing
  • Application: Retry Logic with Exponential Backoff