ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

前言：朋友咨询我说执行简单的update语句失效，症状如下：
MySQL> update order_info  set province_id=15  ,city_id= 1667  where order_from=10 and order_out_sn='1407261241xxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> 
 

QQ远程过去，开始check
1，查看数据库的隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> 

 
2，去查看先当前库的线程情况：

mysql> show full processlist;

+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

| Id       | User            | Host              | db              | Command     | Time    | State                   | Info                  |

+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

|        1 | event_scheduler | localhost         | NULL            | Daemon      | 9635385 | Waiting on empty queue  | NULL                  |

|  9930577 | business_web    | 192.168.1.21:45503 | business_db     | Sleep       |     153 |                         | NULL                  |

|  9945825 | business_web    | 192.168.1.25:49518 | business_db     | Sleep       |      43 |                         | NULL                  |

|  9946322 | business_web    | 192.168.1.23:44721 | business_db     | Sleep       |     153 |                         | NULL                  |

|  9960167 | business_web    | 192.168.3.28:2409  | business_db     | Sleep       |      93 |                         | NULL                  |

|  9964484 | business_web    | 192.168.1.21:24280 | business_db     | Sleep       |       7 |                         | NULL                  |

|  9972499 | business_web    | 192.168.3.28:35752 | business_db     | Sleep       |      13 |                         | NULL                  |

| 10000117 | business_web    | 192.168.3.28:9149  | business_db     | Sleep       |       6 |                         | NULL                  |

| 10002523 | business_web    | 192.168.3.29:42872 | business_db     | Sleep       |       6 |                         | NULL                  |

| 10007545 | business_web    | 192.168.1.21:51379 | business_db     | Sleep       |     155 |                         | NULL                  |
......

+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

没有看到正在执行的慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX\G;

*************************** 1. row ***************************

                    trx_id: 20866

                 trx_state: LOCK WAIT

               trx_started: 2014-07-31 10:42:35

     trx_requested_lock_id: 20866:617:3:3

          trx_wait_started: 2014-07-30 10:42:35

                trx_weight: 2

       trx_mysql_thread_id: 9930577

                 trx_query: delete from dltask where id=1