MySQL

 

MySQLのlock in share mode

MySQLのlock in share mode、ちゃんと使っていますか?for updateと何が違い、どういう時に使い、どんなメリット・デメリットがあるのか、それを検証したいと思います。共有ロックの使い方を把握し、ファントムリード等が発生しないよう注意しましょう。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

lock in share modeは、共有ロック・shared lock・slock、と言われているロックです。

for updateは、排他ロック・xlock、と言われています。

参照(select)する時にロックをする機能です。

主な使い道は、ファジーリード・ロストアップデートを防ぐ、です。

ファジーリードはトランザクション分離レベルがread committed以下の場合の発生します。repeatable readの場合はそもそもANSI/ISO SQLの仕様上発生しません。

具体的な使い所としては、一覧画面の表示時のSQLや、不正データ検出、等で使うかと思います。

最大の違いはslockによる参照をブロックしない・されない点です。つまり、参照同士のブロックはされません。

slock (lock in share mode) は、slock (lock in share mode) をブロックしません。

slock (lock in share mode) は、xlock (for update) をブロックします。

slock (lock in share mode) は、xlock (for update) にブロックされます。

slock (lock in share mode) は、slock (lock in share mode) にブロックされません。

要は、slock同士のみブロックしない、という事です。

解りやすいように一覧にすると以下の通りです。

slock xlock update or delete
slock ブロックしない。 ブロックする。 ブロックする。
xlock ブロックする。 ブロックする。 ブロックする。
update or delete ブロックする。 ブロックする。 ブロックする。

※ update or deleteは更新処理なのでxlock(for update)と同じ結果になります。

例えば、一覧画面表示にfor update等を使ってしまうと、ブロックだらけで酷いパフォーマンスになります

自分が参照するだけなら、他セッションの更新のブロックは必要でも、参照のブロックは必要ありません。

だから、lock in share modeを使うのです。

こうして見ると万能に見えてしまうのがlock in share modeですが、デメリットもあります。

slockに限り、ロックの獲得を許してしまう点です。

ここが実は問題で、slockした後に何らかの更新処理でxlockをかけてしまうと、デッドロックしてしまいます。

詳しくは、外部キーとデッドロックを参照して下さい。

例えば、最初は参照処理のみだったのに、仕様変更等で更新処理も必要になった場合、lock in share modeをfor updateを変更しないと、デッドロックしてしまいます。

さて、slockをxlockに変更しないといけないという事に気づけるでしょうか。

同一データをslock => xlockするとデッドロックするので、同時実行しないとまず気付けないのではないでしょうか。

加えて、slockとxlockの動作を理解しているフロントの開発者は以外と少ないので、大抵この自体は防げません。

前述のように、slockの後に更新処理を入れないようにすることです。

防ぐ方法としては、更新処理を共通メソッドに切り出してlock in share modeをfor updateに変更する場所を一元管理し、注意をコメントに記述しておくといいかと思います。

ORMをカスタマイズし、slock後に更新クエリを発行すると実行時エラーを強制的にスローするのもありでしょうか。

もし参照件数が少ないのであれば、割りきって全てfor updateでもいいかもしれませんね。

treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告