2022年 春期 応用情報技術者試験 問6

クーポン発行サービス

K社は，インターネットでホテル，旅館及びレストラン（以下，施設という）の予約を取り扱う施設予約サービスを運営している。各施設は幾つかの利用プランを提供していて，利用者はその中から好みのプランを選んで予約する。会員向けサービスの拡充施策として，現在稼働している施設予約サービスに加え，クーポン発行サービスを開始することにした。

発行するクーポンには割引金額が設定されていて，施設予約の際に料金の割引に利用することができる。K社は，施設，又は都道府県，若しくは市区町村を提携スポンサとして，提携スポンサと合意した割引金額，枚数のクーポンを発行する。

クーポン発行に関しては，提携スポンサによって各種制限が設けられているので，クーポンの獲得，及びクーポンを利用した予約の際に，制限が満たされていることをチェックする仕組みを用意する。

提携スポンサによって任意に設定可能なチェック仕様の一部を表1に，クーポン発行サービスの概要を表2に示す。

[クーポン発行サービスと施設予約サービスのE-R図]

クーポン発行サービスと施設予約サービスで使用するデータベース（以下，予約サイトデータベースという）のE-R図（抜粋）を図1に示す。予約サイトデータベースでは，E-R図のエンティティ名をテーブル名に，属性名を列名にして，適切なデータ型で表現義した関係データベースによってデータを管理する。

クーポン管理テーブルの列名の先頭に"獲得制限"又は"予約制限"が付く列は，クーポンの獲得制限，又はクーポンを利用した予約制限のチェック処理で使用し，チェックが必要ない場合にはNULLを設定する。"獲得制限_1枚限り"には，「同一会員1枚限りの獲得制限」のチェックが必要なときは'Y'を，不要なときはNULLを設定する。

UNIQUE制約を付けた。なお，予約サイトデータベースにおいては，UNIQUE制約を構成する複数の列で一つの列でもNULLの場合は，UNIQUE制約違反とならない。

d クーポン明細 ADD CONSTRAINT クーポン明細_IX1
UNIQUE(クーポンコード，獲得会員コード，獲得制限_1枚限り)

L主任は，①予約テーブルの"クーポンコード"，"クーポン発行連番"に対しても，UNIQUE制約を付けた。

予約サイトデータベースでは，更新目的の参照処理と更新処理においてレコード単位にロックを掛け，多重処理を行う設定としている。ロックが掛かるとトランザクションが終了するまでの間，他のトランザクションによる同一レコードに対する処理はロック解放待ちとなる。

[クーポン獲得処理の連番管理方式]

クーポン発行サービスと施設予約サービスのCRUD図（抜粋）を図3に示す。

クーポン新規登録処理では，1種類のクーポンにつき1レコードをクーポン管理テーブルに追加する。クーポン獲得チェック処理では，獲得可能期間，会員住所による獲得制限，発行上限枚数に関するチェックを行う。チェックの結果，エラーがない場合に表示される同意ボタンを押すことによって，クーポン獲得処理を行う。

クーポン発行サービスでは，上限の定められた発行枚数分のクーポンを抜けや重複なく連番管理する方式が必要になる。特に，提携スポンサが都道府県，市区町村であるクーポンは割引金額が大きく，クーポンの発行直後にトラフィックが集中することが予想される。発行上限枚数到達後にクーポン獲得処理が動作する場合の考慮も必要である。L主任は，トラフィック集中時のリソース競合によるレスポンス悪化を懸念して，ロック解放待ちを発生させない連番管理方式（以下，ロックなし方式という）のSQL文（図4）を考案した。このSQL文では，ロックを掛けずに参照，主キー制約によってクーポン発行連番の重複レコード作成を防止する。

ここで，関数COALESCE(A, B)は，AがNULLでないときはAを，AがNULLのときはBを返す。また，"クーポンコード"，"会員コード"は，該当の値を格納する埋込み変数である。

INSERT INTO クーポン明細 (クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り)
WITH 発行済枚数取得 AS (SELECT COALESCE(MAX(c), 0) AS 発行済枚数
    FROM クーポン明細 WHERE クーポンコード = :クーポンコード)
SELECT :クーポンコード,
    (SELECT 発行済枚数 + 1 FROM 発行済枚数取得) < 発行上限枚数),
    :会員コード, 獲得制限_1枚限り
FROM クーポン管理 WHERE クーポンコード = :クーポンコード;

[クーポン獲得処理の連番管理方式の見直し]

ロックなし方式をレビューしたM課長は，トラフィック集中時に主キー制約違反が発生することによって，会員による再オペレーションが頻発するデメリットを指摘し，ロック解放待ちを発生させることによって更新が順次行われる連番管理方式（以下，ロックあり方式という）の検討と方式の比較，高負荷試験の実施を指示した。

L主任は，クーポン管理テーブルに対して初期値が0の"発行済枚数"という列を追加し，このデータ項目のカウントアップによって連番管理をするロックあり方式のSQL文（図5）を考案した。

UPDATE クーポン管理 
WHERE クーポンコード = :クーポンコード AND 発行済枚数 < g ;
INSERT INTO クーポン明細 (クーポンコード, クーポン発行連番, 獲得会員コード, 獲得制限_1枚限り)
SELECT :クーポンコード, 発行済枚数, :会員コード, 獲得制限_1枚限り
FROM クーポン管理 WHERE クーポンコード = :クーポンコード;

④ロックあり方式では，図3のCRUD図の一部に変更が発生する。

L主任は，ロックなし方式とロックあり方式の比較を表3にまとめ，高負荷試験を実施した。

高負荷試験実施の結果，どちらの方式でも最大トラフィック発生時のレスポンス，スループットが規定値以内に収まることが確認できた。そこで，会員による再オペレーションの発生しないロックあり方式を採用することにした。