MySQL(InnoDB)のロックとデッドロックについて紹介します。
ロック待ちを確認したいときは、MySQL 8.0以降なら performance_schema.data_lock_waits（8.0より前は information_schema.innodb_lock_waits）を見ます。直近のデッドロックは SHOW ENGINE INNODB STATUS の「LATEST DETECTED DEADLOCK」に記録されています。
ロックとは
ロックとは、データを更新する前に行やテーブルを他のセッションから更新されないようにすることです。
行をロックする行ロックやテーブルをロックするテーブルロックがあります。
流れは、たとえばレコード1に対してデータを更新したいとします。
そのときに、まずロックを取得します。
ロックされると、他のセッションからはレコード1を更新できない状態になります。
ロックを取得したら、updateで更新して完了すると、ロックを解除します。
ロックが解除されれば、他のセッションからも更新可能な状態に戻ります。
トランザクションを使用したときのロックは、commitやrollbackされたときに解除されます。
つまり、トランザクションを開いたままcommitし忘れたセッションがあると、その行は他のセッションからずっと更新できません。ロック待ちの原因として一番多いパターンです。
デッドロックとは
デッドロックは、複数セッションがそれぞれロック解除待ちをしてしまって処理が完了しない状態です。
- たとえば、セッションAとセッションBがあり、セッションAからレコード1をupdateする際、
レコード1をロックします。 - 同じ時刻にセッションBからレコード2に対してupdateする際、
レコード2をロックします。 - そして、セッションAでレコード2のupdateをしようとしたとき、
セッションBですでにレコード2はロックされている状態のため、
セッションAからロックを取得できません。ロック待ちの状態になります。 - そして、セッションBからレコード1のupdateをしようとしたとき、
レコード1はセッションAがロックしているのでロック待ちの状態になります。 - セッションAもBも互いにロック待ちの状態になってしまいます。
これをデッドロックといいます。
InnoDBはデッドロックを自動で検出し（innodb_deadlock_detect が既定でON）、片方のトランザクションをロールバックしてもう片方を先に進めます。ロールバックされた側にはエラー1213が返るので、アプリケーション側でそのトランザクションをやり直します。
Lock wait timeout exceeded.
エラー1205です。ロックの解除を待っている時間が innodb_lock_wait_timeout（既定50秒）を超えたときのメッセージです。
デッドロックとは別物で、多くは「別のセッションがトランザクションを開いたままcommitしていない」ことが原因です。デッドロックなら待たずにすぐ次の1213が返ります。
-- 待ち時間の確認（秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';Deadlock found when trying to get lock
エラー1213です。デッドロックが検出され、このトランザクションがロールバックされたというメッセージです。
lock解除待ちの確認方法
-- lock解除待ちの確認 MySQL8.0より前
SELECT * FROM information_schema.innodb_lock_waits;
-- lock解除待ちの確認 MySQL8.0以降
SELECT * FROM performance_schema.data_lock_waits;MySQL 8.0では information_schema.innodb_locks と innodb_lock_waits は廃止され、performance_schema.data_locks（保持中と待機中のロック一覧）と data_lock_waits（どのロックがどのロックを待っているか）に置き換わりました。
「誰が誰を待たせているか」をすぐ知りたいときは、sysスキーマのビューが読みやすいです。
-- 待たせている側と待っている側のスレッドID・SQLをまとめて表示
SELECT * FROM sys.innodb_lock_waits\G
-- 実行中のトランザクション一覧（LOCK WAITになっているものを探す）
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx;trx_state が LOCK WAIT の行が待っている側です。待たせている側がcommitし忘れたセッションなら、そのセッションでcommitかrollbackをするか、やむを得ない場合は KILL スレッドID; で切断するとロックが解放されます。
デッドロックの確認方法
-- deadlockの確認
SHOW ENGINE INNODB STATUS\G上のコマンドを実行すると、Statusの項目に最近発生したデッドロックの情報が記載されているのが確認できます。
長いので、コピーして、メモ帳などに貼り付けると確認しやすくなります。
「LATEST DETECTED DEADLOCK」という項目に、thread idや、デッドロックが発生したSQL、どちらのトランザクションがロールバックされたかが記録されています。
ここに残るのは最後の1件だけです。発生頻度を追いたい場合は innodb_print_all_deadlocks をONにすると、すべてのデッドロックがMySQLのエラーログに書き出されます。
SET GLOBAL innodb_print_all_deadlocks = ON;システム変数の確認・変更方法は【MySQL】システム変数とは？その確認、取得、変更方法、実際に流れたSQLを追う方法はDBeaverで実行したSQLを確認する方法｜MySQL general logとの違いで解説しています。