mysql: Lock wait timeout exceeded

报错原因-猜想1：
当锁等待超时后innodb引擎报此错误，等待时间过长的语句被回滚（不是整个事务）。如果想让SQL语句等待其他事务更长时间之后完成，你可以增加参数innodb_lock_wait_timeout配置的值。如果有太多长时间运行的有锁的事务，你可以减小这个innodb_lock_wait_timeout的值，在特别繁忙的系统，你可以减小并发。
InnoDB事务等待一个行级锁的时间最长时间（单位是秒），超过这个时间就会放弃。默认值是50秒。一个事务A试图访问一行数据，但是这行数据正在被另一个innodb事务B锁定，此时事务A就会等待事务B释放锁，等待超过innodb_lock_wait_timeout设置的值就会报错ERROR 1205 (HY000)：

innodb_lock_wait_timeout

是一个动态参数，默认值为50.

set GLOBAL innodb_lock_wait_timeout=1500；

注意，global变量的改变只会影响修改之后打开的session；注意不能改变当前session；

报错原因-猜想2：
出现了死锁。
1)查询是否锁表：

SHOW open tables WHERE in_use>0;
+----------+-----------+--------+-------------+
| Database | Table     | In_use | Name_locked |
+----------+-----------+--------+-------------+
| item     | dtl60temp |      1 |           0 |
| item     | dtl60     |      1 |           0 |
+----------+-----------+--------+-------------+

2）查询相关的锁：
在8.0中，有关事务锁的信息如下：
innodb_trx ## 当前运行的所有事务

data_locks ## 当前出现的锁，查看正在锁的事务

data_lock_waits ## 锁等待的对应关系 ，查看等待锁的事务

假设现在表格t上同时在进行三个事务，查看哪个锁阻塞：

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

或者直接使用sys中，innodb_lock_waits视图：

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

3）查询产生锁的具体sql语句。步骤如下

1. 确定pid。查看sys.innodb_lock_waits表，如果进程被锁，则获取blocking_pid值。
2. 查看被锁进程的thread id。

 SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;

3. 查询performance_schema.events_statements_current表，确定该线程中的最后一条sql语句。

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28\G

4. 也可以查询线程的后10条语句。这时，使用的是performance_schema.events_statements_history表。

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;

5. 确认是否可以kill之后，将不需要的线程kil掉。
   也可以选择之后，批量执行。

select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id into outfile '/tmp/kill.txt';

有关innodb_trx, data_locks, data_lock_waits, 详见https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-examples.html

补充 Mysql 8.0 表锁lock & unlock

锁只能在同一个session里获取或释放。使用表锁可以在更新时加速。在当前会话下，拥有LOCK TABLES和SELECT权限的用户可以使用表锁。
在视图上使用表锁时，会在检查用户对每一张视图下的原始表格的权限后，自动锁上这些表。
使用表锁时，与表相关的外键也会被隐式锁上，加上read-only锁。
对于级联的更新，加上shared-nothing write-lock。
LOCK TABLES在获取新锁前，会隐式地释放这个会话中之前的所有表锁。
表锁中，写锁可以完成表格级别的操作，例如DROP TABLE, TRUNCATE TABLE，但是这些操作在读锁中是禁止的。

UNLOCK TABLES释放会话中所有表锁。也可以用这个命令释放global read lock。

LOCK TABLES t1 READ, t2 WRITE;
UNLOCK TABLES;

详见
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

本文参考
http://blog.itpub.net/29654823/viewspace-2150471/
更新了mysql8.0的部分