MySQL的ERROR 1205错误分析

一、错误发生及原因猜测

1、错误发生

在删除 t_user 表的一条数据时，Navicat 发生长时间的无响应，然后弹出一个对话框，提示：ERROR 1205: Lock wait timeout exceeded; try restarting transaction

关闭对话框之后，数据并没有被删除。

2、原因猜测

根据错误信息可以知道，是因为锁等待超时导致的错误。那么具体到底是什么原因。

我们知道，InnoDB 引擎是支持事务的。

在使用 begin 或 start transaction 开启事务，进行更新、删除时，会对被操作的行加行级锁。

如果这个事务没有提交，而其他的事务对同一行也进行了更新、删除操作，那么这个事务是不能成功的，至于会不会出现上面的错误，还不确定。

二、错误重现

1、演示环境

操作系统	Windows 7 x64
MySQL版本	MySQL Community Server (GPL) 5.5.56
执行未提交事务的用户	system
查看会话、执行删除操作的用户	root
数据库	test
表	t_user

 1 mysql> select * from t_user;
 2 +----+----------+
 3 | id | username |
 4 +----+----------+
 5 |  1 | a1       |
 6 |  2 | a2       |
 7 |  3 | a3       |
 8 |  4 | a4       |
 9 |  5 | a5       |
10 |  6 | a6       |
11 +----+----------+

2、错误重现

使用 A窗口更新 id = 2 的数据

1 mysql> begin;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 mysql> update t_user set username = 'b1' where id = 2;
5 Query OK, 1 row affected (0.00 sec)
6 Rows matched: 1  Changed: 1  Warnings: 0

在没有提交的情况下，使用 C窗口查看当前事务，我们可以看到事务的状态(trx_state)、行锁数(trx_rows_locked)、修改数据行数(trx_rows_modified) 等信息

 1 mysql> select * from innodb_trxG;
 2 *************************** 1. row ***************************
 3                     trx_id: A023
 4                  trx_state: RUNNING
 5                trx_started: 2018-03-11 15:30:19
 6      trx_requested_lock_id: NULL
 7           trx_wait_started: NULL
 8                 trx_weight: 3
 9        trx_mysql_thread_id: 5
10                  trx_query: NULL
11        trx_operation_state: NULL
12          trx_tables_in_use: 0
13          trx_tables_locked: 0
14           trx_lock_structs: 2
15      trx_lock_memory_bytes: 376
16            trx_rows_locked: 1
17          trx_rows_modified: 1
18    trx_concurrency_tickets: 0
19        trx_isolation_level: REPEATABLE READ
20          trx_unique_checks: 1
21     trx_foreign_key_checks: 1
22 trx_last_foreign_key_error: NULL
23  trx_adaptive_hash_latched: 0
24  trx_adaptive_hash_timeout: 10000

此时再使用 B窗口删除t_user表的数据，在删除 id = 2 的数据时，发现事务被阻塞。我们在 C窗口查看 processlist、事务以及锁等待 情况

1 mysql> show full processlist;

1 mysql> select * from innodb_trxG;

1 mysql> select * from INNODB_LOCK_WAITSG;

当事务超时后出现1205的错误

此时，我们再查看一下 processlist、事务以及锁等待 情况，发现刚才处于 等待状态 的事务、process和锁等待已经没有了

1 mysql> show full processlist;
2 mysql> select * from INNODB_LOCK_WAITSG;
3 mysql> select * from innodb_trxG;

此时把 A窗口rollback 回来

mysql> rollback;

再查看一下processlist、事务以及锁等待情况

由此可以确定：1205 的错误是因为未提交事务对数据加了行级锁，当前事务获取同一数据行级锁超时导致的

三、解决方案

我们可以使用 show full processlist 查看未提交事务的连接的 id

可以看到这个连接的id是5

如果可以确定不是很重要的事务，我们可以使用kill命令断开这个连接。

mysql> kill 5;

如果不确定的话，还是需要沟通一下如何安全处理。