环境
mysql5.5 
现象
a.数据更新或新增后数据经常自动回滚。
b.表操作总报 lock wait timeout exceeded 并长时间无反应
解决方法
a.应急方法：show processlist; kill掉出现问题的进程
b.根治方法：select * from innodb_trx 查看有是哪些事务占据了表资源。
c.我的方法：设置mysql锁等待超时 innodb_lock_wait_timeout=50 ，autocommit=on
该类问题导致原因
据我分析,mysql的 innodb存储引擎是支持事务的，事务开启后没有被主动commit。导致该资源被长期占用，其他事务在抢占该资源时，因上一个事务的锁而导致抢占失败！因此出现 lock wait timeout exceeded 
接下来是转载的内容
转载:http://blog.sina.com.cn/s/blog_6bb63c9e0100s7cb.html
mysql 5.5 -- innodb_lock_wait 锁 等待 记得以前，当出现：error 1205 (hy000): lock wait timeout exceeded; try restarting transaction，
要解决是一件麻烦的事情 ；
特别是当一个sql执行完了，但未commit，后面的sql想要执行就是被锁，超时结束；
dba光从数据库无法着手找出源头是哪个sql锁住了；
有时候看看show engine innodb status , 并结合 show full processlist; 能暂时解决问题；但一直不能精确定位；
在5.5中，information_schema 库中增加了三个关于锁的表（memory引擎）；
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系
看到这个就非常激动 ； 这可是解决了一个大麻烦，先来看一下表结构
root@127.0.0.1   : information_schema 13:28:38> desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | no | | | |#锁id
| lock_trx_id | varchar(18) | no | | | |#拥有锁的事务id
| lock_mode | varchar(32) | no | | | |#锁模式
| lock_type | varchar(32) | no | | | |#锁类型
| lock_table | varchar(1024) | no | | | |#被锁的表
| lock_index | varchar(1024) | yes | | null | |#被锁的索引
| lock_space | bigint(21) unsigned | yes | | null | |#被锁的表空间号
| lock_page | bigint(21) unsigned | yes | | null | |#被锁的页号
| lock_rec | bigint(21) unsigned | yes | | null | |#被锁的记录号
| lock_data | varchar(8192) | yes | | null | |#被锁的数据
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
root@127.0.0.1   : information_schema 13:28:56> desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | no | | | |#请求锁的事务id
| requested_lock_id | varchar(81) | no | | | |#请求锁的锁id
| blocking_trx_id | varchar(18) | no | | | |#当前拥有锁的事务id
| blocking_lock_id | varchar(81) | no | | | |#当前拥有锁的锁id
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
root@127.0.0.1   : information_schema 13:29:05> desc innodb_trx ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| field | type | null | key | default | extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | no | | | |#事务id
| trx_state | varchar(13) | no | | | |#事务状态：
| trx_started | datetime | no | | 0000-00-00 00:00:00 | |#事务开始时间；
| trx_requested_lock_id | varchar(81) | yes | | null | |#innodb_locks.lock_id
| trx_wait_started | datetime | yes | | null | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | no | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | no | | 0 | |#事务线程id
| trx_query | varchar(1024) | yes | | null | |#具体sql语句
| trx_operation_state | varchar(64) | yes | | null | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | no | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | no | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | no | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | no | | 0 | |#事务锁住的内存大小（b）
| trx_rows_locked | bigint(21) unsigned | no | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | no | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | no | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | no | | | |#事务隔离级别
| trx_unique_checks | int(1) | no | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | no | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | yes | | null | |#最后的外键错误
| trx_adaptive_hash_latched | int(1) | no | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | no | | 0 | |#
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows in set (0.01 sec)
下面我们来动手看看数据吧：
##建立测试数据：
use test;
create table tx1
(id int primary key ,
c1 varchar(20),
c2 varchar(30))
engine=innodb default charset = utf8 ;
insert into tx1 values
(1,'aaaa','aaaaa2'),
(2,'bbbb','bbbbb2'),
(3,'cccc','ccccc2');
commit;
###产生事务；
### session1
start transaction;
update tx1 set c1='heyf',c2='heyf' where id =3 ;
## 产生事务，在innodb_trx就有数据 ；
root@127.0.0.1   : information_schema 13:38:21> select * from innodb_trx g
*************************** 1. row ***************************
trx_id: 3669d82
trx_state: running
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: null
trx_wait_started: null
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: null
trx_operation_state: null
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: repeatable read
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
1 row in set (0.00 sec)
### 由于没有产生锁等待，下面两个表没有数据 ；
root@127.0.0.1   : information_schema 13:38:31> select * from innodb_lock_waits g
empty set (0.00 sec)
root@127.0.0.1   : information_schema 13:38:57> select * from innodb_locks g
empty set (0.00 sec)
#### 产生锁等待
#### session 2
start transaction;
update tx1 set c1='heyfffff',c2='heyffffff' where id =3 ;
root@127.0.0.1   : information_schema 13:39:01> select * from innodb_trx g
*************************** 1. row ***************************
trx_id: 3669d83 ##第2个事务
trx_state: lock wait ## 处于等待状态
trx_started: 2010-12-24 13:40:07
trx_requested_lock_id: 3669d83:49:3:4 ##请求的锁id
trx_wait_started: 2010-12-24 13:40:07
trx_weight: 2
trx_mysql_thread_id: 2346 ##线程 id
trx_query: update tx1 set c1='heyfffff',c2='heyffffff' where id =3
trx_operation_state: starting index read
trx_tables_in_use: 1 ##需要用到1个表
trx_tables_locked: 1 ##有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: repeatable read
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
*************************** 2. row ***************************
trx_id: 3669d82 ##第1个事务
trx_state: running
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: null
trx_wait_started: null
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: null
trx_operation_state: null
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: repeatable read
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
2 rows in set (0.00 sec)
root@127.0.0.1   : information_schema 13:40:12> select * from innodb_locks g
*************************** 1. row ***************************
lock_id: 3669d83:49:3:4 ## 第2个事务需要的锁
lock_trx_id: 3669d83
lock_mode: x
lock_type: record
lock_table: `test`.`tx1`
lock_index: `primary`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
*************************** 2. row ***************************
lock_id: 3669d82:49:3:4 ## 第1个事务需要的锁
lock_trx_id: 3669d82
lock_mode: x
lock_type: record
lock_table: `test`.`tx1`
lock_index: `primary`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
2 rows in set (0.00 sec)
root@127.0.0.1   : information_schema 13:40:15> select * from innodb_lock_waits g
*************************** 1. row ***************************
requesting_trx_id: 3669d83 ## 请求锁的事务
requested_lock_id: 3669d83:49:3:4 ## 请求锁的锁id
blocking_trx_id: 3669d82 ## 拥有锁的事务
blocking_lock_id: 3669d82:49:3:4 ## 拥有锁的锁id
1 row in set (0.00 sec)