当有一个事务长时间不提交的时候。。。会引起某个表一直被锁。。导致别的事务使用这个表时 引起锁等待问题Lock wait timeout exceeded; try restarting transaction
举例如下1.开启事务。锁表lp_user_coupons_bak start TRANSACTIONupdate lp_user_coupons_bak set title=concat(title,'1');
2.此时别的事务对这个表进行操作 就会产生锁等待问题insert into `lp_user_coupons_bak` (`app_id`, `user_id`, `coupon_id`, `is_usable`, `money`, `usable_threshold`, `is_mutex`, `img`, `type`, `title`, `usable_start_at`, `usable_end_at`) values (1, 114, 60, 0, 30.00, 0.00, 1, '', 1, '30优惠券', '2018-12-07 11:30:55', '2019-12-28 11:30:55')
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction
这个时候当你想要找产生问题的事务时。。查看进程等方式都是看不到的。。。但是可以在 information_schema.innodb_trx 找到对应的进程id然后通过kill id的方式 释放这个事务
information_schema.innodb_trx中trx_mysql_thread_id这个字段对应session 的id
如下sql 已经加入nagios监控select a.id,a.user,a.host,b.trx_started,if(TIMESTAMPDIFF(second,b.trx_started,now())>20,current_date,"no_long") as tran_time from information_schema.processlist a right outer join information_schema.innodb_trx b on a.id = b.trx_mysql_thread_id;
===========================
注意查看performance_schema这个库的信息 需要开启参数
show variables like '%performance_schema%' 值为 ON 值为 OFF表示关闭 是看不到对应的表的信息（ performance_schema.events_statements_current）查看事务未提交的sqlSELECT a.sql_text, c.id, d.trx_started FROM performance_schema.events_statements_current a join performance_schema.threads b ON a.thread_id = b.thread_id join information_schema.processlist c ON b.processlist_id = c.id join information_schema.innodb_trx d ON c.id = d.trx_mysql_thread_id where c.id=11 ORDER BY d.trx_started
