SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
MySQL有时会报这种延时的异常。一、问题分析
经过对网上相关问题的搜索,总结了解决这种问题的分析思路:
首先,表象上,是SQL执行时间太长,导致超过数据库设置的默认延时时长。如果经过分析,确实由于有些事务的延时太大导致的。分析思路和步骤是:
步骤1:查看影响的table
步骤2:获取所有附加锁的种类和其他互斥信息
分析数据:
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
110514 19:44:14 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9014315, signal count 7805377
Mutex spin waits 0, rounds 11487096053, OS waits 7756855
RW-shared spins 722142, OS waits 211221; RW-excl spins 787046, OS waits 39353
------------------------
LATEST FOREIGN KEY ERROR
------------------------
110507 21:41:35 Transaction:
TRANSACTION 0 606162814, ACTIVE 0 sec, process no 29956, OS thread id 1223895360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3024, 8 row lock(s), undo log entries 1
MySQL thread id 3686635, query id 124164167 10.64.89.145 viget updating
DELETE FRO