＜出題要旨＞
【設問1(1)(2)】UNIQUE制約
【設問1(3)】外部キー
【設問1(4)】検査制約
【設問2】SQL
【設問3】デッドロック
設問1
設問1（1）
設問1（1）は表2の穴埋め（案1の”商品”テーブルに定義した制約の内、UNIQUE制約を定義するもの）とUNIQUE製薬を定義する目的を答える問題。
図2の「案1の”商品”テーブルの構造」、表1「主な列の意味」を確認し、UNIQUE制約が必要かどうかを判断する。
UNIQUE制約とは、1つのテーブル内でキー列の一意性を保証する制約である。よって、本文中から「キー列を一意とする」という表現がないか、探していく。
〔受注管理システムの要求仕様〕の「2．注文（2）」を読み進めると、「商品一覧に表示される順番は、商品全体で重複がないように、商品企画担当者が決めた表示順に基づく」と記載がある。この「重複がないように」が「一意性」を示しており、列名”表示順”に、UNIQUE制約が必要であることがわかる。
したがって、対象の列名は『表示順』で、目的としては、『商品全体で重複がないように商品の表示順を決めるため』や『商品の表示順を商品全体で一意にするため』となる。
設問1（2）
設問1（2）は、表3「案2の”単品商品”テーブル及び”セット商品”テーブルに定義した制約」について、設問1（1）で回答した列名に対して、ユニーク索引を作成しただけでは要求仕様を満たせない。その理由を答える問題。
これは、本文中に「ユニーク索引を複数のテーブルにまたがって作成することはできない」とある。
案2の
図3「案2の”単品商品”テーブル及び”セット商品”テーブルの構造」から、案2は”単品商品”テーブル及び”セット商品”テーブルを分ける構造とする案である。
したがって、回答は、『ユニーク索引は、一つのテーブル内で、キー列の一意性を保証するものだから』や『ユニーク索引を複数のテーブルに跨って定義することはできないから』となる。
設問1（3）
設問1（3）は、外部キー設定時の不都合に関する問題である。
【ア】は、”セット商品構成”テーブルの”単品商品番号”列に外部キーを設定した時の、案1の不都合。
【イ】は、”在庫”テーブルの”商品番号”列に外部キーを設定した時の、案2の不都合。
【ア】については、項番1を参考にすれば回答できる。項番1の不都合は「案1の場合、セット商品番号列に単品商品番号を設定できてしまう」というものである。同様な事象が【ア】についても起こる。よって、『案1の場合、単品商品番号列にセット商品番号を設定できてしまう』となる。
【イ】について、”在庫”テーブルの構造は図3に記載がある。これを見ると、”在庫”テーブルの”商品番号”列は主キーとなっている。
案2では、”単品商品”テーブル及び”セット商品”テーブルを分ける構造としており、”単品商品”テーブルの主キー；商品番号、”セット商品”テーブルの主キー；商品番号となっている。つまり、”商品番号”を外部キーにしたいが、候補が2つある。よって、どちらか判別することはできないので、外部キーの設定はできない。なんで？？
したがって、回答としては、次のようになる。
・『”在庫”テーブルに行を挿入できない』
・『異なるテーブルの主キーを同じ外部キーに入力できない』
・『単品商品番号とセット商品番号を同じ外部キーに入力できない』
設問1（4）
設問1（4）は、案1の”商品”テーブルの検査制約の問題である。
図1「関係”商品”及び”在庫”の関係スキーマ」の単品商品とセット商品の属性と表1「主な列の意味」
＜単品商品の検査制約＞
①社内原価 IS NOT NULL
③化粧箱番号 IS NULL
⑥詰合せ日数 IS NULL
＜セット商品の検査制約＞
②社内原価 IS NULL
③化粧箱番号 IS NOT NULL
ポイント
・UNIQUE制約とは
・外部キー設定の条件
・サブタイプをテーブルとして、実装する時の考慮すべき点
1. UNIQUE制約とは
| 項目 | 内容 |
|---|---|
| 概要 | 特定の列（または列の組み合わせ）の値が**一意（重複不可）**であることを保証する制約。 |
| 目的 | 重複データを防ぎ、整合性・検索性能を維持する。 |
| 特徴 | – NULLは複数行に許可される（DBMSによる）- 複数列の組合せ（複合UNIQUE）も設定可能 |
| 使用例 | 社員番号、メールアドレス、会員IDなどを一意に保つ場合 |
| 注意点 | – 主キー（PRIMARY KEY）も内部的にはUNIQUE制約を持つ。- UNIQUEとPRIMARY KEYの違いは「NULL許可」と「1テーブル1つ制限」。 |
| 例文（SQL） | ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email); |
- 意味
テーブル内で特定の列（または列の組み合わせ）の値が重複しないことを保証する制約。 - 特徴
- NULLは許容される（ただし DBMS によっては複数NULL可/不可の違いがある）。
- 1テーブルに複数の UNIQUE 制約を定義可能。
- 主キーと違い「必ず一意であるが、必ず存在しなくてもよい」（NULL可）。
例：
- 商品テーブルの「表示順」は「重複しない」という業務要件があるため UNIQUE 制約を付与。
- 社員テーブルの「メールアドレス」なども典型的な UNIQUE 制約対象。
2. 外部キー設定の条件
| 項目 | 内容 |
|---|---|
| 概要 | 外部キー（FOREIGN KEY）は、他テーブルの主キーまたは一意キーを参照し、参照整合性を保証する。 |
| 設定条件 | ✅ 以下すべてを満たす必要あり：1️⃣ 参照先の列が 主キーまたはUNIQUE制約付き列 である。2️⃣ 参照元・参照先の データ型・桁数が一致 している。3️⃣ 両方のテーブルが 同じDBスキーマ上 に存在。 |
| 参照整合性制約の動作 | – ON DELETE CASCADE：親削除時に子も削除- ON DELETE SET NULL：親削除時に子をNULL化- ON UPDATE CASCADE：親の変更に追従 |
| 目的 | 論理的に関連するデータの整合性を保持（孤児レコードの防止）。 |
| 注意点 | – 外部キー制約により、削除・更新が遅くなる場合あり。- パフォーマンス重視の場面では一時的に外すこともある。 |
| 例文（SQL） | ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); |
- 外部キー（FK）制約とは
あるテーブルの列が、他のテーブルの「主キー」または「UNIQUE制約付き列」を参照することを保証する仕組み。 - 条件
- 参照先列が主キー or UNIQUE 制約を持っていること。
- データ型・桁数が一致していること。
- 外部キーは 1つの参照先テーブルしか指定できない（複数テーブル跨ぎは不可）。
- 効果
- 存在しない値を参照できない（参照整合性保証）。
- 参照先が削除された場合の挙動（RESTRICT, CASCADE, SET NULL など）が制御できる。
例：
- 在庫テーブルの商品番号 → 商品テーブルの商品番号（案1のように1つにまとまっていれば外部キー可能）。
- 案2のように商品テーブルが「単品」「セット」に分かれてしまうと、外部キー先が特定できないため設定できない。
3. サブタイプをテーブルとして実装する時の考慮点
「単品商品」と「セット商品」のように、共通の属性を持ちながら一部の属性や振る舞いが異なる関係をスーパータイプ/サブタイプ（または汎化/特化）と呼びます。これをリレーショナルデータベースで実装する際には、主に以下の3つのパターンがあり、それぞれ考慮点があります。
| 実装パターン | 特徴 | 考慮すべき点（今回の問題に絡む点） |
| 案1: 1つのテーブルにまとめる (スーパータイプ方式) | 全属性を1つのテーブルに定義し、区別用のフラグ（例: 商品区分）を持つ。 | 外部キーの不都合: 外部参照時、単品とセットの区別がつかず、不適切な参照（設問1（3）ア）が起こりやすい。 |
| 案2: サブタイプごとにテーブルを分ける (サブタイプ方式) | 単品商品、セット商品のテーブルに分け、共通属性を持つスーパータイプテーブルは作らない。 | 外部キーの不都合: 外部参照（例: 在庫）はどちらのテーブルを参照すべきか確定できず、通常の外部キー設定（設問1（3）イ）ができない。 |
| 案3: スーパータイプとサブタイプに分ける | 商品（共通属性）と単品商品、セット商品（固有属性）のテーブルに分ける。 | 複数のテーブルをJOINする必要があり、参照は複雑になるが、外部キーによる参照整合性は保ちやすい。 |
(a) 単一テーブル方式（案1）
- サブタイプを1つのテーブルにまとめる。
- 利点
- 外部キー参照がシンプル（商品番号は1か所だけ）。
- 在庫テーブルなどは商品番号をそのまま参照可能。
- 欠点
- セット商品と単品商品で不要な列が出る（NULLが多くなる）。
- サブタイプ間の制約（例：単品商品構成にセット商品を入れられてしまう等）が表現しづらい。
(b) 複数テーブル方式（案2）
- サブタイプごとに別テーブルを持つ。
- 利点
- サブタイプごとの制約を強くできる。
- 無駄な列を持たない。
- 欠点
- 在庫テーブルのように、全体で共通の概念（商品番号）を参照する場合に外部キーを張れない。
- 複数テーブルを跨いで一意性を保証する仕組み（例：表示順の一意性）が直接は実現できない。
- UNION やビューを使って「共通の商品一覧」を構成する必要が出る。
設問2（なし）
設問3
設問3（1）
設問3（1）は、デッドロックが発生有無について答える問題である。
＜各トランザクション＞
TR1；単品商品を2個注文
TR2；単品商品1セット商品1個注文
TR3；セット商品1個注文
TR4；セット商品2個注文
＜処理フローの確認＞
◾️トランザクションのISOLATIONレベル；READ COMMITED
◾️単品商品の場合
①注文に基づいて、”注文”、”注文明細”テーブルに行挿入。
②表示順で”在庫”テーブルの引当可能数を調べ、可能なら更新する。
◾️セット商品の場合
①注文に基づいて、”注文”、”注文明細”テーブルに行挿入。
②表示順で”在庫”テーブルの引当可能数を調べ、引当可能なら更新する。
③引当不可なら、
＜セット商品の場合＞
・（”在庫”テーブル）不足セット商品数；不足数を加算。
・”セット商品構成”テーブルから主キー｛セット商品番号、単品商品番号｝順に、セット商品を構成する単品商品の構成数を調べ、必要数を計算。
＜単品商品の場合の”在庫”テーブルについて＞
・引当可能数；必要数を減算した値
・不足セット商品用引当済み数；必要数を加算した値
＜TR1・TR2の場合＞
回答は『×』。フローを確認すると、
『〔TR1〕単品商品AとBを注文（表示順）。
（TR2）単品商品Bと単品商品BとAを含むセット商品C（B、Aの並びは｛セット商品番号、単品商品番号｝順）を注文（表示順）。
→（TR1）で単品商品A、Bを注文（表示順）→＜引当可＞→（TR1）表示順で”在庫”テーブルでAを更新（行ロック）→（TR2）で単品商品Bとセット商品Cを注文→＜引当不可＞→（TR2）で単品商品Bをロック→（TR2）主キー順で”在庫”テーブルの単品商品B、Aの必要数を更新→（TR1）でAは更新中のため、（TR2）のAは待ち、Bはロック中→（TR1）表示順で”在庫”テーブルでBを更新→（TR1）A；ロック中、B；（TR2）のロック解放待ち、（TR2）A；（TR1）のロック解放待ち、B；ロック中』となり、デッドロックになる。
ポイント
ロック取得順が逆転しているため、同時に進むと TR1はB待ち・TR2はA待ち となり、循環待ちが発生する。
＜TR1・TR3の場合＞
回答は『×』。フローを確認すると、
『〔TR1〕単品商品AとBを注文（表示順）。
（TR3）単品商品BとAを含むセット商品C（B、Aの並びは｛セット商品番号、単品商品番号｝順）を注文（表示順）。
→（TR1）で単品商品A、Bを注文（表示順）→＜引当可＞→（TR1）表示順で”在庫”テーブルでAを更新→（TR3）でセット商品Cを注文→＜引当不可＞→（TR3）主キー順で”在庫”テーブルの単品商品B、Aの必要数を更新→（TR1）でAは更新中のため、（TR3）のAは待ち、Bはロック中→（TR1）表示順で”在庫”テーブルでBを更新→（TR1）A；ロック中、B；（TR3）のロック解放待ち、（TR3）A；（TR1）のロック解放待ち、B；ロック中』となり、デッドロックになる。
TR1は 表示順 A→B でロックを取得。
TR2は セット構成順 B→A でロックを取得。
| 時点 | TR1の処理 | TR1のロック状態 | TR2の処理 | TR2のロック状態 | 状態 |
|---|---|---|---|---|---|
| ① | 単品商品Aを注文（表示順1位）→在庫A更新開始 | Aロック取得中 | ― | ― | TR1がAをロック |
| ② | 単品商品Bを注文予定（表示順2位） | Aロック保持 | セット商品C注文（在庫不足）→構成商品処理開始 | Bロック取得 | 両者でロック取得順が逆 |
| ③ | ― | Aロック保持 | 在庫B更新済、次に在庫Aを更新しようとするがAはTR1が保持中 | Bロック保持、A待ち | TR2がA待ち発生 |
| ④ | 在庫Bを更新しようとするが、BはTR2が保持中 | Aロック保持、B待ち | AはTR1待ち、Bは保持中 | Bロック保持、A待ち | 相互待ち（デッドロック成立） |
＜TR3・TR3の場合＞
デッドロックが起こる可能性があるのは、同じ行に対する処理が重なった時なので、
（TR3A）単品商品BとAを含むセット商品C（B、Aの並びは｛セット商品番号、単品商品番号｝順）を注文（表示順）。
（TR3B）単品商品BとAを含むセット商品C（B、Aの並びは｛セット商品番号、単品商品番号｝順）を注文（表示順）。
①（TR3A）セット商品Cを注文。
②（TR3A）＜引当不可＞
③（TR3A）セット商品の構成商品のうち、Bを行ロック（主キー順）
④（TR3B）ット商品Cを注文。
⑤（TR3B）＜引当不可＞。
⑥（TR3B）セット商品の構成商品のうち、Bを行ロックしにいくが、③の処理待ち
⑦（TR3A）セット商品の構成商品のうち、Aを行ロック（主キー順）／（TR3B）Bの行ロック解放待ち
⑧（TR3A）セット商品の構成商品B・Aを更新／（TR3B）Bの行ロック解放待ち
⑨（TR3A）セット商品の構成商品B・Aの行ロック解放／（TR3B）Bの行ロック
〜以降〜（TR3B）の処理が進む
よって、＜TR3・TR3の場合＞主キー順で更新するのでデッドロックは発生しない。
（セット商品Dの構成が（単品商品A・B・C）で、セット商品Eの構成商品が（単品商品A・C）の場合などはデッドロックが起こりそう）
結論
同一のセット商品Cを TR3A・TR3B が注文した場合：
主キー順（B→A）の統一でロック順が同じになるため、デッドロックは発生しない。
（TR3B は待たされるが、先行トランザクション終了後に順次進む）
| 時点 | TR3Aの処理 | TR3Aのロック状態 | TR3Bの処理 | TR3Bのロック状態 | 状態 |
|---|---|---|---|---|---|
| ① | セット商品Cを注文 | ― | ― | ― | ― |
| ② | 在庫不足 → 構成商品の処理へ | ― | ― | ― | ― |
| ③ | 構成商品Bをロック（主キー順でB→A） | Bロック取得 | ― | ― | TR3AがBを確保 |
| ④ | ― | Bロック保持 | セット商品Cを注文 | ― | 並行開始 |
| ⑤ | ― | Bロック保持 | 在庫不足 → 構成商品の処理へ | ― | TR3Bが構成処理に入る |
| ⑥ | ― | Bロック保持 | 構成商品Bをロックしようとするが待ち | B待ち | TR3Bが待ち状態 |
| ⑦ | 構成商品Aをロック（主キー順2番目） | Bロック保持、Aロック取得 | B待ち | B待ち | 順序通り進む |
| ⑧ | 構成商品B・Aを更新 | B・Aロック保持 | B待ち | B待ち | TR3Aが更新完了へ |
| ⑨ | 構成商品B・Aのロックを解放 | ― | Bロック取得 | Bロック取得 | TR3Bの処理再開 |
| ⑩ | ― | ― | 構成商品Aをロック → 更新 | Aロック取得 | TR3Bが正常に進む |
＜TR3・TR4の場合＞
デッドロックが起こる可能性があるのは、同じ行に対する処理が重なった時なので、
（TR3）単品商品BとAを含むセット商品C（B、Aの並びは｛セット商品番号、単品商品番号｝順）を注文（表示順）。
TR4；セット商品2個注文。セット商品D（構成商品；A・E）とセット商品F（構成商品；B・E）を注文。
①（TR3）未処理／（TR4）セット商品DとFを注文。
②（TR3）未処理／（TR4）＜引当不可＞
③（TR3）未処理／（TR4）構成商品；A・Eと構成商品；B・Eをロックしにいく
④（TR3）未処理／（TR4）A；行ロック
⑤（TR3）セット商品Cを注文／（TR4）A；行ロック中
⑥（TR3）＜引当不可＞／（TR4）A；行ロック中
⑦（TR3）構成商品；B・Aを主キー順でロックしにいく／（TR4）A；行ロック中
⑧（TR3）B；行ロック／（TR4）A；ロック中、E；行ロック
⑨（TR3）B；ロック中、A；未処理／（TR4）A；ロック中、E；ロック中、B；行ロック
⑩（TR3）B；ロック中、A；未処理／（TR4）A；ロック中、E；ロック中、B；ロック中
(11)（TR3）B；ロック中、A；行ロック【ロック解放待ち】／（TR4）A；ロック中、E；ロック中、B；行ロック
→デッドロック発生。
よって、＜TR3・TR4の場合＞（TR4）で（TR3）のセット商品の構成商品が、2つのセット商品にまたがり、かつ、（TR3）の商品順と異なる順で注文された場合、デッドロックが発生する。
（TR4）セット商品D（構成商品；A・E）とセット商品F（構成商品；B・E）と
（TR3）セット商品C（構成商品；B・A）で（A→B）と（B→A）で順番があべこべになっている。
| 時点 | TR3の処理（セットC: B→A） | TR3のロック状態 | TR4の処理（セットD:A,E ＋ F:B,E） | TR4のロック状態 | 状態／メモ |
|---|---|---|---|---|---|
| ① | 未実行（まだ注文処理に入っていない） | ― | セットD・F を注文 | ― | TR4開始 |
| ② | 未実行 | ― | 在庫不足判定 → 構成商品の処理へ | ― | TR4は構成処理へ |
| ③ | 未実行 | ― | 構成商品の行をロック取得しにいく（複数行） | ― | これからロック取得 |
| ④ | 未実行 | ― | A を行ロック取得 | A:ロック | TR4 が A を確保 |
| ⑤ | セットC を注文（TR3開始） | A: —（未取得） | TR4 A:ロック中 | A:ロック | TR3が割り込む形で開始 |
| ⑥ | 在庫引当判定（TR3） → 引当不可 | ― | TR4 A:ロック中 | A:ロック | TR3は構成処理へ進む必要あり |
| ⑦ | 構成商品のロック取得へ（主キー順 B→A） | ― | TR4 A:ロック中、E:ロック取得へ | A:ロック, E:ロック取得中/保持 | TR3が B→A の順でロックを取ろうとする |
| ⑧ | B を行ロック取得 | B:ロック | TR4 は A:ロック中、E:ロック保持 | A:ロック, E:ロック | TR3 が B を確保 → ここで交差が始まる |
| ⑨ | TR3：B ロック保持、次に A を取得しようとする（AはTR4が保持） | B:ロック、A:待ち | TR4：A ロック保持、E ロック保持、次に B を取得しにいく（BはTR3が保持） | A:ロック, E:ロック, B:待ち | TR3 は A を待ち、TR4 は B を待つ（相互待ち） |
| ⑩ | 同上（互いに解除待ち） | B:ロック, A:待ち | 同上（互いに解除待ち） | A:ロック, E:ロック, B:待ち | 循環待ちが継続 |
| ⑪ | TR3 が A のロック取得を試みる（だが TR4 が保持）：TR3 側は A の解放待ち（blocked） | B:ロック（保持）、A:ブロック | TR4 が B のロック取得を試みる（だが TR3 が保持）：TR4 側は B の解放待ち（blocked） | A:ロック（保持）、E:ロック（保持）、B:ブロック | 相互待ち（デッドロック）成立 |
結論（一行）
TR4 が複数のセットにまたがって構成商品（A と B）を別の順序（TR4 は A→… と B→… を混在）でロックし、TR3 が主キー順（B→A）でロックしたため、TR3 と TR4 のロック取得順が交差して循環待ち（デッドロック）を起こす。
なぜ起きたか（要点）
さらに TR4 が E も保持しているため、解放の順序も混乱しやすい（複数行ロック＝発生リスク増）。
デッドロックは「同じ行」に対する処理が重なるかつ「ロック取得順が逆転する」場合に発生する。
今回は TR3 が B→A の順でロック取得、TR4 は A を先に取りその後 B を取ろうとするため順序が逆になり循環待ちが発生した。
デッドロック問題のポイント整理
1. トランザクション分離レベル（READ COMMITTED）
- READ COMMITTED は「コミット済みデータのみ読める」分離レベル。
- まだコミットされていないデータは他トランザクションから見えない。
- 通常の SELECT ではロックは保持し続けず、更新系（UPDATE/DELETE/SELECT FOR UPDATE）で行ロックが発生する。
- → デッドロック回避を保証するわけではない。
2. デッドロックの基本
- 定義：複数トランザクションが互いに必要なロックを相手が保持しており、待機が循環して解決不能となる状態。
- 発生条件（4条件）：
① 相互排他、②保持待ち、③奪い合い不可、④循環待ち。 - DBMS は通常、デッドロックを検出すると 一方をロールバックして解消する。
3. 今回の発生パターン
A：異なる処理で異なるロック順序を採用
- TR3：主キー順に B → A の順でロック。
- TR4：セット商品ごとの順序で A → E、B → E のように別順序を混在。
- 結果：TR3 が B を確保し A を待ち、TR4 が A を確保し B を待ち → 循環待ち。
B：複数処理で同一行を異なる順序で参照
- セット商品が複数の商品を共有しており（例：A, B, E が重複して登場）、
処理パターンごとに「A 先か B 先か」が揺れる。 - この揺れにより同じ在庫行に対して 異なる順序でロック要求 → デッドロックが発生。
4. その他の補足ポイント
- 複数行ロックはリスク増：構成商品が増えると組み合わせが爆発的に増え、順序揺れの可能性が高まる。
- 典型的な回避策：
- ロック取得順序をシステム全体で統一（例：常に商品ID昇順）。
- まとめてロック（
SELECT ... FOR UPDATEで一括）。 - デッドロック発生を前提にし、例外検出後にリトライ。
- 試験観点の要点：
- デッドロックは「同一行に異なる順序でアクセス」することが主因。
- READ COMMITTED でも普通に起こり得る。
✅ まとめ直すと：
対策は「ロック順序統一」「一括ロック」「再試行処理」が定石。
READ COMMITTED はデータ一貫性を保証するが、デッドロックは防げない。
デッドロックは「ロック順序の不一致」と「同一行アクセスの順序揺れ」で起きる。
今回は TR3 が B→A、TR4 が A→B で循環待ちを形成。
TR1（単品2個）は表示順に従って在庫行を更新する。仮に表示順が A→B だとすると TR1 はまず A をロック（更新）、次に B をロックする。
TR2（セット）はセット構成の主キー順に従って単品の必要数を更新する。構成順が B→A なら TR2 はまず B をロック、その次に A をロックしようとする。
タイミング次第で、ある瞬間に TR1 が A をロックして B を取得しようとしている一方、TR2 が B をロックして A を待っている、という相互待ち（サイクル）が発生する。
設問3（2）
