トランザクションは慎重に。どうも、かわしんです。今トランザクションを実装しています。
タイトルは釣りです。MySQL のデフォルトのトランザクション分離レベルである REPEATABLE READ での話です。後、確かめた訳ではないですが MySQL に限った話ではないです。
さて、トランザクションの用語に Strictness (ST) と Rigorous (RG) というものがあります。詳しくはこの記事を読むとヒントになるかもしれません。
トランザクションの Strictness と Rigorousness の定義を再確認する - ぱと隊長日誌
これら2つの違いはあるトランザクションがあるレコードを Read した時に、Strictness は別のトランザクションでも そのレコードに Write できる のに対して、Rigorous は Read したトランザクションが Commit か Abort するまで Write がブロックされる かの違いです。Rigorous の方が Strictness よりも厳密です。
つまり 2PL の文脈でいえば、内部的に取得した Read Lock を Commit/Abort 前に解放するかどうかの違いになります。
今回はこれを実際の MySQL で試して確認し、実際のアプリケーションでのユースケースで気をつけるべきところを紹介します。
実験をしてみる
今回試したのは、MySQL 8.0.15 です。Docker で動かしています。
テーブルは以下の構造で、1つだけレコードを追加しています。
CREATE TABLE hoge ( id INT NOT NULL, value INT NOT NULL default 0, PRIMARY KEY (id) ); INSERT INTO hoge (id, value) VALUES (1, 1);
2 つのコネクションを作成しそれぞれでトランザクションを作って id = 1 のレコードの value をカウントアップする実験をしていきます。
操作は全て mysql クライアントコマンドで行っています。
普通に値を更新して Rigorous でないことを確かめる
2 つのコネクションからそれぞれ以下の SQL を同時に１行ずつ実行します。
BEGIN; SELECT * FROM hoge WHERE id = 1; UPDATE hoge SET value = 2 WHERE id = 1; SELECT * FROM hoge WHERE id = 1; COMMIT;
片方で BEGIN した後にもう片方で BEGIN をして ... と行った具合です。
まず、両方のトランザクションで id = 1 の value は 1 であるとわかります。
mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
そこで両方のトランザクションでその値に 1 を加えた 2 を更新します。
mysql> UPDATE hoge SET value = 2 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 2 | +----+-------+ 1 row in set (0.00 sec)
片方のトランザクションはこのように更新に成功します。少なくとも SELECT したトランザクションがある中で UPDATE が成功しているため、Rigorous ではない ということがわかります。
更新のブロッキングと奇妙な挙動
さて、片方のトランザクションですが、ブロックされレスポンスが返ってきません。
そして、更新に成功したトランザクションを COMMIT するとブロックされたトランザクションが動き始めます。
mysql> UPDATE hoge SET value = 2 WHERE id = 1; Query OK, 0 rows affected (7.08 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec)
しかし、値の更新に失敗 します。Changed: 0 となっていることからも更新に失敗していることがわかります。
ここで別の値（例えば 3 とか）に更新すると更新に成功します。
なぜ同じ値だと更新に失敗して違う値だと更新に成功するのかがよくわかりません 。両方成功していいはずです。
これはあくまでも僕の想像ですが、同じ値に更新するということは前の値に対して同じ相対的な操作（この場合はカウントアップ）を行っている可能性が高いです。そのため、意図せぬ競合状態によるデータの不整合が発生している可能性が高く、そのバグに利用者が気付きやすくするためにあえて更新を失敗させているのではないかと思いました。
もしカウントアップなどの相対的な操作ではなく、両方ともその値にするという絶対的な操作であった場合でも更新に失敗しても最終的な値はその値になっているため、データが壊れるということはなくデメリットは小さいです。
こういう利用者のミスに優しい MySQL の親切設計なのではないかと推察しました。
逆に、片方では +1 して片方では +2 するみたいなユースケースでは更新に失敗しないため注意が必要です。
正しくカウントアップを成功させる
SELECT した値に操作をして UPDATE しても更新に失敗することがわかりました。ではどうすれば正しく値のカウントアップができるのでしょうか？
方法は2つあります。
- ロックをとる
UPDATE文を工夫する
１つは SELECT するときに ロックをとる 方法です。MySQL では、SELECT 文の末尾に FOR UPDATE をつけることでロックを取得できます。ロックを取得したトランザクションが Commit/Abort するまで別のトランザクションのロックはブロックされ、ロックを獲得できた時には更新された値を取得することができます。
SELECT * FROM hoge WHERE id = 1 FOR UPDATE;
もう1つは UPDATE 文を工夫する というものです。値の相対的な操作がカウントアップなど単純な場合は、value = value + 1 とすると相対的な操作がされて更新されます。
UPDATE hoge SET value = value + 1 WHERE id = 1;
別のトランザクションで更新された値に対して加算が行われるため、MySQL の REPEATABLE READ のトランザクション分離レベルであっても、以下のように 1 と SELECT された値に 1 を足したら 3 になったみたいな一見直感に反する結果になります。
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 1 | +----+-------+ 1 row in set (0.00 sec) mysql> UPDATE hoge SET value = value + 1 WHERE id = 1; Query OK, 1 row affected (1.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hoge WHERE id = 1; +----+-------+ | id | value | +----+-------+ | 1 | 3 | +----+-------+ 1 row in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
SERIALIZABLE ではどうなるか
MySQL は Rigorous ではないと大見得を切ってしまいましたが、MySQL でもトランザクション分離レベルを SERIALIZABLE にすると Rigorous になります。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
2 つのトランザクションで SELECT した上で UPDATE するとブロックされて処理が返ってきません。
そのあとに両方のトランザクションで UPDATE するとデッドロックしてしまいます。
mysql> UPDATE hoge SET value = 14 WHERE id = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
SERIALIZABLE は厳密にはなりますが速度が遅くなってしまうため大量の処理を捌くには向いていないです。
まとめ
MySQL の Rigorous の挙動を調べるために色々脱線しましたがまとめると以下のようになります。
- MySQL のデフォルトのトランザクション分離レベルである
REPEATABLE READでは Rigorous ではない- ただし同じ値に対して同じ操作をしたと思われる場合は親切にも MySQL は更新を失敗させる
- 相対的な値の操作をする場合は
SELECT ~~ FOR UPDATEしてロックをとるか、UPDATE ~~ value = value = 1と SQL 内に相対的な操作を記述する。 SERIALIZABLEは Rigorous になるが遅いし、容易にデッドロックする
アプリケーションのユースケースとしては、ポイントの加算など値の相対的な操作をすることはよくあると思います。
その際は必ずロックを取るか SQL 内に相対的な操作を記述することでエラーのない整合性の取れたデータ更新ができるようになります。
