digdeep

凡是过去,皆是序幕。Read the fucking manual and source code.

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

现象:

导入一个insert 语句sql文件到数据库中时,老是报错:“Error Code: 1205 - Lock wait timeout exceeded; try restarting transaction”。

我们使用 innodb_trx innodb_lock_waits innodb_locks 这三个表开始调查:

1、找到被锁卡死的SQL:

SELECT * FROM innodb_trx WHERE trx_state='LOCK WAIT'

通过改语句找到被锁卡住,也就是申请锁 处于锁等待状态的SQL信息:

 可以得到当前处于锁等待的线程id值为:11188391835

trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
trx_started:事务的开始时间
trx_requested_lock_id:等待事务的锁ID
trx_wait_started:事务等待的开始时间
trx_weight:事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚
trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应
trx_query:事务运行的SQL语句

2、找到持有锁的线程 和 申请锁被卡住的线程

SELECT * FROM INNODB_LOCK_WAITS where requesting_trx_id=11188391835

 申请锁的线程id为:11188391835;而当前持有锁的线程id为:11184617003

3、查看锁的信息

SELECT * FROM INNODB_LOCKS WHERE lock_trx_id IN (11188391835,11184617003)

 可以看到线程 11184617003 持有主键上面的 X 锁,而线程 11188391835申请 S 锁被卡住;、

4、查看 持有锁的线程的mysql线程id

SELECT TIMESTAMPDIFF(SECOND,trx_started,NOW()),innodb_trx.* FROM innodb_trx WHERE trx_id='11184617003'

 可以看到该线程的mysql的线程id为:11184617003;而且已经运行了 20186秒,也就是300多分钟,而且他的权重 15416 很大,所以不会被回滚。所以导致线程 11184617003 被回滚。

而且我们看到他的 trx_query 为空。

所以解决方法就是:kill 141278145

 搞定。重新导入就可以了。

5、原因分析

初步调查原因应该是多个线程并发导入,导致了锁冲突。因为每个线程都是导入十几万的数据,有多达十几个线程并发导入,比较容易造成锁冲突。

 

posted on 2021-09-12 16:31  digdeep  阅读(1232)  评论(0编辑  收藏  举报
不懂数据库和Web安全的架构师不是一个好的程序员。