lock wait timeout exceeded;try restarting transactio解决方案

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction 解决方案

问题原因:

今天线上环境,突然出现一个问题,追踪原因是数据库中的一条语句报错,错误内容:lock wait timeout exceeded; try restarting transactio

执行update table set status = 1 where id = 10002;是可以的。

而执行update table set status = 1 where id = 10001;这条语句执行失败。

错误提示的意思,很明显,是因为这条语句被锁住了。所以需要释放这个锁。


解决方案:

新创建的数据库,有information、mysql、performance_schema、test四个数据库。

现在我们要查test库中使用情况,我们可以到information_schema中查询。

解释:information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名、数据库的表、表栏的数据类型与访问权限等。再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库的有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面。

我们可以用下面三张表来查原因:

1
2
3
innodb_trx        ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

如果数据库中有锁的话,那么再使用如下命令:

1
2
3
4
5
6
7
8
9
10
show full processlist;    ## 查看当前数据库的线程情况:
select * from information_schema.innodb_trx;
找到占用系统资源的锁,然后杀死即可。
kill 9372221;

另外几个常用命令:
select @@tx_isoloation;
REPEATABLE-READ // MySQL默认的事务隔离级别就是REPEATABLE-READ

注意:MySQL是自动提交事务的(即:autocommit=1),可以使用 show variables like 'autocommit' 或者 select @@autocommit 查看当前数据库是否为自动提交事务;若autocommit的值不是1还可以使用set global autocommit = 1 将自动提交设置为开启。

一般查看当前数据库的线程情况没有看到正在执行的很慢SQL记录线程,再去查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。

如下图:
Duplicate

图中红色语句为占用系统资源的语句,我们需要杀掉这个锁,执行kill 线程id号。上面这条记录的id为 9372221,所以我们执行:kill 9372221即可。执行之后:
Duplicate

然后我们再查看事务表:

1
2
查看事务表: select * from information_schema.innodb_trx;
发现为空,则表示已经正常了。

其他的记录不需要关注,因为其他的记录状态为”RUNNING” 即正在执行的事务,并没有锁。

我们可以进一步了解一下那三张表的表结构:
information_schema.innodb_locks表

1
desc information_schema.innodb_locks;
Field Type Null Key Default Remark
lock_id varchar(81) No 锁ID
lock_trx_id varchar(18) No 拥有锁的事务ID
lock_mode varchar(32) No 锁模式
lock_type varchar(32) No 锁类型
lock_table varchar(1024) No 被锁的表
lock_index varchar(1024) Yes 被锁的索引
lock_space bigint(21) unsigned Yes NULL 被锁的表空间号
lock_page bigint(21) unsigned Yes NULL 被锁的页号
lock_rec bigint(21) unsigned Yes NULL 被锁的记录号
lock_data varchar(8192) Yes NULL 被锁的数据

information_schema.innodb_lock_waits表

1
desc information_schema.innodb_lock_waits;
Field Type Null Key Default Remark
requesting_trx_id varchar(18) No 请求锁的事务ID
requesting_lock_id varchar(81) No 请求锁的锁ID
blocking_trx_id varchar(18) No 当前拥有锁的事务ID
blocking_lock_id varchar(81) No 当前拥有锁的锁ID

information_schema.innodb_trx表

1
desc information_schema.innodb_trx;
Field Type Null Key Default Extra Remark
trx_id varchar(18) No 事务ID
trx_state varchar(13) No 事务状态
trx_started datetime No 0000-00-00 00:00:00 事务开始时间
trx_requested_lock_id varchar(81) Yes NULL innodb_locks.lock_id
trx_wait_started datetime Yes NULL 事务开始等待的时间
trx_weight bingint(21) unsigned No 0 #
trx_mysql_thread_id bingint(21) unsigned No 0 事务线程ID
trx_query varchar(1024) Yes NULL 具体SQL语句
trx_operation_state varchar(64) Yes NULL 事务当前操作状态
trx_tables_in_use bingint(21) unsigned No 0 事务中有多少个表被使用
trx_tables_locked bingint(21) unsigned No 0 事务拥有多少个锁
trx_lock_structs bingint(21) unsigned No 0 #
trx_lock_memory_bytes bingint(21) unsigned No 0 事务锁住的内存大小(B)
trx_rows_locked bingint(21) unsigned No 0 事务锁住的行数
trx_rows_modified bingint(21) unsigned No 0 事务更改的行数
trx_concurrency_tickets bingint(21) unsigned No 0 事务并发票数
trx_isolation_level varchar(16) No 事务隔离级别
trx_unique_checks int(1) No 0 是否唯一性检查
trx_foreign_key_checks int(1) No 0 是否外键检查
trx_last_foreign_key_error varchar(256) Yes NULL 最后的外键错误
trx_adaptive_hash_latched int(1) No 0 #
trx_adaptive_hash_timeout bingint(21) unsigned No 0 #
文章目录
  1. 1. SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction 解决方案
本站总访问量 | 本页面被访问 | 您是第位小伙伴

© XueSi博客 版权所有 备案号 : 赣ICP备19008485号-1