MySQLでテーブルが排他ロック（stateがLocked）になり全部待ち状態になった原因まとめ

2016/11/20 MySQL

MySQLでテーブルが排他ロックになり、全部待ち状態になり動かなくなりました。

先ず、画面（ブラウザ）に何も応答がなくなってしまったので、

でどのSQLが動いているか確認したところ、以下の順で命令されたSQLがロック状態でした。

  1. 重い参照処理（select）　実行中
  2. 参照処理（select）　Locked
  3. 更新処理（insert, update）　Locked
  4. 参照処理（select）　Locked

4.参照処理もロックされているので、画面表示すらも何も出来なくなっていました。

 

この環境のMySQLのバージョンは5.1です。

今回は、この原因と対応方法を調べたので記録しておきます。

スポンサーリンク

原因

ストレージエンジンがMyISAMだった

結論として、該当テーブルのストレージエンジンにMyISAMを使っていた為でした。

今回の環境でのMySQLのバージョンは5.1でした。

MySQL5.1ではデフォルトのストレージエンジンがMyISAMだったのです。MySQL5.5からようやくデフォルトのストレージエンジンがInnoDBにになっていたのです。

公式　https://dev.mysql.com/doc/refman/5.6/ja/innodb-default-se.html

MySQL 5.5 よりも前では、MyISAM がデフォルトのストレージエンジンでした。我々の経験上、ほとんどのユーザーはデフォルト設定を変更しませんでした。MySQL 5.5 以上では、InnoDB がデフォルトのストレージエンジンです

 

該当テーブルがMyISAMか調べるコマンドは以下です。

https://normalblog.net/system/mysql/mysql_matome/#InnoDBMyISAM

何故、MyISAMだからロックの原因になるのか

ロック方式

ロック方式には以下の２種類があります。

  • 共有ロック（READロック）　参照可能・更新不可能
  • 排他ロック（WRITEロック）　参照不可能・更新不可能

テーブルロック・行ロック

ロック対象には以下の２種類があります。

  • テーブルロック　対象テーブル全体をロック　MyISAM対応
  • 行ロック　対象テーブルの対象行（レコード）のみをロック　InnoDB対応

※InnoDBでもテーブルロックになる場合があります。

つまり

MyISAMで更新処理（insert, update）をするとテーブル全体を排他ロックしてしまいます。排他ロックなので、ロック中は参照処理（select）する事すら待ち状態になってしまいます。

今回、更新処理（insert, update）と参照処理（select）両方がロックになっていたのは何故？

今回の現象に照らし合わせてみると新たな疑問が出てきました。

  1. 重い参照処理（select）　実行中
  2. 参照処理（select）　Locked
  3. 更新処理（insert, update）　Locked
  4. 参照処理（select）　Locked

上の理論からすると、3.更新処理（insert, update）が実行中ならば、4.参照処理（select）がロックされてしまうのはわかるのですが、まだ1.重い参照処理（select）中なので、2.参照処理（select）と4.参照処理（select）は参照出来ず、ロックされてしまったのは何故だったのか？

更新処理の優先度の方が参照処理よりも高いから

クエリには優先度があり、更新処理が発生した後に発生した参照処理が、更新前の情報を持って帰らないようにしています。

その為、3.更新処理（insert, update）のロックに合わせて、後続の4.参照処理（select）までがロックになってしまったという事のようです。

2.参照処理（select）までがロックになってしまった理由は、やはり優先度が更新処理の方が高い為、順番に関係なく、3.更新処理（insert, update）が待ち状態になったので、未実行だった、2.参照処理（select）もロックになったと推測されます。

参考サイト様　運用視点なMyISAMとInnoDBと。

http://tech.lexues.co.jp/archives/1405

運用あるある
共有ロックなのに排他ロックになる
参照クエリと更新クエリでは更新クエリの方が優先順位が高いです。重い参照クエリを投げてそのテーブルがロックされたあと、更新クエリを発行すると、実行中の参照クエリの次に更新クエリを実行しようと優先順位を上げます。その瞬間、共有ロックだったはずのテーブルが排他ロックになります(更新クエリが終わってから次の参照クエリを実行しようとするため)。更新処理が終わるまでは、参照できなくなってしまいます。

参考サイト様　MySQL ロックの話

WRITEロックキューとREADロックキューがあり、WRITEロックキューが優先的に処理される

まとめ・対応方法・案

というわけで、

  1. 重い参照処理（select）　実行中
  2. 参照処理（select）　Locked
  3. 更新処理（insert, update）　Locked
  4. 参照処理（select）　Locked

このようにロックになってしまった場合、先ずMyISAMから調査してMyISAMだった場合は、

  1. MyISAMをInnoDBに変更する。
  2. SQLの実行時間を短くする。

この２点が考えられました。

MyISAMをInnoDBに変更するについては以下コマンドで出来ますが、調べていくと、気を付けたい事項などが色々出てきましたので別途まとめます。

https://normalblog.net/system/mysql/mysql_matome/#MyISAMInnoDB

参考情報

http://amzn.to/2oJFFzf