1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

aurora mysqlでdeadlockエラーが出るがプロセスkillできないとき

Last updated at Posted at 2021-06-24

結論

インスタンスを再起動すればよい

環境

エンジンバージョン
5.7.mysql_aurora.2.07.4(mysql 5.7.12)

現象

処理中にDBをダウンさせたので後続の処理でdeadlockエラーが出た

ERROR 1213 (40001) at line 24: Deadlock found when trying to get lock; try restarting transaction

ぐぐると大抵プロセスを探してkillしろと書かれている
こんなかんじ↓

しかし SHOW ENGINE INNODB STATUS; で該当のthread idを見つけてkillしてもエラーになった

kill xxx;
[HY000][1094] Unknown thread id: xxx

これでも同じ

CALL mysql.rds_kill(xxx);
[HY000][1094] Unknown thread id: xxx

そもそも show full processlist; を見ても該当のidがないのである :thinking:

原因と対処

There is no way to clear the LATEST DETECTED DEADLOCK section in SHOW ENGINE INNODB STATUS unfortunately. The query has already been killed by the deadlock detector, so there's nothing you can KILL. The only way to reset that information is to restart the server.

No properly written software should continuously alert due to the presence of something in that output.

クエリは既にデッドロック検出器によってキルされているので、キルできるものはなく、
リセットするには、サーバを再起動するしかない、と書かれている
Thank you jeremycole!

まさにこの状態だったので再起動したところ、
もう SHOW ENGINE INNODB STATUS;LATEST DETECTED DEADLOCK の項は出てこなくなり、後続の処理が動くようになった

FYI

同じ現象のひと

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?