MySQL, SQL, トラブルシューティング

MySQL デッドロック対処

MySQLのデッドロック対処 おまけでギャップロック

寄稿しました。

今回はトランザクションでよくありがちなデッドロックのご紹介。

 

 

 

 

たすきがけのデッドロック

よくデッドロックはなんぞや?といった時に提示されるパターンです。

 

 

 

 

解決には?

テーブルへの書き込みの順番を揃えてあげることで解決です!

簡単ですね!

 

 

外部キー制約によるデッドロック

外部キー制約があるテーブルはINSERT時に親テーブルに共有ロックがかかります。

 

 

 

 

 

 

たすきがけになっていないはずなのに…どうして?

何気に見逃しがちなパターンです。

 

解決

外部キー制約がされているテーブルを操作する前に、親のテーブルに対して先に排他ロックをかけておきます。

 

 

 

すぐに対応が出来ない!

設計の見直しや検証が終わるまで対症療法として、デッドロックになっても早くタイムアウトするようにしておく、という手もあります。

 

/etc/my.cnf

 

プログラムでロールバックする

また、プログラム側でデッドロックを検地した場合はロールバックする仕組みを実装することも出来ます。

 

ロールバックの仕組みの実装と同時にデッドロック時にログに出力するようにしたり、メールなどの通知でデッドロックを検知する仕組みを実装すると良いかもしれませんね!

 

 

ギャップロック

ギャップロック: これはインデックスレコード間にあるギャップのロック、または先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロックです。

@see https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html

インデックスの空振りロックです。

 

 

 

 

パターン1 排他ロックで対象IDにレコードが存在しない場合

 

Aトランザクション

 

Bトランザクション

 

Aトランザクション

ギャップロックがかかる。

 

 

パターン2  指定IDの排他ロックを空振りした場合

 

Aトランザクション

 

Bトランザクション

 

ギャップロックがかかる。

 

 

 

1つのテーブルでデッドロックする場合

 

 

1テーブルでもデッドロックは発生する

上記のようにテーブルを作成します。

 

 

 

 

レコードの更新順によっては、1つのテーブルでもたすきがけとなりデッドロックが発生します。

複数レコードを日時などの範囲指定でいっきに更新する時など発生しがちです。

 

解決には?

 

FOR UPDATEで排他ロックをかけてあげればOKです。

 

 

デッドロックをログに出したい

innodb_print_all_deadlocksをONにすればエラーログとしてログに出力することが出来ます。

 

現在の設定を確認します。

OFFになっていますね。

 

 

 

 

デッドロックのログを見てみよう!

実は事前に設定あるので、こんな風にデッドロックの箇所を確認することが出来ます。

 

お疲れ様です。

 

 

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)