ACID特性の隔離性で安全なトランザクション処理をしよう
前回の勉強内容
勉強のきっかけになった問題
トランザクションの隔離性水準のうち，次の(1)，(2)に該当するSQLの指定はどれか。
(1)対象の表のダーティリードは回避できる。
(2)一つのトランザクション中で，対象の表のある行を2回以上参照する場合，1回目の読込みの列値と2回目以降の読込みの列値が同じであることが保証されない。
- READ COMMITTED
- READ UNCOMMITTED
- REPEATABLE READ
- SERIALIZABLE
出典 : データベーススペシャリスト試験 令和3年秋期 午前Ⅱ 問7
「ACID特性」とは、トランザクション処理で守らないといけない4つの性質のことです。
トランザクションが，データベースに対する更新処理を完全に行うか，全く処理しなかったのように取り消すか，のどちらかの結果になることを保証する特性はどれか。
- 一貫性(consistency)
- 原子性(atomicity)
- 耐久性(durability)
- 独立性(isolation)
トランザクション処理に求められる特性の頭文字をとって「ACID特性」といいます。
| 特性 | 日本語 | 説明 |
|---|---|---|
| Atomicity | 原子性、不可分性 | トランザクションの処理が「すべて実行される」か「全く実行されない」のどちらかの結果になる |
| Consistency | 一貫性、整合性 | テーブルの定義を満たすようにトランザクションが実行されることで、データの破損やエラーが起きた場合でもデータの整合性が保たれる |
| Isolation | 独立性、隔離性 | 複数のトランザクションが同時進行しても干渉したり、他の処理の影響を受けない |
| Durability | 耐久性、永続性 | 完了したトランザクションの結果は記録されることで、システム障害が発生しても失われない |
隔離性が満たされないと発生する残念な事象
「ACID特性」にある「隔離性」は、「他の処理に影響を与えない」という特性です。
| 事象 | 他の言い方 | 意味 |
|---|---|---|
| ダーティリード | 未コミット読み取り、非コミット読み取り | 処理中の未確定データを他のトランザクションが読み込んでしまうこと |
| アンリピータブルリード | 反復不能読み取り、ファジーリード | 同じデータを複数回読み込んだ場合、他のトランザクションが内容を更新・削除したことにより途中で内容が変わること |
| ファントムリード | 幻像読み取り、仮読み取り | 同じデータを複数回読み込んだ場合、他のトランザクションが内容を追加したことにより途中でレコードが増えること |
試験問題で具体的な例を考えます。
データベースのトランザクションT2の振る舞いのうち，ダーティリード(dirty read)に関する記述はどれか。
- トランザクションT1が行を検索し，トランザクションT2がその行を更新する。その後T1は先に読んだ行を更新する。その後にT2が同じ行を読んでも，先のT2による更新が反映されない値を得ることになる。
- トランザクションT1が行を更新し，トランザクションT2がその行を検索する。その後T1がロールバックされると，T2はその行に存在しない値を読んだことになる。
- トランザクションT2がある条件を満たす行を検索しているときに，トランザクションT1がT2の検索条件を満たす行を挿入する。その後T2が同じ条件でもう一度検索を実行すると，前回は存在しなかった行を読むことになる。
- トランザクションT2が行を検索し，トランザクションT1がその行を更新する。その後T2が同じ行を検索した場合，同じ行を読んだにもかかわらず，異なる値を得ることになる。
出典 : データベーススペシャリスト試験 平成26年春期 午前Ⅱ問14
「ダーティリード」は「未確定データを他のトランザクションが読み込んでしまうこと」なので以下の流れになる「2.」が正解です。
- トランザクションT1が行を更新し，
- -> 未確定のデータができる
- トランザクションT2がその行を検索する。
- -> 未確定データを他のトランザクションが読み込む
- その後T1がロールバックされると，T2はその行に存在しない値を読んだことになる。
隔離性水準とは、トランザクションの独立性の指標のことです。
「隔離性が満たされないと発生する残念な事象」を防ぐための「他のトランザクションへ影響を与えない度合い」が、隔離性水準です。
SQLでトランザクションの隔離性水準を READ COMMITTED に指定したときに発生する状態はどれか。
- ダーティリードとアンリピータブルリードとファントムリードが発生する。
- ダーティリードとアンリピータブルリードは発生しないが，ファントムリードが発生する。
- ダーティリードは発生しないが，アンリピータブルリードとファントムリードが発生する。
- ダーティリードもアンリピータブルリードもファントムリードも発生しない。
出典 : データベーススペシャリスト試験 平成24年春期 午前Ⅱ問15
| 隔離性水準 | 参照時 のロック |
更新時 のロック |
ダーティリード の発生 |
アンリピータブル リード の発生 |
ファントムリード の発生 |
|---|---|---|---|---|---|
| READ UNCOMMITTED | なし | 更新時に 占有ロック |
o | o | o |
| READ COMMITTED | なし | トランザクション開始時 に占有ロック |
x | o | o |
| REPEATABLE READ | 参照時に 共有ロック |
トランザクション開始時 に占有ロック |
x | x | o |
| SERIALIZABLE | トランザクション開始時 に占有ロック |
トランザクション開始時 に占有ロック |
x | x | x |
いろんなところの隔離性水準
「SERIALIZABLEにすれば何の問題もない！」ように見えますが、PostgreSQLでは「READ COMMITTED」、MySQLでは「REPEATABLE READ」がデフォルトになっています。
PostgreSQLではリードコミッティドがデフォルトの分離レベルです。 トランザクションがこの分離レベルを使用すると、SELECT問い合わせ（FOR UPDATE/SHARE句を伴わない）はその問い合わせが実行される直前までにコミットされたデータのみを参照し、まだコミットされていないデータや、その問い合わせの実行中に別の同時実行トランザクションがコミットした更新は参照しません。
13.2. トランザクションの分離
- REPEATABLE READ
これが InnoDB のデフォルトの分離レベルです。 同じトランザクション内の Consistent reads は、最初の読取りによって確立された snapshot を読み取ります。 つまり、同じトランザクション内で複数のプレーン (非ロック) SELECT ステートメントを発行すると、これらの SELECT ステートメントも互いに一貫性が保たれます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.7.2.1 トランザクション分離レベル
PythonのフレームワークであるDjangoでは、MySQLを使う場合に「READ COMMITTED」が使われるようになっていました。
引用元 : https://github.com/django/django/blob/8aa83464664938367a425f011a7df5663a955d09/django/db/backends/mysql/base.py#L237C15-L237C15
# django.db.backends.mysql.base.DatabaseWrapper#get_connection_params
# ↓指定がなければ「READ COMMITTED」になる
isolation_level = options.pop("isolation_level", "read committed")
if isolation_level:
isolation_level = isolation_level.lower()なぜ、DjangoはMySQLのデフォルトである「REPEATABLE READ」を使わないのでしょう？答えはDjangoのドキュメントサイトにありました。
頑張って訳すと「REPEATABLE READでデータ損失が発生する可能性があり、データベースのリレーショナル整合性で問題があっても検知できないかもしれないから」みたいなことが理由のようです。
Data loss is possible with repeatable read. In particular, you may see cases where get_or_create() will raise an IntegrityError but the object won’t appear in a subsequent get() call.
Databases | Django documentation | Django
隔離性水準は、バランスが大切です。
隔離性水準を高めれば、「不確実なデータは減る」けれど「ロック待ちなどで実行できる処理も減る」のです。
トランザクションの隔離性水準を高めたとき，不整合なデータを読み込むトランザクション数と，単位時間に処理できるトランザクション数の傾向として，適切な組合せはどれか。
選択肢 不整合なトランザクションを読み込む
トランザクション数単位時間に処理できる
トランザクション数ア 増える 増える イ 増える 減る ウ 減る 増える エ 減る 減る 出典 : データベーススペシャリスト試験 平成30年春期 午前Ⅱ問17
なので、「隔離性水準」と「パフォーマンス」はバランスよく設定できるようになるといいですね。
経験豊富なユーザーは、実際にトランザクションが互いに干渉しないと確信できれば、パフォーマンスと並列性の向上の代わりに保護の低下をトレードオフするように分離レベルを調整できます。
MySQL :: MySQL 8.0 リファレンスマニュアル :: MySQL 用語集
最後に「勉強のきっかけになった問題」を考えます。
「対象の表のある行を2回以上参照する場合，1回目の読込みの列値と2回目以降の読込みの列値が同じであることが保証されない」ということは「アンリピータブルリードが発生する」ということになります。
なので「ダーティリードは発生しない」かつ「アンリピータブルリードが発生する」ということなので答えは「READ COMMITTED」になります。
