执行sql报lock wait timeout exceeded; try restarting transaction
mysql查询时候报错:
Lock wait timeout exceeded; try restarting transaction
译文:锁等待超时;试着重新启动事务
被锁了,需要解锁。
1.innodb_trx 当前运行的所有事务
select * from information_schema.innodb_trx;
查询trx_state的状态
trx_state:
LOCAK WAIT 占用系统资源的语句(事务状态)
RUNNING 即正在执行的事务,并没有锁
#查询线程
SELECT * from information_schema.processlist;
#杀死线程
kill 738178711杀死进程
#查询线程
SHOW FULL processlist;
2.innodb_locks 当前出现的锁
select * from information_schema.innodb_locks;
3.innodb_lock_waits 锁等待的对应关系
select * from information_schema.innodb_lock_waits;
#可以查找执行线程用时比较久的线程,然后直接干掉
SELECT * from information_schema.processlist WHERE Time > 1000 ORDER BY TIME desc;
#查看最近死锁的日志
show engine innodb status;