※ この記事はMySQL Casual Advent Calendar 2017の11日目の記事です。

A critique of ANSI SQL isolation levelsを読んで(読んだブログ)、MySQL(innodb)で分離レベルごとのanomaly(不整合)の発生について実験しました。使ったのはDockerで立てられる 9.0.3-rc-log MySQL Community Sereverです。

ここでは上記の論文であげられているanomalyとid:kumagiさんのブログ(いろんなAnomaly)で知ったread only anomalyが起こるかを分離レベルごとに試してみます。最初に、それぞれのanomalyについての簡単な説明とkumagiさんのブログで使っている書き方を真似た図、それに対応するプランを整理し、(実行経過は省略してw)結果だけ書きます。

※ この実験のために、mytxという自作コマンドラインツールを作ってみました（本当はそれについてを投稿するつもりでした)。ここで書いているプランとはmytxコマンドで指定する複数トランザクションの実行手順書のことですが、プランは単にトランザクション番号とSQLのペアを1行ずつ、実行順序に書いたcsvファイルです。今回使ったプランはmytxのsampleディレクトリにあるので、mytxをgo getすれば同じコマンドを試すことが出来ます！(詳しくはmytxの紹介ブログで)

準備

今回使ったテーブル定義です。init.sqlで、テーブルの構造と初期データを次のように用意します。 Dockerで立てた実験用のMySQLを相手に使っているので、ロックのタイムアウト時間と分離レベルをGLOBALに設定してます。

SET GLOBAL innodb_lock_wait_timeout=6000;
-- SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DROP TABLE IF EXISTS t1;

CREATE TABLE sample.t1 (
  id int AUTO_INCREMENT NOT NULL PRIMARY KEY,
  c1 int UNIQUE KEY,
  c2 int
);

INSERT INTO t1 (c1, c2) VALUES(1, 10);
INSERT INTO t1 (c1, c2) VALUES(2, 20);
INSERT INTO t1 (c1, c2) VALUES(3, 50);
INSERT INTO t1 (c1, c2) VALUES(4, 50);
INSERT INTO t1 (c1, c2) VALUES(5, 100);

実験するAnomaly

今回実験する以下のanomalyについて整理します

Dirty Read
Fuzzy Read
Phantom Read
Lost Update
Read Skew
Write Skew
Read Only Anomaly

Dirty Read

書き込み途中のトランザクションのデータを読みだしてしまうことで発生する不整合。

f:id:tom__bo:20171210175859p:plain:h150

ROLLBACKとCOMMITは適当。 2行目で1行目のc2=70が読めるかで確認。

1,UPDATE t1 SET c2 = 70 WHERE id = 4
2,SELECT * from t1 WHERE id = 4
1,ROLLBACK
2,COMMIT

Fuzzy Read

リードロックに対して書き込みが行えてしまうことで発生する不整合。

f:id:tom__bo:20171210175913p:plain:h150

1行目と4行目で違う結果になるかで確認。

1,SELECT c2 from t1 WHERE id = 4
2,UPDATE t1 SET c2 = 70 WHERE id = 4
2,COMMIT
1,SELECT c2 from t1 WHERE id = 4
1,ROLLBACK

Phantom Read

phraseによって指定された条件を満たすデータを読んでいる時に、その条件を満たすデータが挿入されてしまうことで発生する不整合

f:id:tom__bo:20171210175925p:plain:h150

1行目と４行目で違う結果になるかで確認。

1,SELECT c1 from t1 WHERE c2 = 50
2,INSERT INTO t1 (c1, c2) VALUES (6, 50)
2,COMMIT
1,SELECT c1 from t1 WHERE c2 = 50
1,ROLLBACK

Lost Update

Multi version concurency control(MVCC)の実装において競合するデータに対する変更の内片方が上書きされてしまう不整合。

f:id:tom__bo:20171210175938p:plain:h150

id=4のデータが元の値から+40された結果になっているかで確認。　 Tx3のコマンドは確認用に入れています。

1,SELECT c2 INTO @a from t1 WHERE id = 4
2,SELECT c2 INTO @b from t1 WHERE id = 4
1,UPDATE t1 SET c2 = @a + 10 WHERE id = 4
2,UPDATE t1 SET c2 = @b + 30 WHERE id = 4
1,COMMIT
2,COMMIT
3,SELECT * from t1 WHERE id = 4
3,ROLLBACK

Read Skew

データ間に制約がある場合にその制約に違反する結果が読めてしまう不整合。

f:id:tom__bo:20171210175953p:plain:h180

MySQLでカラム間で制約をかける機能はないので、1行目と４行目でUNIQUE制約をかけたc1カラムで違反するデータが読めるかで確認。

1,SELECT c1 FROM t1 WHERE id = 3
2,UPDATE t1 SET c1 = 100 WHERE id = 3
2,UPDATE t1 SET c1 = 3 WHERE id = 4
2,COMMIT
1,SELECT c1 FROM t1 WHERE id = 4
1,ROLLBACK

Write Skew

データ間に制約がある場合にその制約に違反する結果が書き込めてしまう不整合。

f:id:tom__bo:20171210180008p:plain:h180

Read skewと同様にUNIQUE制約を利用して、duplicate errorが発生するかで確認。 duplicate errorが発生するということは排他制御でserializableに処理できなかったという理解。

1,SELECT c1 INTO @a FROM t1 WHERE id = 3
1,SELECT c1 INTO @b FROM t1 WHERE id = 4
2,SELECT c1 INTO @c FROM t1 WHERE id = 3
2,SELECT c1 INTO @d FROM t1 WHERE id = 4
1,UPDATE t1 SET c1 = @a + @b WHERE id = 3
1,COMMIT
2,UPDATE t1 SET c1 = @c + @d WHERE id = 4
2,COMMIT
3,SELECT * FROM t1 WHERE id = 3 OR id = 4
3,ROLLBACK

Read Only Anomaly

2つのトランザクション(ここでいうTx1,Tx2)であれば問題がないが、これらの間にデータを読むだけのTx3が入ることで不整合となるアノマリー。

f:id:tom__bo:20171210180021p:plain:h200

Tx3とTx4で同じ結果になるかで確認。
Tx4は確認用に入れています。

1,SELECT c2 INTO @a FROM t1 WHERE id = 3
1,SELECT c2 INTO @b FROM t1 WHERE id = 4
2,SELECT c2 INTO @c FROM t1 WHERE id = 3
2,UPDATE t1 SET c2 = @c + 100 WHERE id = 3
2,COMMIT
3,SELECT c2 FROM t1 WHERE id = 3
3,SELECT c2 FROM t1 WHERE id = 4
3,COMMIT
1,UPDATE t1 SET c2 = IF(@a = @b, @a - 10, @a) WHERE id = 4
1,COMMIT
4,SELECT c2 FROM t1 WHERE id = 3
4,SELECT c2 FROM t1 WHERE id = 4
4,COMMIT

結果

上記の設定でアノマリーが発生するか確認した結果以下の図のようになりました。アノマリーが発生した場合にO、発生しなかった場合にXを書いています。

f:id:tom__bo:20171210232434p:plain

critique論文の分類ではRepeatable ReadレベルではLost Updateが発生しますが、MySQLでは発生しませんでした。 MySQLにはMVCCも組み込まれているためか、SERIALIZABLEではLost UpdateとWrite SkewでDeadlockが発生しました。SERIALZABLE以外の分離レベルでWrite Skewのanomalyが発生するかについては、duplication errorが発生したためanomalyが起きたということにしました。

また、Lost Update, Write Skewの実験でSELECTのコマンドにFOR UPDATEをつけた場合はどの分離レベルであってもアノマリーは発生しないという結果になりました。

f:id:tom__bo:20171210150714p:plain

FOR UPDATEのケースを無視して、ここまでの結果をCritique論文のものと比較すると下のようになります。矢印の方向に分離レベルを移動すると矢印に沿って書いてあるanomalyが発生しなくなるという図です。

f:id:tom__bo:20171211140840p:plain — critique論文の分類(左)とMySQLの分類(右)でのアノマリーの結果

Phantom ReadがRepeatable Readで起こらないこととSnapshot Isolation(MVCC)によるanomalyがRead Commited からSerializableの間に入ったことがわかりやすくなりました。

まとめ

MySQLのinnodbにおけるトランザクション分離レベルごとの違いを実験しました。分離レベルやFOR UPDATE句の違いで、実行途中でどういうロックが取られていくかを書くつもりでしたが、全然間に合いませんでした。それはそのうち書きます。

tom__bo’s Blog

情報系学生が筋トレしたり、筋トレしたり筋トレしたことを書くブログ。もはやダイアリー

MySQL(innodb)の分離レベルごとのanomalyについて実験した

準備