１つのフォームで同時に２つの表を更新する際に、失われた更新の防止(ロストアップデート防止)がどのように働くか、確認してみました。Oracle APEXが標準で提供しているロストアップデートの防止については、以前に記事を書いています。
スキーマ定義
確認のために２つ簡単な表を作成しました。クイックSQLでの定義は以下です。行バージョン番号も生成しています。
# prefix: tst
# rowVersion: true
# semantics: default
concjoin1
value1 vc80
value11 vc80
concjoin2
value2 vc80
value22 vc80
生成されたDDLは以下です。行バージョン番号を付加するトリガーも生成されています。
create table tst_concjoin1 (
id number generated by default on null as identity
constraint tst_concjoin1_id_pk primary key,
row_version integer not null,
value1 varchar2(80),
value11 varchar2(80)
)
;
create table tst_concjoin2 (
id number generated by default on null as identity
constraint tst_concjoin2_id_pk primary key,
row_version integer not null,
value2 varchar2(80),
value22 varchar2(80)
)
;
-- triggers
create or replace trigger tst_concjoin1_biu
before insert or update
on tst_concjoin1
for each row
begin
if inserting then
:new.row_version := 1;
elsif updating then
:new.row_version := nvl(:old.row_version,0) + 1;
end if;
end tst_concjoin1_biu;
/
create or replace trigger tst_concjoin2_biu
before insert or update
on tst_concjoin2
for each row
begin
if inserting then
:new.row_version := 1;
elsif updating then
:new.row_version := nvl(:old.row_version,0) + 1;
end if;
end tst_concjoin2_biu;
/対話グリッドのページ作成
以下のSQLをソースとする対話グリッドのページを作成します。
select
t1.id,
t1.value1,
t2.value2
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
最初に空のアプリケーションを作成します。アプリケーション・ビルダーより、作成、新規アプリケーションの作成を実行し、アプリケーションの設定画面で名前だけ設定し、アプリケーションの作成をクリックします。
作成したアプリケーションより、ページ作成ウィザードを実行します。ページの作成をクリックします。
フォームを選択します。
編集可能対話グリッドを選択します。
ページ名を行の値 - VALUE1 & 2と設定し、次へ進みます。このページに作成する対話グリッドのプロパティ、失われた更新のタイプを行の値(これがデフォルトの設定です)として確認を行うため、この名前にします。
ナビゲーションのプリファレンスとして、新規ナビゲーション・メニュー・エントリの作成を選択し、次へ進みます。新規ナビゲーション・メニュー・エントリはデフォルトでページ名が設定されます。
データ・ソースとしてローカル・データベースを選択します。編集が有効をONにし、ソース・タイプはSQL問合せとし、SQL SELECT文を入力のところに、SQLを入力します。検証をクリックしてSQLを確認し、作成を実行します。
対話グリッドのリージョンを含んだページが作成されます。認識されている列を調整します。左ペインの
レンダリング・ビューより、対話グリッドの
列を開きます。
ROWIDを削除します。コンテキスト・メニューを開き、
削除を実行します。
ID列を選択し、タイプを非表示、主キーをONに変更します。
ページを実行し、一行挿入してみます。
ORA-01776: 結合ビューを介して複数の実表を変更できません。というエラーが発生します。
対話グリッドのソースがSQLの場合、挿入/更新/削除の処理は、以下のようなSQLにて実施されます。
挿入は必ずエラーになります。挿入の対象が表TST_CONCJOIN1(IDとVALUE1)とTST_CONCJOIN2(VALUE2)の両方になるためです。
insert into
(
select
t1.id,
t1.value1,
t2.value2
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
) t
values
(1,100,200);
ジョインをしていても、データを挿入する列が片方の表のみを対象としているときは、データの挿入が可能です。上記の例では、VALUE2を除けば表TST_CONCJOIN1のIDとVALUE1がデータの挿入の対象になります。ですので、VALUE2の問合せのみをONにして挿入の対象から除外すると、データの挿入時にエラーが発生せず、表TST_CONCJOIN1のみにデータが挿入されます。
今回の例では表TST_CONCJOIN1のみに行が挿入されても、同じIDが表TST_CONCJOIN2に存在しないため、ジョインした結果としては何も表示されません。
同様に、更新の場合は以下のようなSQLが実行されます。こちらも両方の表を同時に更新しようとするため、エラーになります。
update
(
select
t1.id,
t1.value1,
t2.value2
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
) t
set t.value1 = 100, t.value2 = 200
where t.id = 1;
削除の場合は、表TST_CONCJOIN1のIDのみを対象とすることになるため、そちらの表の行のみ削除されます。
delete from
(
select
t1.id,
t1.value1,
t2.value2
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
) t
where t.id = 1;ジョインした表を対象に、それぞれを同時に更新するために以下のSQLを対話グリッドを処理するプロセスに設定します。
begin
case :APEX$ROW_STATUS
when 'C' then
insert into tst_concjoin1(value1) values(:VALUE1) returning id into :ID;
insert into tst_concjoin2(id, value2) values(:ID, :VALUE2);
when 'U' then
update tst_concjoin1 set value1 = :VALUE1 where id = :ID;
update tst_concjoin2 set value2 = :VALUE2 where id = :ID;
when 'D' then
delete from tst_concjoin1 where id = :ID;
delete from tst_concjoin2 where id = :ID;
end case;
end;
左ペインにてプロセス・ビューを開いて、行の値 - VALUE1 & 2 - 対話グリッド・データの保存という名前のプロセスを選択します。タイプが対話グリッド - 行の自動処理(DML)であるプロセスです。設定のターゲット・タイプをPL/SQL Codeに変更し、挿入/更新/削除するPL/SQLコードに上記のSQLを設定します。
失われた更新はON、行のロックはYesとして、それぞれ有効にしておきます。
最後に対話グリッドのAttributesを開いて、失われた更新タイプが行の値となっていることを確認します。
以上で、最初のページは完成しました。あたかも、列としてValue1とValue2を持つ表を操作しているのと同じように、対話グリッドを操作できます。動かして確認してみましょう。
対話グリッドは必ずしも、単一の表だけが編集の対象ではなく、単一の表をイメージした操作を提供するコンポーネントになっています。
ロストアップデートの防止の動作確認
セッションを分離するため、異なるブラウザを立ち上げ、作成した対話グリッドのページをそれぞれ開きます。
Value1がJohn、Value2がKateである行が双方の対話グリッドに存在する状態です。
最初にKateをJaneに変更し、保存を実行します。
別のブラウザに操作を移動します。Value1がJohn、Value2がKateになっています。
JohnをKenに変更し、
保存をクリックします。
ユーザーが更新処理を開始してから、データベース内の現行バージョンのデータが変更されています。というエラーが発生します。
対話グリッドの失われた更新タイプとして行の値が設定されている場合、ソースから対話グリッドにデータを取得する際に、行の値からチェックサムも同時に生成します。以下のようなイメージです。
select
t1.id id,
t1.value1 value1,
t2.value2 value2,
apex_item.md5_checksum(t1.id, t1.value1, t2.value2) cs
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
そして、更新処理を行う前に、変更対象の行に限定して、同じ問合せを発行しチェックサムを取得します。
select
t1.id id,
t1.value1 value1,
t2.value2 value2,
apex_item.md5_checksum(t1.id, t1.value1, t2.value2) cs
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
where t1.id = 1 for update nowait
取得したチェックサムが一致すると、取り出した時点から保存されている行に変更が発生していないと判断して、実際の行の更新処理を行います。行のロックがYesになっていると、確認のためにチェックサムを取り出す際に、for updateを加えて行をロックします。チェックサムの再取得から実際の更新処理までは、ほとんど一瞬ですが、その間に行が変更されることを禁止しています。
ソースとして与えるSQLによっては、for updateが付けられるとエラーが発生したり、または意図したように表がロックされない場合があります。その場合は、Noとしてロックをかけるのを諦めるか（不要な場合もあります）、PL/SQL Codeにてロックをかけます。
アプリケーション開発時に意識をしていないと、高負荷のときに、実行されている処理の組み合わせに依存して、稀にアップデートした値が失われるという、原因の発見が難しい障害として顕在化する可能性があります。
単一表をソースとしている際には、ロストアップデートの防止はデフォルトで有効で、行のロックも有効であるため意識する必要はありません。
行の値が有効でないケースの確認
同じ手順で、ソースを以下のSQLに変更した対話グリッドのページを作成します。ページ名は行の値 - VALUE11 & 22とします。
select
t1.id,
t1.value11,
t2.value22
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
操作の対象からVALUE1, VALUE2を除き、代わりにVALUE11, VALUE22と含めます。更新処理として記載するPL/SQL Codeは以下になります。
begin
case :APEX$ROW_STATUS
when 'C' then
insert into tst_concjoin1(value11) values(:VALUE11) returning id into :ID;
insert into tst_concjoin2(id, value22) values(:ID, :VALUE22);
when 'U' then
update tst_concjoin1 set value11 = :VALUE11 where id = :ID;
update tst_concjoin2 set value22 = :VALUE22 where id = :ID;
when 'D' then
delete from tst_concjoin1 where id = :ID;
delete from tst_concjoin2 where id = :ID;
end case;
end;
先ほどと同様に、ブラウザを２つ開き、それぞれデータを更新します。
新しく追加した対話グリッドでデータを更新します。全行更新してもエラーは発生しません。
ソースでの取得対象に重なりがないため、他方が更新されてもチェックサムが変わるということがありません。
更新対象として重なりがなくても、ロストアップデートの防止を行いたい場合は、失われた更新タイプに行バージョン番号を選択します。
行バージョン番号による防止
ソースを以下のSQLに変更した対話グリッドのページを作成します。ページ名は行バージョン番号 - VALUE1 & 2とします。
select
t1.id,
t1.row_version + t2.row_version row_version,
t1.value1,
t2.value2
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
作成された対話グリッドのリージョンの列より、ROWIDを削除し、IDを非表示にします。さらに追加された 列ROW_VERSIONも非表示にします。ROW_VERSIONが対話グリッドより更新されることはありませんが、問合せのみはOFFにします。
更新に使用するPL/SQL Codeは、行の値 - VALUE1 & 2で設定したものと同じコードを使います。最後に対話グリッドのAttributesの失われた更新タイプを行バージョン列、行バージョン列をROW_VERSIONに変更します。
変更を保存すると、ページが完成です。続いて、ページ名を行バージョン番号 - VALUE11 & 22として、行の値 - VALUE11 & 22に対応するページを作成します。ソースは以下のSQLになります。
select
t1.id,
t1.row_version + t2.row_version row_version,
t1.value11,
t2.value22
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
更新に使用するPL/SQL Codeは、行の値 - VALUE11 & 22で設定したものと同じコードを使います。その他に指定する内容は、行バージョン番号 - VALUE1 & 2のページと同じです。
先ほどと同じ操作を行います。列Value2を全行更新します。
別のブラウザにて、列Value22を全行更新します。失われた更新タイプが行の値の際には発生しませんでしたが、今回はエラーになっています。
実は列ROW_VERSIONを問合せに含めた時点で（失われた更新タイプを行バージョン列として、列にROW_VERSIONを選ぶには、ソースのSQLにて取得が必須）チェックサムの計算にROW_VERSIONが含まれるため、設定が行の値のままでも結果は同じになります。ただし、列ROW_VERSIONがあるならチェックサムの計算は不要なので、ROW_VERSIONを選びましょう。
フォーム・リージョンの場合
Oracle APEXのバージョン19より、フォーム・リージョンが新設されています。フォーム・リージョンは、表の１行を対象として更新を行いますが、設定が対話グリッドど同じになるよう設計されています。
フォーム・リージョンについても、リージョンのソースとなるSQL問合せを設定します。
フォームに含まれるページ・アイテムの中で、主キーとなるページ・アイテムを決定します。
フォーム・リージョンのAttributesに、失われた更新タイプの設定が含まれます。
対話グリッドと同様に、更新処理を行うプロセスには、
設定として、
ターゲット・タイプ、
挿入/更新/削除するPL/SQLコード(タイプがPL/SQL Codeの場合)、
失われた更新の防止、
行のロックがあります。
更新に使用するコード内では、列名ではなくページ・アイテム名をバインド変数名として使用します。ページ番号9にフォームがある場合、以下のようなコードになります。対話グリッドで使用したものと、基本的に違いはありません。
begin
case :APEX$ROW_STATUS
when 'C' then
insert into tst_concjoin1(value11) values(:P9_VALUE11) returning id into :P9_ID;
insert into tst_concjoin2(id, value22) values(:ID, :P9_VALUE22);
when 'U' then
update tst_concjoin1 set value11 = :P9_VALUE11 where id = :P9_ID;
update tst_concjoin2 set value22 = :P9_VALUE22 where id = :P9_ID;
when 'D' then
delete from tst_concjoin1 where id = :P9_ID;
delete from tst_concjoin2 where id = :P9_ID;
end case;
end;
同一の行をフォームで開き、すでに別のブラウザから更新されている行に更新を行うと、対話グリッドの場合と同様にエラーが発生します。
本来であれば、対話グリッドとまったく同じ動作になるのですが、フォームの場合、現時点(2021年1月29日)では不具合のため、失われた更新タイプを行バージョン列に設定すると、新規行の挿入時に
ORA-01403: データが見つかりません。が発生します。そのため、行バージョン列を指定する前に、不具合が修正されているか確認する必要があります。
なお、対話グリッドと同様にソースのSQLにROW_VERSIONが含まれているため、失われた更新タイプが行の値であってもロストアップデートの防止はされます。この場合、本来必要のないチェックサムの計算が行われます。
動的アクションによる更新
動的アクションのサーバー側のコードを実行によって、表の値を更新することができます。便利な機能ですが、動的アクションによる更新には、今まで紹介してきたロストアップデートを防止する機能はありません。
動的アクションによって、表TST_CONCJOIN1に列VALUE1、VALUE11、表TST_CONCJOIN2の列VALUE2、VALUE22の値の読込、更新を行うページを作ります。
ページ番号は14で作成しています。
ボタンのアクションは、動的アクションで定義します。
読込のボタンのPL/SQLコードは以下になります。
begin
select
t1.value1,
t1.value11,
t2.value2,
t2.value22
into
:P14_VALUE1,
:P14_VALUE11,
:P14_VALUE2,
:P14_VALUE22
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
where t1.id = :P14_ID;
end;
送信するアイテムとしてP14_ID、戻すアイテムはP14_VALUE1, P14_VALUE2, P14_VALUE11, P14_VALUE22の４つです。
更新のボタンのPL/SQLコードは以下になります。
begin
update tst_concjoin1 set value1 = :P14_VALUE1, value11 = :P14_VALUE11 where id = :P14_ID;
update tst_concjoin2 set value2 = :P14_VALUE2, value22 = :P14_VALUE22 where id = :P14_ID;
commit;
end;
送信するアイテムとして、全てのページ・アイテムを指定します。
すでに存在するIDを見つけやすくするため、IDは選択リストとして、以下のSQLをLOVに登録しています。
select id d, id r from tst_concjoin1
ブラウザを２つ立ち上げ、同じIDを選択して読込を実行します。
何か値を変更し、更新を実行します。
別のブラウザへ移動し、値を変更して、更新します。エラーにならず更新できます。
最初のブラウザに戻り、読込を行うと、最後に更新したデータになり、そのブラウザにて更新したデータはなくなっています。
対話グリッドとフォームという標準のコンポーネントは、データを安全に更新するという機構が組み込まれていますが、動的アクションなどからSQLを呼び出して更新する場合などは、そういった機構が働かない、という点に注意を払ってアプリケーション開発を行う必要があります。
例えば、上記にROW_VERSIONを保持するページ・アイテムを追加し、ロストアップデートの防止を実装してみます。ページ番号は１５とします。
データの読込時にROW_VERSIONも取得します。
begin
select
t1.value1,
t1.value11,
t2.value2,
t2.value22,
t1.row_version + t2.row_version
into
:P15_VALUE1,
:P15_VALUE11,
:P15_VALUE2,
:P15_VALUE22,
:P15_ROW_VERSION
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id
where t1.id = :P15_ID;
end;
更新の際には、ROW_VERSIONの比較を行います。一致しないときはエラーにしています。
declare
l_row_version number;
expLostUpdate exception;
begin
select t1.row_version + t2.row_version into l_row_version
from tst_concjoin1 t1 join tst_concjoin2 t2
on t1.id = t2.id where t1.id = :P15_ID
for update nowait;
if l_row_version = :P15_ROW_VERSION then
update tst_concjoin1 set value1 = :P15_VALUE1, value11 = :P15_VALUE11 where id = :P15_ID;
update tst_concjoin2 set value2 = :P15_VALUE2, value22 = :P15_VALUE22 where id = :P15_ID;
else
raise expLostUpdate;
end if;
commit;
end;
標準コンポーネント以外による更新という意味では、REST APIについても同様にロストアップデートの防止の実装が必要な場合もあるかと思います。
今回作成したアプリケーションのエクスポートをこちらに置きました。
https://github.com/ujnak/apexapps/blob/master/exports/join-lostupdate.sql
ジョインした表のロストアップデートの防止についての説明は以上になります。Oracle APEXのアプリケーション開発の一助になれば幸いです。
完
