3.3SQLとトランザクション（SELECT/JOIN/副問合せ/GROUP BY・ビュー/インデックス・ACID・同時実行制御・障害回復・分散DB/NoSQL）

本節はテクノロジ系の中でも出題数が多い領域です。基本情報技術者ではSQLの基本構文とトランザクションの用語理解が中心でしたが、応用情報技術者では副問合せを含む複雑なSQLの読解、2相ロックによるスケジュールの直列可能性の判断、隔離性水準ごとに発生し得る異常現象の区別まで踏み込みます。「ダーティリード」「非再現読取り」「ファントムリード」という3つの異常現象を、どの隔離性水準で防げるかとセットで理解することが得点の鍵です。

3.3.1SQLとビュー・インデックス

• SELECT／JOIN（内部結合・外部結合）／副問合せ（サブクエリ）＝1つのSQL文の中に別のSELECT文を入れ子にする手法（例：WHERE dept_id IN (SELECT id FROM depts WHERE region = 'east')のように条件の絞り込みに使う）。GROUP BY＝集約関数（count/sum/avg等）と組み合わせて集計する。集約後の結果をさらに条件で絞り込むにはWHEREではなくHAVINGを使う。
• ビュー＝実データを持たず、SELECT文を保存した仮想的な表。頻繁に使う結合・集計を簡潔に参照でき、元表の列を絞って見せることでアクセス制御にも使える（元表が更新されればビューの結果も追随する）。インデックス＝特定の列に対する検索を高速化する索引構造（多くはB木）。検索は速くなるが、更新（INSERT/UPDATE/DELETE）のたびに索引も更新するためオーバーヘッドが増えるトレードオフがある。

3.3.2トランザクションと同時実行制御・隔離性水準

• ACID特性＝原子性（全実行/全未実行）・一貫性（整合性維持）・独立性（他Txから干渉されない）・耐久性（完了結果は障害後も残る）。2相ロック（2PL）＝トランザクション実行中をロック獲得のみの拡張フェーズとロック解放のみの縮小フェーズに分け、一度解放したら新規獲得しないルール。2PLに従うスケジュールは直列可能性（複数Txを1つずつ順に実行したのと同じ結果になる性質）が保証される。
• 隔離性水準は独立性の強さの段階で、緩めるほど並行性は上がるが異常現象のリスクが増える。ダーティリード＝他Txが未コミットの更新を読んでしまう異常（最も緩い水準で発生し得る）。非再現読取り（反復不能読取り）＝同一Tx内で同じ行を2回読むと他Txのコミットにより値が変わっている異常。ファントムリード＝同一条件で2回検索すると他Txの挿入により行数が変わっている異常。水準を上げるほど防げる異常は増えるが並行性（スループット）は下がるトレードオフがある。
• 障害回復はログ（更新前ログ・更新後ログ）で行う。ロールバック＝異常終了Txの更新を更新前ログで巻き戻す（Tx障害時）。ロールフォワード＝バックアップ以降の更新後ログを再適用する（ディスク障害等でのバックアップ復旧時）。チェックポイントを定期的に取得すると、障害時に遡るログの範囲を絞れ回復時間を短縮できる。
• 分散DB＝データを複数の拠点（サーバ）に分散配置するDB。レプリケーション＝同じデータを複数拠点に複製し、可用性向上や読み取り負荷分散に使う（更新の反映タイミングにより同期/非同期の方式がある）。NoSQL＝関係モデルに縛られないDB（キーバリュー型・ドキュメント型・グラフ型等）で、大量データの水平分散や柔軟なスキーマが必要な場面で選ばれる。

在庫管理システムで「発注残がある商品のうち、直近30日で1件も出荷実績のないものを一覧表示する」という要件を考えます。これはSELECT p.id, p.name FROM products p WHERE p.backorder_qty > 0 AND p.id NOT IN (SELECT s.product_id FROM shipments s WHERE s.shipped_at >= CURRENT_DATE - 30)のように副問合せで「直近30日に出荷された商品ID」を取得し、外側のSELECTで除外条件として使うのが自然な組み立てです。この一覧を営業部門にも見せたいが在庫の原価情報は隠したい場合、原価列を除いたSELECT文をビューとして定義して提供すれば、元表への直接アクセスを許可せずに済みます。次に、この在庫更新処理を2人の担当者が同時に行う場面を考えます。担当者Aが商品Xの在庫行を更新中（ロック獲得済み・未コミット）に、担当者Bが同じ行を読み取ってしまうとダーティリードが発生し、Aの更新が後で取り消された場合Bは存在しない値を見てしまいます。これを防ぐにはコミット前のデータを他Txに読ませない隔離性水準（多くのDBMSの既定である読み取りコミット済み以上）が必要です。さらに、在庫チェックから発注確定までの間に他の担当者が同じ条件で新しい注文行を挿入すると、最初に数えた件数と後で数えた件数が変わるファントムリードが起こり得るため、集計結果を厳密に固定したい業務（例えば在庫引当の一括処理）では直列化可能（Serializable）という最も厳しい隔離性水準を選び、並行性を犠牲にしてでも正確性を優先します。この在庫システムをデータセンター障害に備えて別リージョンへレプリケーションする場合、常に最新状態を保証したいなら同期レプリケーションを選びますが、書き込みのたびに遠隔地への反映を待つため書き込み性能が低下するトレードオフがあり、多少の反映遅延を許容できるなら非同期レプリケーションでスループットを優先する、という判断が実務では行われます。

3.3.3この節のまとめ

• 副問合せは内側のSELECT結果を外側の条件に使う。ビュー=保存されたSELECT定義（実データなし）・インデックス=検索高速化だが更新オーバーヘッド増
• 2相ロックで直列可能性を保証。隔離性水準を上げるほどダーティリード→非再現読取り→ファントムリードの順で防げるが並行性は低下
• ロールバック=更新前ログ・ロールフォワード=更新後ログ。分散DBのレプリケーションは同期(整合性優先)/非同期(性能優先)で選択