SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded 解决思路

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