Closed18

[記事下書き] MySQL/Postgres におけるトランザクション分離レベルの実際

記事をパブリッシュしました。以下を参照してください↓

https://zenn.dev/mpyw/articles/rdb-transaction-isolations

SQL 標準

アノマリー

分離レベル\事象 ダーティリード ファジーリード ファントムリード 直列化異常
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
事象 意味
ダーティリード 他のトランザクションで コミットされていない更新 を参照してしまう
ファジーリード 他のトランザクションで コミットされた更新 を参照してしまう
ファントムリード 他のトランザクションで コミットされた新規作成・削除 を参照してしまう
直列化異常 複数のトランザクションを並列実行した結果が直列実行した場合と等価にならない

実際はどう?

共通で言えること

  • MySQL/Postgres とも,ファジーリードとファントムリードはセットで起こったり起こらなかったりするようになっているため, SQL 標準のように更新なのか新規・削除なのかを意識する機会は少ないです。
  • 一貫性読み取りで参照するデータは,更新時に参照するデータ本体とは隔離された スナップショット になります。
文の種類 アクション 参照先 ロック
SELECT 一貫性読み取り スナップショット 無し
SELECT ... FOR SHARE ロック読み取り データ本体 共有ロック
SELECT ... FOR UPDATE ロック読み取り データ本体 排他ロック
INSERT
UPDATE
DELETE
書き込み データ本体 排他ロック

MySQL

アノマリー

分離レベル\事象 ダーティリード ファジーリード ファントムリード 直列化異常
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ 🔺 🔺
SERIALIZABLE

特異的な特徴が分かるように分解したもの↓

分離レベル\アクション 一貫性読み取り ロック読み取り
書き込み
READ UNCOMMITTED ❌ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
❌ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
READ COMMITTED ✅ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
✅ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
REPEATABLE READ
(デフォルト)
✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
❌ 直列化異常
✅ ダーティリード
🔺 ファジーリード
🔺 ファントムリード
❌ 直列化異常
SERIALIZABLE すべてロック読み取り化 ✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
✅ 直列化異常

ロック

分離レベル\アクション 一貫性読み取り ロック読み取り
書き込み
READ UNCOMMITTED
READ COMMITTED
無し レコードロック
REPEATABLE READ
(デフォルト)
無し レコードロック
ギャップロック(一部はネクストキーロックにも該当)
SERIALIZABLE 未定義 レコードロック
ギャップロック(一部はネクストキーロックにも該当)

エラー

エラー原因 メッセージ例
デッドロック ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  • READ COMMITTED 以下では,毎回最新のスナップショットを見ます。
  • REPEATABLE READ 以上では,SELECT 実行で獲得したスナップショットバージョン をトランザクションの終了まで保持し続けます。同じクエリに対しては同じ結果が保証されています。
    • スナップショットを参照しないロック読み取りに関しては, READ COMMITTED と同じような動きになり,毎回最新のスナップショットを見ることになります。
  • REPEATABLE READ のアノマリーに関しては,かなり特異な特徴を持ちます。
    • 一貫性読み取りだけをしている限りでは,スナップショットバージョンが固定されているので,ファジーリードやファントムリードは発生しません。
    • ロック読み取り・書き込みだけをしている限りでは, ギャップロック があるため,ファジーリードやファントムリードは発生しません。
    • 「一貫性読み取り」と「ロック読み取り・書き込み」の間に整合性はありません。 最初の一貫性読み取りでは現れなかった変更やファントムが,次のロック読み取りで現れることがあります。
  • SERIALIZABLE では, 一貫性読み取りはすべてロック読み取りに変換されるため,実質的に存在しません。

https://softwarenote.info/p1067/

https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html

Postgres

アノマリー

分離レベル\事象 ダーティリード ファジーリード ファントムリード 直列化異常
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

Postgres は MySQL のように特異的な特徴は無し↓

分離レベル\アクション 一貫性読み取り ロック読み取り
書き込み
READ COMMITTED
(デフォルト)
✅ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
✅ ダーティリード
❌ ファジーリード
❌ ファントムリード
❌ 直列化異常
REPEATABLE READ ✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
❌ 直列化異常
✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
❌ 直列化異常
SERIALIZABLE ✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
✅ 直列化異常
✅ ダーティリード
✅ ファジーリード
✅ ファントムリード
✅ 直列化異常

ロック

分離レベル\アクション 一貫性読み取り ロック読み取り
書き込み
READ COMMITTED
(デフォルト)
無し レコードロック
REPEATABLE READ 無し レコードロック
更新競合に対する楽観ロック
SERIALIZABLE 直列化異常に対する楽観ロック レコードロック
更新競合に対する楽観ロック
直列化異常に対する楽観ロック

エラー

エラー原因 メッセージ例
デッドロック ERROR: deadlock detected
DETAIL: Process 37542 waits for ShareLock on transaction 2995; blocked by process 37541.
Process 37541 waits for ShareLock on transaction 2996; blocked by process 37542.
更新競合 ERROR: could not serialize access due to concurrent update
直列化異常 ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
  • READ COMMITTED では,毎回最新のスナップショットを見ます。
  • REPEATABLE READ 以上では,トランザクション開始時点で 参照するスナップショットバージョンが確定されます。クエリを実行するまではスナップショットバージョンを確定しない MySQL とはここが大きく異なります。 「トランザクション開始時から全テーブル・全レコードに対して楽観ロックがかけられている」 と考えてもよいでしょう。
  • SERIALIZABLE では, 直列化異常に対する楽観ロック も追加で行われます。更新競合には該当しない「SELECT による集計をサブクエリとして利用する INSERT」なども楽観ロックの対象になります。

https://www.postgresql.jp/docs/12/transaction-iso.html

http://www.nminoru.jp/~nminoru/postgresql/pg-transaction-mvcc-snapshot.html

で,結局どうするの?

以下は個人的な意見。

共通

  • 基本的には READ COMMITTED をベースにした上で ロック読み取り を活用せよ。通常は NO WAIT オプションを付与してブロッキングを回避し,即時失敗させるとよい。
  • ロック読み取りは空振りするとレコードロックが獲得できないので,その懸念がある場合は アドバイザリーロック を併用せよ。

https://zenn.dev/mpyw/articles/rdb-advisory-locks

MySQL

  • デフォルトの REPEATABLE READ から READ COMMITTED に変更して使用せよ。
  • もし REPEATABLE READ のまま使用する場合, ギャップロック に注意せよ。 レコードロックの空振り で意図せず発生させてしまわないように注意。
  • SERIALIZABLE は並列実行性が著しく落ちるため,実用性は薄い。
  • READ UNCOMMITTED の出番は基本的には無い。

Postgres

  • デフォルトの READ COMMITTED のまま使用せよ。
  • 競合頻度が低い場合, REPEATABLE READ 以上で観測される 楽観ロック の振る舞いに身を任せたほうがパフォーマンスが出る場合があると考えられる。トレードオフとして,リトライもしくはリトライを促すための処理をアプリケーション側で書く必要となる。
    • 業務システムの管理画面等では多少活躍の機会はあるか?と思われたが, NO WAIT を付ければそれだけでロック読み取りの欠点であるブロッキングは排除できるので,あまり明確な優位性がないような…

後で記事化しようと思いますが,おかしいところあったらコメントしてください

アノマリーもうちょい細分化したほうがいいかなー
「Lost Update」「Write Skew」「Read Only Anomaly」は直列化異常に含めちゃっていいような気もするけど

https://tombo2.hatenablog.com/entry/2017/12/11/141156

https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404

Postgres だと Repeatable Read でも Lost Update 回避できたりする点がややこしい…と思ったら↑のブログの間でも見解分かれててカオスなことになってる


MySQL だとロストすると思うけど, Postgres の REPEATABLE READ だと Concurrent Update 検知でエラーになるようにしか見えん…

試してみたけどなりますなぁ
この記事間違ってそう


なんかこの記事も上部の表と自己矛盾してるように見える


https://qiita.com/kumagi/items/5ef5e404546736ebac49

REPEATABLE READでは起きないがREAD COMMITEDでは起こりうる。

熊木さんは完全に逆のこと言ってるw
多分こっちが正しくて実験したほうのブログは書き間違いかな

これであってるか…?

(Non-Repeatable Read と Read Skew は Fuzzy Read にまとめました)

Postgres

READ COMMITTED REPEATABLE READ SERIALIZABLE
Dirty Read
Fuzzy Read
Phantom Read
Lost Update
Concurrent Update
Error Detection

Concurrent Update
Error Detection
Write Skew
R/W Dependencies
Error Detection
Read Only

MySQL

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE
Dirty Read
Fuzzy Read 🔺
Broken on
Locking Read
Phantom Read 🔺
Broken on
Locking Read
Lost Update
Write Skew
Read Only

Broken on Consistent Read は割愛でいいかなぁ
レコードロックしたら排他制御できるの当たり前だし

書き直し

理論面

理論面についてはこの記事の本質ではないため,外部へのリンクで割愛する。

https://developer.hatenastaff.com/entry/2017/06/21/100000

https://qiita.com/kumagi/items/1dc1a91ec007365ac694

実際はどう?

論文で提唱されているトランザクション分離レベルを MySQL と Postgres にどう対応させるかについては,以下の記事を参考にした。

http://blog.kimuradb.com/?eid=877571

https://www.kimullaa.com/entry/2020/03/14/134232

さらに Postgres の実装詳細については,以下の記事を参考にした。

http://www.nminoru.jp/~nminoru/postgresql/pg-transaction-mvcc-snapshot.html

https://blog.yux3.net/entry/2021/05/15/102916

以下では, ANSI-SQL92 という古典的な規格ではなく, A Critique of ANSI SQL Isolation Levels 論文に基づく対応付けを行う。また後に発見された Read Only Skew についても併記している。

共通事項

Anomaly

グルーピング 現象
読み取り不整合 Dirty Read
Fuzzy Read
Phantom Read
Read Skew
更新競合 Cursor Lost Update
Lost Update
直列化異常 Write Skew
Observe Skew
現象 意味
Dirty Read 他のトランザクションで コミットされていない 変更を 参照 してしまう
Fuzzy Read
Phantom Read
Read Skew
他のトランザクションでコミットされた変更を 参照 してしまう
Cursor Lost Update 他のトランザクションで Locking Read によって読み取った後
コミットされた変更を 上書き してしまう
Lost Update 他のトランザクションでコミットされた変更を 上書き してしまう
Write Skew トランザクション T_1 が読み取った x を使って y の変更するとき,
トランザクション T_2 が読み取った y の値を使って x を変更してしまい,
すれ違いざまに相手の変更前の値に依存した更新を行ってしまう
Observe Skew 2 つだけであれば直列化可能であったはずのトランザクション T_1 T_2
途中状態T_3 が観測することによって 循環参照 が発生し,観測者を含めた
T_1 T_2 T_3 の並び順を矛盾なく確定させることができなくなってしまう
  • MySQL/Postgres とも,Fuzzy Read と Phantom Read はセットで起こったり起こらなかったりするようになっているため, ANSI 標準のように更新なのか新規・削除なのかを意識する機会は少ない。ここでは簡単のため 1 つにまとめる。
  • Read Skew についても Fuzzy Read と現象の内容が似ているため,ここでは 1 つにまとめる。
  • Observe Skew については Read Only Anomaly, Read Only Skew, Batch Processing などさまざまな呼称が存在するが,どれも意味が伝わりにくいので,この記事で Observe Skew という名称を提案する。

Snapshot Isolation

文法 アクション 参照先 ロック
SELECT Consistent Read Snapshot -
SELECT ... FOR SHARE Locking Read Current Shared
SELECT ... FOR UPDATE Locking Read Current Exclusive
INSERT
UPDATE
DELETE
Write Current Exclusive
  • 一貫性読み取り (Consistent Read) では,更新時に参照する現在のデータ本体 (Current) とは隔離された読み取り専用の スナップショット をロックせずに取得する。
  • ロック読み取り (Locking Read) では,現在のデータ本体 (Current) をロックして取得する。

MySQL

Anomaly

READ UNCOMMITTED READ COMMITTED REPEATABLE READ
[Default]
REPEATABLE READ WITH CONSISTENT SNAPSHOT SERIALIZABLE
Dirty
Read
Fuzzy
Read

Phantom
Read

Read
Skew
🔺
Broken on
Locking Read
🔺
Broken on
Locking Read
Cursor
Lost
Update
Lost
Update
Write
Skew
Observe
Skew

Locking

分離レベル\アクション Consistent Read Locking Read/Write
READ UNCOMMITTED
READ COMMITTED
- レコードロック
REPEATABLE READ
[Default]
- レコードロック
ギャップロック(一部はネクストキーロック)
SERIALIZABLE すべて Locking Read 化 レコードロック
ギャップロック(一部はネクストキーロック)

Errors

原因 メッセージ例
デッドロック ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  • READ COMMITTED 以下では,毎回最新のスナップショットを見る。
  • REPEATABLE READ 以上では,レコードロックで担保できない,現在レコードが存在しない範囲のロックに関しては, ギャップロック で解決している。両者とも 悲観的ロック である。
  • REPEATABLE READ 以上では,SELECT 実行で獲得したスナップショットバージョン をトランザクションの終了まで保持し続ける。同じクエリに対しては同じ結果が保証される。
    • BEGIN の代わりに START TRANSACTION WITH CONSISTENT SNAPSHOT を使用すると,トランザクション開始と同時にスナップショットを獲得する。 これは Postgres で BEGIN を使った場合と同じタイミングとなる。
  • SERIALIZABLE では, Consistent Read はすべて Locking Read に変換されるため,実質的に存在しない。

https://softwarenote.info/p1067/

https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html

Postgres

Anomaly

READ COMMITTED REPEATABLE READ SERIALIZABLE
Dirty Read
Fuzzy Read
Phantom Read
Read Skew
Cursor Lost Update
Lost Update
Concurrent Update
Error Detection

Concurrent Update
Error Detection
Write Skew
R/W
Dependencies
Error Detection
Observe Skew

Locking

分離レベル\アクション Consistent Read Locking Read/Write
READ COMMITTED
[Default]
- レコードロック
REPEATABLE READ - レコードロック
更新競合検査
SERIALIZABLE SIRead ロック レコードロック
更新競合検査
SIRead ロック

Errors

原因 メッセージ例
デッドロック ERROR: deadlock detected
DETAIL: Process 37542 waits for ShareLock on transaction 2995; blocked by process 37541.
Process 37541 waits for ShareLock on transaction 2996; blocked by process 37542.
更新競合 ERROR: could not serialize access due to concurrent update
直列化異常 ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
  • READ COMMITTED では,毎回最新のスナップショットを見る。
  • REPEATABLE READ 以上では,トランザクション開始時点で 参照するスナップショットバージョンが確定される。
  • REPEATABLE READ 以上で更新競合があったとき, MySQL は後勝ちで上書きする一方, Postgres はエラーとして後続トランザクションをアボートする。表面的な動きだけに着目するのであれば, 「トランザクション開始時から全テーブル・全レコードに対して楽観ロックがかけられている」 と考えても差し支えない。
  • SERIALIZABLE では, SIRead ロックの仕組みにより,直列化異常も発生しない。
    • SIRead ロックを取り入れた Snapshot Isolation (SI) は, SSI (Serializable Snapshot Isolation) と呼ばれる。

https://www.postgresql.jp/docs/12/transaction-iso.html

だいたい話まとまってきたから記事に書き起こすか

Postgres の SERIALIZABLE の場合のリードオンリー最適化についても書く

このスクラップは2ヶ月前にクローズされました
ログインするとコメントできます