【SQL入門】トランザクションの分離
はじめに
『スッキリわかるSQL入門 第2版』を読んで、問題に取り組みつつ、メモとしてまとめました。
なお、一部SQL文の例などを引用させていただいています。
トランザクションの分離
分離性(isolation)
複数のトランザクションが同時に実行された場合でも、お互いに影響を与えず、あたかも1つずつ順番に実行されたかのように見えるという性質です。これにより、データの整合性が保たれます。
分離性とは、まさに一つのトランザクションを独立したブロックとして扱い、他のトランザクションからの干渉を防ぐ性質のことを指します。
同時実行の副作用
世の中で利用されているサービスは、多くの利用者から1つのDBMSに対してたくさんのSQL文が送られます。
DBMSはそれらのリクエストを同時に処理しようとするため、同じ行を複数の利用者が同時に読み書きする可能性があります。このような状態が発生すると、副作用が発生し、正しい処理が行われない可能性があります。
分離性がないと起こる問題
更新の消失（Lost Update）
2つのトランザクションが同じデータを同時に読み込み、それぞれ異なる変更を加えてコミットした場合、先にコミットした変更が後からコミットした変更によって上書きされ、失われてしまいます。
ダーティリード（Dirty Read）
あるトランザクションが、まだコミットされていない別のトランザクションの変更を読み込んでしまうことです。
その後、変更元のトランザクションがロールバックされると、読み込んだデータは存在しない変更となり、不整合が発生します。
ノンリピータブルリード（Non-repeatable Read）
あるトランザクションが同じデータを複数回読み込んだ際、その間に別のトランザクションがデータを変更・コミットしたため、読み込むたびに異なる値を取得してしまいます。
3つの代表的な副作用
副作用1 ダーティーリード(dirty read)
あるトランザクションが更新されている最中に、他のトランザクションからデータを読み出すことができてしまう状況です。これは、READ UNCOMMITTEDの分離レベルでのみ発生します。以下の例では、トランザクションBの内容をCOMMITされる前に途中で読みこんでしまっています。結果的に、ロールバックされたにもかかわらずトランザクションA、誤ったデータを取得しています。
※ https://qiita.com/WebEngrChild/items/e85d0762c1383e353349 より引用
まだコミットされていない未確定の変更を他の人が読めてしまう副作用のこと
その後キャンセルされてしまう可能性のある未確定の情報を元にして別の処理を行なうため、非常に危険な副作用
副作用2 反復不能読み取り(non-repeatable read)
同一トランザクション内で同じデータを２回以上読み取ると、その間に他のトランザクションがデータを更新しコミットした結果、異なる値が返される現象を指します。これは、READ COMMITTEDの分離レベルで可能性があります。以下の例では、トランザクションAの中で２回に分けて商品Aの在庫数を取得していますが、結果が異なっています。
※ https://qiita.com/WebEngrChild/items/e85d0762c1383e353349 より引用
あるテーブルに対してSELECT文を実行した後、他の人がUPDATE文でデータを書き換えると、次回SELECTした際に検索結果が異なってしまうという副作用のこと
テーブルの内容を複数回読み取る際、その間にデータの内容が変化してしまっては困るケースがある。
副作用3 ファントムリード(phantom read)
同一トランザクション内で同じデータ範囲を二回以上クエリすると、その間に他のトランザクションが新たなデータを追加・コミットした結果、クエリ結果が異なる現象を指します。これは、一般的にREPEATABLE READの分離レベルで発生する可能性があります。ただし、MySQLのInnoDBでは、この分離レベルでも後述するネクストキーロックという機能を使うことで防止することができます。
※ https://qiita.com/WebEngrChild/items/e85d0762c1383e353349 より引用
2回のSELECT文の間に、他の人がINSERT文で行を追加すると、2回のSELECT文で結果行数が変わってしまう副作用のこと
1回目の検索結果の行数に依存するような処理を行なう場合に問題となることがある。
◎DBMSによるトランザクション制御(3)
DBMSは、あるトランザクションを実行する際、他のトランザクションから影響を受けないよう、分離して実行する。
→ 仮に他のトランザクションと同時に実行していたとしても、あたかも単独で実行しているのと同じ結果になるように制御する。
ロック(lock)
複数のユーザーやプロセスが同じデータベースのデータに同時にアクセスする際に、データの整合性を保つために、一時的にそのデータへのアクセスを制限する仕組みです。
あるトランザクションが現在読み書きしている行に鍵をかけ、他の人のトランザクションからは読み書きできないようにします。
「ロックを取る」「ロックを取得する」
あるトランザクションが特定の行などをロックすること表す表現
自分のトランザクションがコミットまたはロールバックで終了すると、かけた鍵は解除され、他の人のトランザクションがその行を読み書きできるようになります。
→ 自分が読み書きしたい行を他の人がロックしている場合、その人のトランザクションが完了するまで自分は待ち続けることになります。
※このロックの1つ1つの待ち時間は短いが、ロックがたくさん発生すると、データベースの動作は非常に遅くなってしまう点には注意が必要
ロックの一般的な種類
共有ロック（Shared Lock）
データの読み取りを目的としたロックです。複数のトランザクションが同時に共有ロックをかけることができます。
しかし、共有ロックがかかっている間は、他のトランザクションがそのデータを変更するための排他ロックをかけることはできません。
排他ロック（Exclusive Lock）
データの変更（挿入、更新、削除）を目的としたロックです。一度に1つのトランザクションしか排他ロックをかけることができません。排他ロックがかかっている間は、他のトランザクションは読み取りも変更もできません。
ロックが多発する主な理由(パフォーマンス低下の理由)
ロックの競合（Lock Contention）
複数のトランザクションが、同じデータに対する排他ロックを要求すると、先にロックを取得したトランザクションが完了するまで、他のトランザクションは待ち状態に入ります。この待ち時間が増えると、全体の処理効率が落ちます。
デッドロック（Deadlock）
複数のトランザクションが互いに相手がロックしているリソースを待つ状態になり、永遠に処理が進まなくなる状況です。DBMSはデッドロックを検知すると、どちらか一方のトランザクションを強制的に終了（ロールバック）させますが、これによりトランザクションが無駄になり、リソースの再取得が必要になります。
データベースの世界においては、一つのトランザクションで複数のデータにロックをかける際に、そのロックをかける順番によっては、二つのトランザクションでお互いのロックの解放（アンロック）を待つ状態になってしまう、いわゆるお見合い状態になり、後続の処理が動かないことになってしまうことがあります。
※ https://itmanabi.com/exclusive-lock/ より引用
並列性の低下
ロックは基本的に、複数の処理を直列化させる効果があります。これにより、本来同時に実行できたはずの処理が、ロックのせいで順番待ちになり、システムの並列性が失われます。
これらの問題を避けるためには、トランザクションの範囲をできるだけ小さくしたり、適切な分離レベルを選択したりすることが重要になります。
分離レベル
トランザクションを使うメリット
ロックの仕組みが有効になり、副作用は発生しないようになる
トランザクションを使うデメリット
DBMSのパフォーマンスが損なわれる
正確なデータ操作とパフォーマンスは二律背反の関係にあるが、ある程度の両立は可能
トランザクション分離レベル(transaction isolation level)
多くのDBMSで、どの程度厳密にトランザクションを分離するかを指定することができます。
トランザクション分離レベルは、複数のトランザクションが同時に実行される際に、お互いの処理結果がどの程度見えてしまうか（分離度）を定義するものです。
分離レベルは、ACID特性のうちのI（Isolation：分離性）を制御します。
分離レベルと処理効率はシーソーの関係にあります。
分離レベルを低くすると、並列処理の効率が上がりパフォーマンスは向上しますが、データの不整合が起こるリスクが高まります。
分離レベルを高くすると、データの安全性は増しますが、ロックなどによって処理の待ち時間が発生し、パフォーマンスは低下します。
※ https://qiita.com/dodonki1223/items/69350c46602f51ca56fd より引用
デフォルトの分離レベル以外の任意の分離レベルを SET TRANSACTION ISOLATION LEVEL 命令を使用して選択できます。
4つの主要な分離レベルと特徴
1. READ UNCOMMITTED (最も低い分離レベル)
他のトランザクションがまだコミットしていない（確定していない）変更を読み取ることができます。
特徴
ロックがほとんどかからないため、パフォーマンスは非常に高いです。
リスク
ダーティリード（Dirty Read）という問題が発生します。これは、他のトランザクションが更新したが、まだコミットされていないデータを読み取ってしまう現象です。そのトランザクションが後でロールバックされた場合、存在しないデータを読んだことになります。
2. READ COMMITTED
他のトランザクションがコミットした（確定した）変更のみを読み取ることができます。
特徴
一般的なデータベースのデフォルト設定であり、バランスの取れたレベルとされています。
リスク
ノンリピータブルリード（Non-repeatable Read）という問題が発生します。同じトランザクション内で同じ行を2回読み取ったときに、2回目の読み取りで別のトランザクションがコミットした変更（更新や削除）が反映され、異なる結果になる現象です。
3. REPEATABLE READ
トランザクションが開始してから終了するまで、同じ行を何度読み込んでも同じ値が保証されます。
特徴
同じ行を複数回読み取っても結果が一貫するため、ノンリピータブルリードを防ぎます。
リスク
ファントムリード（Phantom Read）という問題が発生します。これは、同じトランザクション内で条件に一致する行を2回検索したとき、2回目の検索で別のトランザクションがコミットした新しい行（挿入）が反映され、行数が増えてしまう現象です。
4. SERIALIZABLE (最も高い分離レベル)
複数のトランザクションが同時に実行されたとしても、それらが一つずつ順番に実行されたかのように振る舞います。
特徴
最も安全性が高いレベルで、上記すべての問題を防ぎます。
リスク
多くのロックがかかるため、パフォーマンスが最も低くなります。
並列性が失われ、アプリケーションのスケーラビリティが損なわれる可能性があります。
分離レベルの選択
トランザクション分離レベルは、システムの要件に応じて慎重に選択する必要があります。
-
データの正確性が最優先の場合（例：金融取引）
SERIALIZABLEやREPEATABLE READを検討します -
読み取りが主でパフォーマンスが重要な場合
READ COMMITTEDやREAD UNCOMMITTEDで十分な場合があります
SET TRANSACTION ISOLATION LEVEL 分離レベル名
SET CURRENT ISOLATION 分離レベル名
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
分離レベルで性能と安全性のバランスを選択できる
並列実行制御（ MVCC: multi-version concurrency control ）
複数のトランザクションが同時に同じデータにアクセスしても、互いに干渉せず、ロックを使わずにデータの整合性を保つための仕組み
MVCCの主な考え方は、データの変更履歴を複数世代（バージョン）として保持することです。
つまり、あるデータについて「未確定の書き換え済み」と「書き換え前」の2つのバージョンを併存させます。
読み取りの隔離
読み取り専用のトランザクションは、データが変更される前の古いバージョンを参照します。これにより、書き込み中のトランザクションがロックをかけていても、読み取りはブロックされません。
書き込みの非ブロッキング
書き込みを行なうトランザクションは、データの新しいバージョンを作成します。これにより、他のトランザクションが古いバージョンを読み取っている間も、書き込みを待つ必要がなくなります。
参考文献
『スッキリわかるSQL入門 第2版』
Discussion