innodb_lock_wait_timeout参数

1、参数查看

• 方法一：mysql> show  variables like 'innodb_lock_wait_timeout';    
• 方法二：直接查看my.cnf文件innodb_lock_wait_timeout参数值

2、参数配置

• 方法一：mysql> set global innodb_lock_wait_timeout=1; 重启后会丢失使用my.cnf参数或默认值
• 方法二：直接修改my.cnf文件innodb_lock_wait_timeout参数值，但需要重启实例生效

3、参数值意义

　　innodb_lock_wait_timeout     integer　　 GLOBAL | SESSION

　　

InnoDB事务在放弃前等待行锁的时间（秒）。innodb_lock_wait_timeout默认值为50秒。当有试图访问被另一行锁定的行的事务InnoDB事务在发出以下错误：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 

当发生锁等待超时时，将回滚当前语句 (而不是整个事务)。要回滚整个事务，请使用“innodb_rollback_on_timeout” 开启值为：ON

对于高度交互式的应用程序或 OLTP 系统，您可能会降低该值，以便快速显示用户反馈，或者将更新放入队列中以便稍后处理。对于长时间运行的后端操作，例如等待其他大型插入或更新操作完成的数据仓库中的转换步骤，您可能会增加此值。InnoDB _ lock _ wait _ timeout仅适用于 InnoDB 行锁。MySQL 表锁不会发生在 InnoDB 内部，并且此超时不适用于等待表锁。锁等待超时值不适用于死锁，因为 InnoDB 会立即检测到它们，并回滚其中一个死锁事务。参见第 14.5.5.2 节，“死锁检测和回滚”。innodb _ lock _ wait _ time 可以在运行时使用 set GLOBAL 或 SET SESSION 语句设置。更改全局设置需要超级权限，并影响随后连接的所有客户端的操作。任何客户端都可以更改 innodb _ lock _ wait _ 超时的会话设置，这只影响该客户端。

4、外料

　　报错：  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 的可能原因及解决方法

　　可能场景：

　　　　在同一事务内先后对同一条数据进行插入和更新操作；

 　　　多台服务器(web)或客户端操作同一数据库；

  　　瞬时出现高并发现象；

　　原因：

　　　　在高并发的情况下，事务造成数据库死锁，后续操作超时抛出异常。

 　　　MySQL数据库采用InnoDB模式，默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s，一旦数据库锁超过这个时间就会报错。

　　解决方法：

　　　　a) 收集信息

　　　　　　查看有哪些线程正在执行：show processlist;

　　　　　　查看当前运行的所有事务：SELECT * FROM information_schema.INNODB_TRX;

　　　　　　　　其中trx_mysql_thread_id  与 show processlist;的 id 相对应

　　　　　　查看当前锁定的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

　　　　　　查看当前等锁的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

　　　　b) 根据收集的信息情况判断需要kill 掉的线程

　　　　c) 另外关注事务是否自动提交：

　　　　　　mysql> show variables like 'autocommit';

　　　　　　mysql> set global  autocommit='ON'    或 mysql> set global autocommit=1;    如果不是可以考虑情况配置