MySQL锁；Lock wait timeout exceeded; try restarting transaction

问题

Lock wait timeout exceeded; try restarting transaction

分析

• 设置了autocommit=0，但没有提交或者回滚；autocommit是session级别的。如

  set autocommit=0;
  select * from A where id=1 for update;
  ...
  commit;/*rollback 这里可能被忽略、异常等*/

• 默认设置autocommit=1，但事务没有正常结束，如

  begin;/*start transaction*/
  select * from A where id=1 for update;
  ...
  commit;/*rollback 这里可能被忽略、异常等*/

解决方案

• 检查阻塞的进程并kill掉【推荐】

  select * from information_schema.processlist p join information_schema.innodb_trx it on it.trx_mysql_thread_id =p.id where trx_query is null;/*排查没有执行sql，但是占用事务的进程*/
  select concat('call mysql.rds_kill(',id,');') from information_schema.processlist p join information_schema.innodb_trx it on it.trx_mysql_thread_id =p.id where trx_query is null;/*kill 阻塞其他进程的进程*/

• 检查程序是否发生异常导致没有事务没有正常结束。事务正确的使用方式【推荐】

  begin;/*start transaction;*/
  select * from A where id=1 for update;
  ...
  commit;/*rollback*/

• 查看锁状态

  select * from information_schema.innodb_locks;
  
  select * from information_schema.innodb_lock_waits ;
  
  select * from information_schema.processlist pl join information_schema.innodb_trx it on pl.id=it.trx_mysql_thread_id join information_schema.innodb_locks il on it.trx_id=il.lock_trx_id;/*当前进行的事务 lock*/

• 查看innodb的状态

  show engine innodb status;

  参考
  http://my.oschina.net/sansom/blog/123364

---

Wait for your reward