前言：朋友咨询我说执行简单的update语句失效，症状如下：
MySQL> update order_infoset province_id=15,city_id= 1667where 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
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
3，看到有这条9930577的sql，kill掉，执行kill 9930577;
mysql> kill 9930577;
Query OK, 0 rows affected (0.00 sec)
mysql>
然后再去查询INNODB_TRX表，就没有阻塞的事务sleep线程存在了，如下所示：
mysql> SELECT * FROM INNODB_TRX\G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql>
再去执行update语句，就能正常执行了，如下所示：
mysql> update order_infoset province_id=15,city_id= 1667where order_from=10 and order_out_sn='1407261241xxxx';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
【mysql--ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction】 mysql>
4，总结分析
表数据量也不大，按照普通的情况来说，简单的update应该不会造成阻塞的，mysql都是autocommit，不会出现update卡住的情况，去查看下autocommit的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|0 |
+--------------+
1 row in set (0.00 sec)
mysql>
看到亮闪闪的0，这个设置导致原来的update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded;
try restarting transaction的错误。
所以赶紧commit刚才执行的update语句，之后 set global autocommit=1;
推荐阅读
- mysql|InnoDB数据页结构
- javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统
- mysql|一文深入理解mysql
- Java毕业设计项目实战篇|Java项目:在线嘿嘿网盘系统设计和实现(java+Springboot+ssm+mysql+maven)
- SQL|SQL基本功（五）--函数、谓词、CASE表达式
- vue|电商后台管理系统(vue+python|node.js)
- Java及基础算法及数据结构|旧笔记整理（MySQL）
- mysql|双非本211硕，无实习无项目，自学大数据开发，秋招上岸
- 数据库|Mysql--InnoDB存储引擎详解
- MySQL学习笔记-9-order by