MySQL

 

MySQLのトランザクション分離レベル

トランザクション分離レベルについての動作と、それぞれで発生する現象・対策についてまとめます。沢山ある謎の用語が何を意味するかを把握し、まずは種類を覚えましょう。種類を把握したら、それに対する対処方法を正しく把握し、アプリのバグや不正データが発生しないようにしましょう。

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

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

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

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

◯ 広告

ANSI/ISO SQLでは、以下のように定義されています。

ロストアップデートについては特に策定されていないと思いますが、一覧に加えておきます。

分離レベル 性能 ダーティーリード ファジーリード ファントムリード ロストアップデート
read uncommitted 起きる。 起きる。 起きる。 起きる。
read committed 起きない。 起きる。 起きる。 起きる。
repeatable read 起きない。 起きない。 起きる。 起きる。
serializable 起きない。 起きない。 起きない。 起きない。

ただし、ANSI/ISO SQLはあくまで仕様であって、実装・動作は各データベース毎に異なります。

MySQLの場合は以下のようになります。

分離レベル 性能 ダーティーリード ファジーリード ファントムリード ロストアップデート
read uncommitted 起きる。 起きる。 起きる。 起きる。
read committed 起きない。 起きる。 起きる。 起きる。
repeatable read 起きない。 起きない。 起きない。 起きる。
serializable 起きない。 起きない。 起きない。 起きない。

repeatable readでファジーリードが起きない理由は、select時にスナップショットを保持しておき、再度読み取る場合はスナップショットを読み込むためです。

スナップショットは自セッショ以外には見えないので、他セッションによるデータの改変を防いでくれるのです。

つまり、反復可能な読み取りが行えるのです。

MySQLでrepeatable readでファントムリードが起きない理由は、ネクストキーロックがあるためです。

MySQLのネクストキーロックは次の行をロックしてしまう事でinsertされる事を防いでくれるのです。

次の行のデータが無い場合、次の行をロックしてinsertさせなければファントムは現れません。

もし次の行にデータが有る場合は、そもそもinsertできないのでファントムは現れません。

仮にデータの最後の行をselectした場合、ネクストキーロックは最後の行以降の行を全てロックします(勿論存在しない行です)。

存在しない行を全てロックしてしまう事で、新規にinsertさせない、つまりファントムが現れません。

ネクスト キー ロック:ファントムの問題を防ぐ

現象について別名が色々有って情報が分散している気がするので、別名も合わせてまとめます。

No. 名称・別名 内容
1 ダーティーリード コミット前のデータを参照してしまうこと。見ているデータがロールバックされた場合、自分の見ていたデータは既に存在しないか、別のデータになる可能性があります。
2 ファジーリード
ノンリピータブルリード
反復不能読み取り
自セッションのトランザクション中に、他セッションがコミットした更新データを参照してしまう。最初に見ていたものと別のものが見えてしまうのです。
3 ファントムリード
ファントムインサート
自セッションのトランザクション中に、他セッションがコミットした追加データを参照してしまう。見えてはいけないもの(ファントム)が見えてしまうのです。
4 ロストアップデート 他セッションの更新を、自セッションの更新で上書きしてしまう、後勝ち更新となる。。

他セッションの未commitデータを参照してしまうのだから、他セッションの更新をブロックします。

自セッションが参照しかしないなら、lock in share modeでslockして下さい。xlockだと参照の性能が落ちます。

自セッションが更新もするなら、for updateでxlockして下さい。slockだとデッドロックする可能性があります。

他セッションのcommit済みupdateデータを参照してしまうのだから、他セッションの更新をブロックします。

解決方法はダーティーリードと同じです。

他セッションのcommit済みinsertデータを参照してしまうのだから、他セッションの更新をブロックします。

解決方法はダーティーリードと同じですが、場合によってlock tablesでテーブルロックが必要になります。

select count(*) from test; 等、where句が無ければlock tablesが必要になります。

もしwhere句でインデックスを貼ったカラムを指定しているなら、そのカラムの値に対してs・xlockします。

インデックスを貼っていないカラムの場合はフルスキャンになるので、for updateしてもlock in share modeしても、結局スキャンした全件がロック対象となる為、テーブルロックになります。

安易にlock tablesしようとせず、select * from hoge where id = 1 for update; 等で絞り込んだ方がいいです。

repeatable readの場合は対策の必要はありません。

理屈的には、ネクストキーロックによって、insertされる可能性のあるレコード(存在しないレコードも)ブロックして防いでいます。

他セッションのcommit済みデータを自セッションで上書きしてしまうので、他セッションの更新をブロックします。

解決方法はダーティーリードと同じです。

ロストアップデートだけは、InnoDBのrepeatable readでは防げません。

repeatable readはスナップショットを読み取るので、自分のスナップショットで他人のデータを上書いてしまいます。

従って、スナップショットを取る時点で他人の更新をブロックしておく必要があるのです。

Falconストレージエンジンの場合は、ロストアップデートを自動検知して、必ず先勝ちになるようです。

ロストアップデートが起きた場合は実行時エラーが返されるので、相応の対応が必要になりそうです。

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

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

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

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

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

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

◯ 広告