【平成31年度春期】データベーススペシャリスト試験 午後Ⅰ問２

自己採点結果　iTEC　15/50　　TAC　20/50

設問１
（１）　ア　在庫　　イ　更新　　ウ　部品番号　　エ　出庫要求明細　　オ　出庫要求番号
　　　 カ　部品番号

【コメント】

トリガーの基本動作を問う問題でしたが、私はこのトリガーの動作を全く理解していないまま解答していました。

BEFOREトリガ：在庫引当処理を行うにあたって、トリガーとなるのは「出庫要求」が行われたとき、つまり「出庫要求」テーブル及び「出庫要求明細」テーブルに行が追加されたときに発動する。そのときに挿入された"部品番号"の値をキーにして「在庫」テーブルから「実在庫数量」と「引当済数量」を参照し、出庫要求数量と比較して引当可能か否かを判定する。「部品番号」をキーにするから、アは「出庫要求明細」、イは「挿入」、エは「在庫」

AFTERトリガ：（引当可能な場合）「在庫」テーブルの「引当済数量」を更新する。よって、オは「実在庫数量」、カは「引当済数量」

（２）
(a) 先行する出庫要求が在庫テーブルのある部品番号の行を参照し、引当処理による更新が完了する前に、後続の出庫要求が同じ部品番号の行を参照して引当処理を行った。

(b) 参照した行単位に専有ロックを掛ける。

 【コメント】

(a) 隔離水準がREAD COMMITTED（ダーティリードは発生しないが、ノンリピータブルリード及びファントムリードは発生する）の場合、先行する「出庫要求」トランザクションが短期ロック（参照）している中で、後続の「出庫要求」トランザクションが長期ロック（更新）すると、先行のトランザクションは後続のトランザクションで更新された結果に対して更新をかけてしまうため問題が生じてしまう。ここでいう問題は、参照時に”実在庫数量"－"引当済数量"≦"基準在庫数量"のチェックが働かないまま更新ができてしまうこと。そこまでは何となく読めてましたが、私は部品番号（行）について解答していましたが、ここでは具体的に"引当済数量"の不正を答えるべきでした。

(b) 上記(a)の問題を解決するには、参照時にその行に対して専有ロックを掛ければよく、問題文の1.ISORATIONレベルに「データ参照時にFOR UPDATE句を指定すると、対象行に専有ロックを掛け、トランザクション終了時に開放する」とあるので、FOR UPDATE句を指定する旨を解答すべきでした。

設問２
（１）　a ウ　　b ア　　c エ　　d キ
（２）　あ　入庫　い　在庫　う　実在庫数量　え　発注済フラグ

【コメント】

（１）トリガについてのSQL文は全く知りませんでした。勉強不足。「在庫」テーブルの"引当済数量"を更新する（UPDATE OF 引当済数量 ON 在庫）のはAFTERトリガであるため、空欄aにはAFTER、その際に発動契機となるのは新規に「在庫」テーブルに行が挿入された場合だから空欄bにはNEW、その際に発動する処理は行単位で行うから、空欄cにはFOR EACH ROW、その処理内容は、新規挿入された行（ASで定義したCHKROWで受けている）に対して”実在庫数量"－"引当済数量"≦"基準在庫数量"を満たすとき、（空欄d はWHEN）ストアドプロシジャーを呼び出す、という処理のようでした。

（２）このAFTERトリガは、「在庫」テーブルの"引当済数量"を更新する際に発動することを狙いとしていたが、この発動条件では"引当済数量"を更新する「出庫」でも発動してしまう。（空欄あ「出庫」）。「出庫」では、(6)にあるように「在庫」テーブル（空欄い「在庫」）の"引当済数量"（空欄う「引当済数量」を更新されるため、このAFTERトリガは「出庫」ごとに発動することになってしまう。これを繰り返さないようにするため、「在庫」テーブルの"発注済フラグ"（空欄え「発注済フラグ」）の状態をトリガ発動の判定条件に加えておく必要がある。具体的には"発注済フラグ"がオフであることをトリガ発動条件に含めるようにする。

設問３
（１）　在庫テーブル
（２）　
① 出庫要求及び入庫で発生するトランザクションを部品番号単位とし、ロック粒度を小さくする
② 発注済フラグがオンの部品は出庫要求を受け付けないようにし、出庫要求と入庫の発生時期をずらす。

【コメント】

（１）「出庫要求」と「入庫」で参照されるのは「在庫」テーブルである。

（２）デッドロックは、異なるトランザクションが異なるタイミングで同じ対象を専有ロックしてしまうことにより、互いの処理が進まず処理が永遠に終わらない状態となること。これを回避するためには、①ロックを掛ける対象を「出庫要求」と「入庫」でタイミングをずらす、②ロックを掛ける対象の範囲を小さくして同時にロックする状況を少なくする（ロック粒度を小さくする）、ということが考えられました。iTECは突然ISOLATIONレベルがREPEATABLE READと定義し、この隔離水準を下げる解答を出してきましたが、FOR UPDATE句を用いていることからすると、問題で問うているISOLATIONレベルはREAD COMMITTEDが前提なのでは？と思っています。iTECとTAC両者で共通する解答には、部品番号順に更新処理を行うことにより、索引から読み込んだ行だけをロック対象としてロック粒度を抑えることが挙げられています。私の解答①では「複数の部品を１つのトランザクションに載せている」ことがロック粒度を大きくしていると思い、部品単位のトランザクション発生を解答したのですが、これはちょっと強引だったかもしれません。また②のトランザクション発生時期をずらすにしても、基準在庫が下回って入荷待ち（"発注済フラグ”がオン）の場合は出庫要求を受け付けないというのでは、業務に支障が出てしまうでしょう。