mysql error1205 博客_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 * fromt_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.00sec)3

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

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

1 mysql> select * frominnodb_trx\G;2 *************************** 1. row ***************************

3 trx_id: A0234 trx_state: RUNNING5 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_trx\G;

1 mysql> select * from INNODB_LOCK_WAITS\G;

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

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

1 mysql> show fullprocesslist;2 mysql> select * fromINNODB_LOCK_WAITS\G;3 mysql> select * from innodb_trx\G;

此时把 A窗口rollback回来

mysql> rollback;

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

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

三、解决方案

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

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

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

mysql> kill 5;

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