LoginSignup
5
2
@asahide

【Oracle23c 新機能】ロックフリー予約を試してみた

Posted at

はじめに

Oracle 23c の新機能で、ロックフリー予約という機能が追加されました。個人的には聞きなれない用語であり、興味を持ったので試してみました。

オラクル社によるロックフリー予約の紹介記事はこちらです。

ロックフリー予約とは

マニュアル によると「ロックフリー予約では、数値列の値に対して加算または減算を実行するときに、コミットされていない更新によって阻止されることがなく、その列値に対する複数の更新を同時に進めることができます。」となっています、よくわかりませんね。
通常、行に対するロックは更新等でトランザクションが開始されたタイミングで取得されますが、ロックフリー予約の機能を利用するとコミットされるタイミングでのみ該当行へのロックが発生する仕組みになるようです。

とりあえず、どのような動作になるのか、実際の23cを利用して確認してみました。

ロックフリー予約表を作成してみた

正確には、ロックフリー予約の機能を持った列を含む表を作成します。
こちらのblog と同様の表を作成してみます。

inventory表は制約により、qty_on_hand列の値が 0以上 且つ shelf_capacity以下 である必要があります。
ここで、チェック制約自体の整合性はチェックされないので作成時に確認する必要があるようです。

ロックフリー予約表の作成
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)
 );
表が作成されました。

blogではこのRESERVABLE属性がついた表は削除はできない、となってますが、23.3の環境では削除できます。

ロックフリー予約表の削除
SQL> drop table inventory;
表が削除されました。

再度作成し、inventory表の属性を確認します。

inventory表の属性
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

SQL> select constraint_name, search_condition from user_constraints where table_name='INVENTORY';
CONSTRAINT_NAME      SEARCH_CONDITION
-------------------- ----------------------------------------
SYS_C0012871	     "ITEM_DISPLAY_NAME" IS NOT NULL
SYS_C0012872	     "QTY_ON_HAND" IS NOT NULL
SYS_C0012873	     "SHELF_CAPACITY" IS NOT NULL
QTY_CK		         qty_on_hand >= 0
SHELF_CK	         qty_on_hand <= shelf_capacity
INV_PK

また、ロックフリー予約表を作成したタイミングで予約ジャーナル表も作成されています。
ここで、予約ジャーナル表にはinventory表のPKのitem_id、RESERVABLE属性のついた列でえあるqty_on_handに関連した列が作成されていることがわかります。

reservation journal表の確認
SQL> select object_name, object_type from user_objects;
OBJECT_NAME		       OBJECT_TYPE
--------------------   -----------
SYS_RESERVJRNL_76463   TABLE
INVENTORY		       TABLE
INV_PK			       INDEX

SQL> desc SYS_RESERVJRNL_76463
名前                                       NULL?    型
----------------------------------------- -------- ----------------------------
ORA_SAGA_ID$					                   RAW(16)
ORA_TXN_ID$					                       RAW(8)
ORA_STATUS$					                       VARCHAR2(11)
ORA_STMT_TYPE$ 				                       VARCHAR2(6)
ITEM_ID				                      NOT NULL NUMBER
QTY_ON_HAND_OP 				                       VARCHAR2(1)
QTY_ON_HAND_RESERVED				               NUMBER

ロックフリー予約表にトランザクションを実行してみた

この予約ジャーナル表の役割は、ジャーナルの名前の通り、inventory表のqty_on_hand列のトランザクションを記録します。以下、見ていきましょう。

最初の状態
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

最初の状態では inventory 表の ITEM_DI=123の行のQTY‗ON_HAND列は 100 です。
該当の行をアップデートします。

トランザクション１
SQL>  update inventory set qty_on_hand = qty_on_hand - 10 where item_id = 123;
1行が更新されました。

SQL> update inventory set qty_on_hand = qty_on_hand - 20 where item_id = 123;
1行が更新されました。

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

ちょっと面白い結果になりました、QTY_ON_HAND列は同一トランザクション内では変更後の値になるはずですが、更新前と同じ 100 となっています。

一方で実行中の予約ジャーナル表の中身は以下のようになり、トランザクション１の中身が記録されています。

トランザクション１の予約ジャーナル表
SQL> select * from SYS_RESERVJRNL_76463;
ORA_SAGA_ID$	 ORA_TXN_ID$	  ORA_STATUS$ ORA_ST	ITEM_ID Q QTY_ON_HAND_RESERVED
---------------- ---------------- ----------- ------ ---------- - --------------------
				 07001E00C1020000 ACTIVE      UPDATE	    123 -		            10
				 07001E00C1020000 ACTIVE      UPDATE	    123 -		            20

ここで、別のターミナルから新たなトランザクション（トランザクション２）をinventory表に対して実行してみます。こちらも実行前の状態では当然未コミットのトランザクション１の途中経過は見えません。
SYS_RESERVJRNL_76463 表についても、トランザクション１の内容は見えません。

トランザクション２
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

SQL> update inventor set qty_on_hand = qty_on_hand + 20 where item_id = 123;
1行が更新されました。

SQL> select * from SYS_RESERVJRNL_76463;
ORA_SAGA_ID$	ORA_TXN_ID$	     ORA_STATUS$ ORA_ST	ITEM_ID    Q QTY_ON_HAND_RESERVED
--------------- ---------------- ----------- ------ ---------- - --------------------
                01000600AF020000 ACTIVE      UPDATE	       123 +	       	       20

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すると、トランザクション２の予約ジャーナル表が空になり、また、当然ですがinventory表にはトランザクション２の +20 が反映され、120となります。

トランザクション２のcommit
SQL> commit;
コミットが完了しました。

SQL> select * from SYS_RESERVJRNL_76463;
レコードが選択されませんでした。

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

このタイミングでトランザクション１を確認すると、トランザクション２のcommit情報が反映され、こちらも120となります。

トランザクション２のcommit後のトランザクション１
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

トランザクション１をcommitすると、-20、-10の結果が反映されて 90 となります。
また、こちらの予約ジャーナル表も空になっていることがわかります。

トランザクション１のcommit
SQL> commit;
コミットが完了しました。

SQL> select item_id, qty_on_hand, shelf_capacity from inventory where item_id=123;
   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123	        90		      120

SQL> select * from SYS_RESERVJRNL_76463;
レコードが選択されませんでした。

まとめ

Oracle社のblog （23cでのロックフリー予約: 開始方法）をなぞっただけですが、従来のトランザクションの動きとは違うことは確認できました。逆に従来の動きを知っている人はupdateを実行したセッションのselect結果が変わらないことに違和感を感じるのではないかと思います。
今まではこのようなロックを回避するためにアプリ側で制御してロックの保持期間を短くするようなこともあったと思いますが、この機能を使えばその辺を意識せずにロックの保持期間を極小にすることができるようになります。

ご自身のアプリでぜひ試してみて頂ければと考えてます。

また、もう少し動きを確認するためには、複数の予約可能テーブルに対して複数のトランザクションをかけてみたり、途中でSHELF_CAPACITYに変更をかけてみたり、v\$lock、v\$locked_objectを眺めてみたりすると面白いかと思いました。

おまけ１

予約フリー表へのトランザクション実行時のSQLトレース結果を見てみると、inventory表へのupdateの他に内部で予約ジャーナル表へのselect、insertが発生していることが確認できました。

inventory表へのupdate文（抜粋）
update inventory set qty_on_hand = qty_on_hand - 20 
where
 item_id = 123

Rows     Execution Plan
-------  ---------------------------------------------------
      0  UPDATE STATEMENT   MODE: ALL_ROWS
      0   UPDATE OF 'INVENTORY'
      1    INDEX (UNIQUE SCAN) OF 'INV_PK' (INDEX (UNIQUE))
予約ジャーナル表へのselect文（抜粋）
SELECT NVL(((select NVL(sum(QTY_ON_HAND_RESERVED), 0) 
from
 SYS_RESERVJRNL_76463 where (ORA_STATUS$ = 'ACTIVE' or ORA_STATUS$ = 'HOLD') 
  and QTY_ON_HAND_OP = '+' and  ORA_TXN_ID$ = :TXID and ITEM_ID = :VAL1 ) - 
  (select NVL(sum(QTY_ON_HAND_RESERVED), 0) from SYS_RESERVJRNL_76463 where 
  (ORA_STATUS$ = 'ACTIVE' or ORA_STATUS$ = 'HOLD') and QTY_ON_HAND_OP = '-' 
  and ITEM_ID = :VAL1 )), 0) as curr_reserv from dual

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   SORT (AGGREGATE)
      0    TABLE ACCESS (FULL) OF 'SYS_RESERVJRNL_76463' (TABLE)
      1   SORT (AGGREGATE)
      0    TABLE ACCESS (FULL) OF 'SYS_RESERVJRNL_76463' (TABLE)
      1   FAST DUAL

insert文のEXECUTION PLANは内部表へのアクセスで失敗していたため、Row Source Operation部分を載せてます。

予約ジャーナル表へのinsert文（抜粋）
INSERT INTO "SYS_RESERVJRNL_76463" (ORA_SAGA_ID$, ORA_TXN_ID$, ORA_STATUS$, 
  ORA_STMT_TYPE$, "QTY_ON_HAND_OP", "QTY_ON_HAND_RESERVED", "ITEM_ID") 
VALUES
 (:1, :2, :3, :4, :5, :6, :7)

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  SYS_RESERVJRNL_76463 (cr=1 pr=0 pw=0 time=149 us starts=1 direct read=0 direct write=0)

おまけ２

ちなみにロックフリー予約ではない表に対して同様のトランザクションをかけたときにはどうなるでしょうか？同じ表定義でロックフリー予約列がない表を作成し、同様のデータを入れます。

従来型の表の作成
SQL> create table inventory2
  2  ( item_id            NUMBER          CONSTRAINT inv_pk2 PRIMARY KEY,
  3    item_display_name  VARCHAR2(100)   NOT NULL,
  4    item_desc          VARCHAR2(2000),
  5    qty_on_hand        NUMBER          CONSTRAINT qty_ck2 
  6                                       CHECK (qty_on_hand >= 0) NOT NULL,
  7    shelf_capacity     NUMBER          NOT NULL,
  8      CONSTRAINT shelf_ck2 CHECK (qty_on_hand <= shelf_capacity)
  9  );
表が作成されました。

SQL> insert into inventory2 values (123, 'Milk', 'Lowfat 2%', 100, 120);
1行が作成されました。

SQL> commit;
コミットが完了しました。

SQL> select item_id, qty_on_hand, shelf_capacity  from inventory2;
   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123	       100		      120

inventory2表に更新をかけます。トランザクション１ではcommit前のタイミングで QTY_ON_HAND列への更新が反映されます。

トランザクション１
SQL> update inventory2 set qty_on_hand = qty_on_hand - 20 where item_id = 123;
1行が更新されました。

SQL> update inventory2 set qty_on_hand = qty_on_hand - 10 where item_id = 123;
1行が更新されました。

SQL> select item_id, qty_on_hand, shelf_capacity  from inventory2;
   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123	        70		      120

次に、別のセッションからは未commitのデータは参照できないため、当然QTY_ON_HAND列は100のままです。また、inventory2表に更新をかけると、当然ですがこのタイミングでロック待ちとなります。

トランザクション２
SQL> select item_id, qty_on_hand, shelf_capacity  from inventory2;
   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123	       100	    	  120

SQL> update inventory2 set qty_on_hand = qty_on_hand + 20 where item_id = 123;

トランザクション１をcommitします。同時に、トランザクション２のupdateが返ります。

トランザクション１をcommit
SQL> commit;
コミットが完了しました。

SQL> select item_id, qty_on_hand, shelf_capacity from inventory2;
   ITEM_ID QTY_ON_HAND SHELF_CAPACITY
---------- ----------- --------------
       123	        70		      120
トランザクション２
1行が更新されました。

トランザクション２側（+20）をコミットすると、90になります、当たり前すぎますね。。

おまけ３

今回QTY_ON_HANDは最後 90 となりましたが、同じupdateを実行しているblogの23cでのロックフリー予約: 開始方法ですと最後何故か 70 で終わってます。英語版のblog Lock-free reservation in 23c: how to start with では最初の100に対してトランザクション１が -50、トランザクション２が +20 なので、この結果をそのままコピーしてしまったものと思われます。。

参考

より詳しく動作を確認したい方は、多分このマニュアルが一番詳しいと思いますので、この辺からどうぞ。

5
2
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
2