MySQLのロックの種類とその競合

MySQLには大きく2つのロックがある

ロックの競合により性能が劣化する

ロックの範囲を理解し、競合が最小になるようにスキーマや操作を検討する

インデックスの選択性を高くするとロック範囲が小さくなる

メタデータロック

テーブル構造にかけるロック

データ量によらずロックの速度は一定

ロック後の処理は一定ではない

commitかrollbackでトランザクション終了するまでロックは残る

発生するロックの種類はクエリによる

メタデータの排他ロック

CREATE TABLE, DROP TABLE, ALTER TABLE

オンラインDDLは排他->共有->排他のうち真ん中の間は読み書きできるもの

UPDATE, INSERT, DELETEは共有書き込みメタデータロックが必要

クエリの実行中だけでなくトランザクションの間中ずっと共有ロックをとっている

トランザクション中に外部APIを叩くのがBadな理由の1つ

InnoDBロック

インデックスレコード1つずつにロックをかけるので、ロックをかけるトータルレコード数がロックにかかる時間に影響する

ロックの範囲

https://gyazo.com/9f91525fd74d5e68032216e7607a8ca8

実際はネクストキーロックとギャップなしロックの2つであることが大半

無限小であるinfimum、無限大であるsupremumの間のギャップにロックがかかっている場合、間には挿入できない

ロックのかけかた

セカンダリインデックスにネクストキーロックをかけ、プライマリインデックスにギャップなしロックをかける

https://gyazo.com/a08ffce9b0a91b5be81d4aabcdc311ac

トランザクション分離レベルがREAD COMMITTEDの場合はセカンダリキーのロックがギャップなしロックに軽減される

セカンダリインデックスによる選択性が高ければロックの粒度が細かくなり、競合しにくくなる

https://gyazo.com/eda943b0b807bba9d14fd5ed9731a41c