【基本情報対策】SQLとトランザクション

Transcript

1. 2026年10月 SQLとトランザクション 基本情報技術者試験 対策 #10 赤神青空
2. ▪主キー以外の項目で決まる項目を、別のテーブルに分ける 前回のクイズの答えは「C」 前回のクイズ 第2正規形のテーブルを第3正規形にするとき、 取り除くのは？ 主キー以外の項⽬によって決まる項⽬があると、 推移関数従属になる。その項⽬を 別のテーブルに分けると、第3正規形になる 答えは C 今ココ おさらい A 繰り返し項⽬ 第1正規形にするときに取り除くもの B 部分関数従属 第2正規形にするときに取り除くもの C 推移関数従属 主キー以外の項⽬で決まる項⽬を、別のテーブルへ D 外部キー 分けたテーブルどうしをつなぐもの。取り除かない 2/17
3. ▪SQL・トランザクションと、実務での範囲の決め方 今回の話 ① SQL SELECT‧JOIN‧GROUP BY ② トランザクション ACID特性‧ロック‧障害回復 ③ 実務では 振替‧ORM‧ロックの順番 今回いちばん持ち帰ってほしいこと トランザクションは、全部反映するか、全部なかったことにするかのどちらか 今ココ はじめに 3/17
4. ▪列と行を選んで、表から取り出す SELECT と WHERE 社員 ⽒名 ⻘⽊ 井上 上⽥ 江藤 ⼩野 部署コード D D D D D 給与（万円） SELECT ⽒名, 給与 FROM 社員 WHERE 部署コード = 'D ' 結果 ⽒名 ⻘⽊ 上⽥ 列を選ぶ（射影） ⾏を選ぶ（選択） 給与（万円） 部署コードが D の⾏の、⽒名と給与の列だけ 今ココ SQL 4/17
5. ▪2つの表を、同じ値の列でつなぐ JOIN（結合） 社員 ⽒名 ⻘⽊ 井上 上⽥ 江藤 ⼩野 部署コード D D D D D 部署 部署コード D D D 部署コードが同じ⾏どうしを、横につなぐ （どちらの表にもある部署コードの列で、つなぐ） 今ココ SQL 部署名 営業 開発 総務 SELECT ⽒名, 部署名 FROM 社員 JOIN 部署 ON 社員.部署コード = 部署.部署コード 結果 ⽒名 ⻘⽊ 井上 上⽥ 江藤 ⼩野 部署名 営業 開発 営業 総務 開発 5/17
6. ▪グループにまとめて、集計してから絞る GROUP BY と HAVING 社員 ⽒名 ⻘⽊ 上⽥ 部署コード D D 井上 ⼩野 D D 江藤 D 給与（万円） SELECT 部署コード, AVG(給与) FROM 社員 GROUP BY 部署コード HAVING AVG(給与) >= 部署コード D D D 平均の給与 部署コードごとにまとめる まとめたあとで絞る HAVING 部署コード D D 平均の給与 平均が 25 未満の D が外れる WHERE はまとめる前の1⾏ずつ、HAVING はまとめたあとのグループに付ける条件 今ココ SQL 6/17
7. ▪成功したら反映、失敗したら取り消す コミットとロールバック 1つのトランザクション ① A の⼝座から 1万円を引き出す 成功したら：コミット（COMMIT） A 万円 ∕ B 万円 2つとも反映する ② B の⼝座へ 1万円を⼊⾦する はじめは A 万円 ∕ B 万円 失敗したら：ロールバック（ROLLBACK） ① だけ反映されると、 1万円がどこにもなくなる 2つとも、なかったことにする 今ココ トランザクション A 万円 ∕ B 万円 7/17
8. ▪トランザクションに求められる4つの性質 ACID特性 原⼦性 Atomicity ⼀貫性 Consistency 振替の ① だけ反映、ということは起きない 例：振替の前後で、残⾼の合計は 8万円のまま 全部実⾏するか、全部取り消すか 決まりに合った状態を保つ 独⽴性 Isolation 耐久性 Durability このあとの排他制御（ロック）の話 このあとの障害回復（ログ）の話 同時に動いても、互いに影響しない 今ココ トランザクション コミットした結果は、障害でも失われない 8/17
9. ▪同時に更新すると、片方の更新が消えることがある 排他制御 ロックなし T ： 万円⼊⾦ はじめの残⾼ 10万円 T ： 万円引き出し 10 を読む 10＋1＝11 を書く T ： 万円⼊⾦ T ： 万円引き出し 11 を書いてコミット ロック待ち 専有ロックで 11 を読む 10−3＝7 を書く 残⾼ 7万円（⼊⾦の 1万円が消えた） 今ココ 排他制御 はじめの残⾼ 10万円 専有ロックで 10 を読む 10 を読む 1〜4 は時間の順 ロックあり 11−3＝8 を書く 残⾼ 8万円（10＋1−3） 9/17
10. ▪読むときのロックと、更新するときのロック 共有ロックと専有ロック 共有ロック データを読むときにかける ほかのトランザクションも、共有ロックをかけて読める 更新したい側（専有ロック）は待つ 専有ロック データを更新するときにかける ほかのトランザクションは、 読むのも更新するのも待つ 今ココ 排他制御 ⾏：かかっているロック 列：かけたいロック 共有ロック 専有ロック 共有ロック ◦ かけられる × 待つ 専有ロック × 待つ × 待つ 同時にかけられるのは、共有ロックどうしだけ 読むだけどうしなら、待たずに進める 10/17
11. ▪互いに相手のロックを待って、進めなくなる デッドロック 実線：ロック中 点線：ロックを待っている ⼝座 A ロック中 待つ T A → B の順に取る T B → A の順に取る 待つ ⼝座 B ロック中 T と T が、互いに相⼿のロックを待ち続ける 今ココ 排他制御 11/17
12. ▪コミット済みは反映し直し、コミット前は取り消す 障害回復 ログ（ジャーナルファイル）に、更新前と更新後の値を記録しておく コミット T 何もしない T ロールフォワード T ロールフォワード T ロールバック チェックポイント ロールフォワード（フォワードリカバリ） コミット済み → ログの更新後の値で、反映し直す 今ココ 障害回復 障害 ロールバック（バックワードリカバリ） コミット前 → ログの更新前の値に戻す 12/17
13. ▪どこからどこまでを1つにするかは、コードで書く ORM でも、範囲は自分で決める SQL で書くと ORM で書くと（書き⽅の⼀例） BEGIN; UPDATE ⼝座 SET 残⾼ = 残⾼ - 10000 WHERE ⼝座番号 = 'A'; UPDATE ⼝座 SET 残⾼ = 残⾼ + 10000 WHERE ⼝座番号 = 'B'; COMMIT; -- 失敗したら ROLLBACK; await db.transaction(async (tx) => { await tx.account.decrement('A', ) await tx.account.increment('B', ) }) BEGIN を書かないと、1⽂ごとにコミットされる 囲んだ中で例外が出ると、まとめてロールバック （DB の既定。⾃動で BEGIN するドライバーもある） 今ココ 実務では （多くの ORM の動き。書き⽅は ORM によって違う） 13/17
14. ▪独立性をどこまで厳しくするかを選べる 分離レベル ゆるい READ UNCOMMITTED 厳しい READ COMMITTED REPEATABLE READ PostgreSQL の既定 MySQL（InnoDB）の既定 SERIALIZABLE SQL の標準で決められた4つの分離レベル 右ほど、同時に動くほかのトランザクションの影響を受けにくい そのぶん、待ちややり直しが増えやすい 既定は DB によって違うので、使っている DB で確かめておきたい 今ココ 実務では 14/17
15. ▪ロックを取る順番を、そろえる デッドロックを避ける どちらのトランザクションも、A → B の順番でロックを取る 時間の順 T A をロック B をロック コミットして外す T A を待つ A を待つ A を待つ A をロック B をロック 順番がそろっていれば、待つことはあっても、互いに待つ輪にはならない 振替なら、例えば「⼝座番号の⼩さい順にロックする」と決めておく PostgreSQL のドキュメントも、ロックを⼀貫した順番で取るのが⼀番の防ぎ⽅としている 今ココ 実務では 15/17
16. ▪全部反映するか、全部なかったことにするか まとめ SQL は、⾏を選び、表をつなぎ、グループにまとめる WHERE はまとめる前の⾏に、HAVING はまとめたあとのグループに付ける条件 トランザクションは、全部反映か、全部取り消しか コミットで反映、ロールバックで取り消し。障害のあとはログを使って直す 範囲とロックの順番は、⾃分で決める ORM でも範囲はコードで書く。ロックは同じ順番で取る 今ココ まとめ SQL トランザクション 実務 16/17
17. ▪一度止めて考えてみてください。答え合わせは次回の冒頭で 今回のクイズ トランザクションの中の処理を、全部実行するか、全部取り消すかのどちらかに する性質は？ A 原子性 今ココ まとめ B 一貫性 C 独立性 D 耐久性 17/17