※ 本記事は、Ulrike Schwinn、Stephane Dupratによる”Lock-free reservation in 23c: how to start with“を翻訳したものです。
2023年5月26日
23cのロックフリー予約とは? また、どのように使用すればよいか? この投稿では、ロックフリー予約を使用する最初のステップについて説明します。
まず、考えられるユースケースについて説明します。通常、トランザクション処理には、古い値を新しい値に置き換えるデータ更新を含むトランザクションが含まれます。たとえば、アプリケーションが様々な状態で複数のトランザクションを処理するとします。ユーザーが品目をカートに入れると、その品目は他のバイヤーには使用できなくなり、売れ残りもなくなります。複数の同時トランザクションがカートにアイテムを追加し、カートをチェックアウトまたは破棄する場合、コミットまたはロールバックによって数量が変更される前に、トランザクションのフィールドをロックする必要があります。
長期間、データをロックすると、ロックが解除されるまで、他のコンカレント・トランザクションは品目にアクセスできなくなります。コンカレント・トランザクションにデータへのアクセスを許可する場合は、アプリケーションの正確性を維持するためにトランザクションを制御する必要があります。ただし、シリアライズでは、更新を開始したトランザクションが完了するまで、他のコンカレント・トランザクションによる行へのアクセスがブロックされます。
これを解決するにはどうすればよいでしょうか?
23cの新しい機能では、トランザクションに低・中優先度を割り当てることができるようになりましたが(ブログ記事「高、中および低優先度トランザクションによる23cでの自動トランザクション・ロールバック」も参照)、特定の待ち時間が経過した後に低優先度のセッションを終了させることしかできないため、役立ちません。
23cでのロックフリー予約はどうでしょうか? ロックフリー予約は、トランザクション用のデータベース内機能を提供し、いわゆる予約可能列に対して動作します。これにより、予約可能列に対する更新でブロックされることなく、同時トランザクションを続行できます。
一般的に、詳細は次の場所にあります。
動作の仕組みは? ロックフリー予約では、更新が続行される条件を指定して、トランザクションが互いにブロックすることなく、同じ行の予約可能列に同時に加算または減算を行えるようにします。これは、数値列がRESERVABLE列であることを指定し、その列のCHECK制約を作成することによって実現されます。予約可能列の更新は行をロックしないので、他のトランザクションが同じ行の非予約可能列を同時に更新するのをブロックすることはありません。
それでは、この機能を簡単な例で示して、その仕組みを理解しましょう。
まず、予約可能列 QTY_ON_HANDを含む表 INVENTORYを作成します。数値データ型のみがサポートされ、予約可能な列プロパティは主キーを持つ表の列に対してのみ指定できることに注意してください。
create table inventory
( item_id NUMBER CONSTRAINT inv_pk PRIMARY KEY,
item_display_name VARCHAR2(100) NOT NULL,
item_desc VARCHAR2(2000),
qty_on_hand NUMBER RESERVABLE CONSTRAINT qty_ck CHECK (qty_on_hand >= 0) NOT NULL,
shelf_capacity NUMBER NOT NULL,
CONSTRAINT shelf_ck CHECK (qty_on_hand <= shelf_capacity)
);
予約可能列はALTER TABLEコマンドを使用して表に追加することもでき、非予約可列はALTER TABLEコマンドを使用して予約可能列に変換することもでき、その逆も同様です。また、制約の使用はオプションです。トランザクションは、予約可能列に設定された制約に基づいて、数量を更新するのに十分なかどうかを決定します。この例では、QTY_ON_HANDの値が常に正であることを確認します。
予約可能表を削除する前に、予約可能列を非予約可能に変換する必要があります。そうしないと、次のエラーが表示されます。:
SQL> drop table inventory; drop table inventory * ERROR at line 1: ORA-55764: Cannot DROP or MOVE tables with reservable columns. First run "ALTER TABLE <table_name> MODIFY (<reservable_column_name> NOT RESERVABLE)" and then DROP or MOVE the table.
したがって、次のコマンドを実行する必要があります。:
SQL> alter table inventory modify (qty_on_hand NOT RESERVABLE); Table altered. SQL> drop table inventory; Table dropped.
『データベース開発ガイド 29.5 ロックフリー予約のガイドラインと制限事項』の制限に関する概要を確認できます。
予約可能列の作成と使用をどのように監視できますか。前述のように、表INVENTORYを再度作成したとします。
データ・ディクショナリ・ビュー USER_TABLES および USER_TAB_COLS を問い合せて、この機能の新しい列を調査します。
SQL> col table_name format a30 SQL> select table_name, has_reservable_column from user_tables where table_name = 'INVENTORY'; TABLE_NAME HAS ------------------------------ --- INVENTORY YES
SQL> select column_name, reservable_column from user_tab_cols where table_name = 'INVENTORY' and reservable_column = 'YES'; COLUMN_NAME RES ------------------------- --- QTY_ON_HAND YES
通常、制約の詳細は、USER_CONSTRAINTSを参照してください。:
SQL> col search_condition format a40 SQL> col constraint_name format a20 SQL> select constraint_name, search_condition from user_constraints where table_name='INVENTORY'; CONSTRAINT_NAME SEARCH_CONDITION -------------------- ---------------------------------------- SYS_C008386 "ITEM_DISPLAY_NAME" IS NOT NULL SYS_C008387 "QTY_ON_HAND" IS NOT NULL SYS_C008388 "SHELF_CAPACITY" IS NOT NULL QTY_CK qty_on_hand >= 0 SHELF_CK qty_on_hand <= shelf_capacity INV_PK 6 rows selected.
INVENTORY表を作成すると、関連する予約仕訳表も作成されます。この場合、SYS_RESERVJRNL_100639と呼ばれます(100639は表INVENTORYのオブジェクトIDです)。予約ジャーナル表は、ユーザー表と同じユーザー・スキーマおよびユーザー表と同じ表領域に作成されます。
USER_OBJECTS表を問い合せて、オブジェクトをリストし、2つの表(INVENTORYおよびジャーナル表SYS_RESERVJRNL_100639)の作成を確認します。
SQL> select object_name, object_type, created from user_objects order by 3 desc; OBJECT_NAME OBJECT_TYPE CREATED ---------------------------------------- ----------------------- --------- INV_PK INDEX 23-MAY-23 SYS_RESERVJRNL_100639 TABLE 23-MAY-23 INVENTORY TABLE 23-MAY-23 ...
この仕訳表の助けを借りて、ロックフリー予約を使用すると、トランザクションの実行中にデータベース内の予約可能なUPDATEを追跡できます。トランザクションが予約可能な更新を発行した予約仕訳表(ここSYS_RESERVJRNL_100639)から選択することで、トランザクションが独自のロックフリー予約を読み取ることができます。注意: 他のトランザクションによる予約は表示されません。
次の構造を確認します。:
SQL> desc SYS_RESERVJRNL_100094 Name Null? Type ----------------------------------------------------------------------------- -------- -------------------------------------------- ORA_SAGA_ID$ RAW(16) ORA_TXN_ID$ RAW(8) ORA_STATUS$ CHAR(12) ORA_STMT_TYPE$ CHAR(16) ITEM_ID NOT NULL NUMBER QTY_ON_HAND_OP CHAR(7) QTY_ON_HAND_RESERVED NUMBER
表に行を追加し、COMMITを実行します。:
insert into inventory values (123, 'Milk', 'Lowfat 2%', 100, 120); insert into inventory values (456, 'Bread', 'Multigrain', 50, 100); insert into inventory values (789, 'Eggs', 'Organic', 50, 75); commit;
最初にUPDATEを試行して、予約可能列の値を変更する必要があるルールに違反します。
SQL> update inventory set qty_on_hand=qty_on_hand; update inventory set qty_on_hand=qty_on_hand * ERROR at line 1: ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.
同じUPDATE文で予約可能列と非予約可能列の更新を混在させるなど、注意する必要がある制限がさらにあります。ルールおよび制限に関する完全な概要を取得する必要がある場合は、「データベース開発ガイド 29.5 ロックフリー予約のガイドラインと制限事項」を参照してください。
現在、表の内容は次のようになります。:
SQL> select item_id, qty_on_hand, shelf_capacity from inventory; ITEM_ID QTY_ON_HAND SHELF_CAPACITY ---------- ----------- -------------- 123 100 120 456 50 100 789 50 75
次に、INVENTORY表を更新し、制約に違反します。SHELF_CAPACITYの容量が不十分なために予約が失敗した場合、UPDATE文はCHECK制約違反で失敗します。
SQL> update inventory set qty_on_hand=qty_on_hand +100 where item_id=123; update inventory set qty_on_hand=qty_on_hand +100 where item_id=123 * ERROR at line 1: ORA-02290: check constraint (SCOTT.SHELF_CK) violated
または、UPDATEを実行して110の減算を試してみます。この場合、制約SCOTT.QTY_CKがチェックされ、次の制約エラーが発生します。:
SQL> update inventory set qty_on_hand = qty_on_hand - 110 where item_id = 123; 2 3 update inventory * ERROR at line 1: ORA-02290: check constraint (SCOTT.QTY_CK) violated
次のシナリオでは、2つのトランザクションでITEM_ID 123の予約可能列QTY_ON_HANDの値を変更します。今回は、チェック制約に違反しません。
予約可能列に対するUPDATEは、トランザクションのコミットまで行をロックしないことに注意してください。かわりに、予約可能列はロックフリー予約を提供します。
At time T1: トランザクション1
トランザクション1で2つのUPDATEを実行します。
SQL> update inventory
set qty_on_hand = qty_on_hand - 10
where item_id = 123;
1 row updated.
SQL> update inventory
set qty_on_hand = qty_on_hand - 20
where item_id = 123;
1 row updated.
トランザクションは、関連付けられた仕訳表(SYS_RESERVJRNL_100639)を検査することで、予約可能列に加えた変更を確認できます。
これを問い合せて、発行した2つのUPDATEをリストします。
SQL> set linesize window SQL> select * from SYS_RESERVJRNL_100639; ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED -------------------------------- ---------------- ------------ ---------------- ---------- ------- -------------------- 03001200CE340000 ACTIVE UPDATE 123 - 10 03001200CE340000 ACTIVE UPDATE 123 - 20
At time T2: トランザクション2
予約可能列は、ロックフリー予約を提供します。したがって、別のトランザクション2でUPDATEを実行できます。
SQL> update inventory
set qty_on_hand = qty_on_hand + 20
where item_id = 123;
1 row updated.
ジャーナル表SYS_RESERVJRNL_100639を再度確認します。
SQL> select * from SYS_RESERVJRNL_100639; ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_STMT_TYPE$ ITEM_ID QTY_ON_ QTY_ON_HAND_RESERVED -------------------------------- ---------------- ------------ ---------------- ---------- ------- -------------------- 0800010044340000 ACTIVE UPDATE 123 + 20
At time T3: トランザクション2
トランザクションがまだコミットされていないため、行の更新がまだ実行されていないことがわかります。
SQL> select item_id, qty_on_hand, shelf_capacity from inventory where item_id=123; ITEM_ID QTY_ON_HAND SHELF_CAPACITY ---------- ----------- -------------- 123 100 120
次に、ロックフリー予約がトランザクションのCOMMITの実際の更新に変換されるため、COMMITを実行します。
SQL> commit; Commit complete.
トランザクション2の変更が適用されます。
SQL> select item_id, qty_on_hand, shelf_capacity from inventory where item_id=123; ITEM_ID QTY_ON_HAND SHELF_CAPACITY ---------- ----------- -------------- 123 120 120
At time T4: トランザクション1
次に、トランザクション1でCOMMITを実行します。最後に、トランザクション1の変更が適用され、両方のトランザクションに表示されます。
SQL> commit; Commit complete. SQL> select item_id, qty_on_hand, shelf_capacity from inventory where item_id=123; ITEM_ID QTY_ON_HAND SHELF_CAPACITY ---------- ----------- -------------- 123 70 120
更新操作がコミットされると、ジャーナル表が再度空になります。仕訳表は内部使用専用です。したがって、ユーザーDMLおよびDDL操作は、予約ジャーナル表では許可されません。DMLを使用して予約仕訳表を作成または変更することはできません。また、SQLを使用して予約表の定義を削除、名前変更または変更することはできません。
これで最初のテスト・シナリオは終了です。
さらに詳しく