Oracle Database 23cのロックフリー予約を有効にした列に更新をしてみた

# はじめに
Oracle Database 23cの新機能にロックフリー予約（Lock-Free Reservations)という機能があります。こちらのblogで紹介されている機能です。
+ [23cでのロックフリー予約: 開始方法](https://blogs.oracle.com/oracle4engineer/post/ja-lock-free-reservation-in-23c)
+ [23cでのロックフリー予約: アプリのスケーリング](https://blogs.oracle.com/oracle4engineer/post/ja-lockfree-reservation-in-23c-scale-your-apps)

在庫管理や座席管理などのシステムなどでの在庫数や座席数といった大量アクセスで同時に更新が求められる数値列の排他制御と同時トランザクション実行性の両立に効果がある機能です。どういうふうに見えるのか動作を試してみました。
# 1.環境構築
[こちらの記事](https://qiita.com/shirok/items/4cd7864e29b8888ca866)を参考にBase Databaseを作成し、PDBに以下のユーザーを作成しました。
``` sql
-- SYSTEMユーザで実行
connect system/WelCome12345##@ipaddress:1521/pdb1.public1.vcn1.oraclevcn.com
grant connect,resource to testuser identified by "WelCome12345##";
grant unlimited tablespace to testuser;
```
# 2.ロックフリー予約の予約可能列を指定
表の作成時(CREATE TABLE)や変更時(ALTER TABLE)に更新対象となる列に**RESERVABLE**句を指定することで、ロックフリー予約の予約可能列にすることができます。
以下のSQLではBalance列を予約可能列に指定しています。さらにチェック制約で50を下回らないように設定しています。
``` SQL
-- TESTUSERユーザで実行
CREATE TABLE Account( ID NUMBER PRIMARY KEY,
 Name VARCHAR2(10),
 Balance NUMBER reservable CONSTRAINT minimum_balance CHECK (Balance >= 50));

insert into Account values (1,'SMITH','100');
insert into Account values (2,'ALLEN','60');
insert into Account values (3,'WARD','75');
commit;
```
ディクショナリビューXXX_TABLESやXXX_TAB_COLS/XXX_TAB_COLUMESで予約可能列があるかどうか、どの列が予約可能列かを確認することができます。以下はUSER_TABLES、USER_TAB_COLSで検索しています。

``` sql
-- TESTUSERユーザで実行
SQL> SELECT table_name, has_reservable_column FROM user_tables
  2  WHERE  table_name = 'ACCOUNT';

TABLE_NAME           HAS
-------------------- ---
ACCOUNT              YES

SQL> SELECT column_name, reservable_column FROM   user_tab_cols
  2  WHERE  table_name = 'ACCOUNT' AND  reservable_column = 'YES';

COLUMN_NAME          RES
-------------------- ---
BALANCE              YES
```

USER_OBJECTSで見ると表と表で指定した主キーのINDEXのほかにSYS_RESERVJRNLで始まる表が作成されていました。この表はロックフリー予約の予約可能列を持つ表を作成したときに自動的に作成されるジャーナルです。ジャーナル表の名前は**SYS_RESERVJRNL_表のOBJECT_ID**となります。
``` sql
-- TESTUSERユーザで実行
SQL> select object_id,object_name,object_type from user_objects;

 OBJECT_ID OBJECT_NAME          OBJECT_TYPE
---------- -------------------- -----------------------
     71951 ACCOUNT              TABLE
     71952 SYS_RESERVJRNL_71951 TABLE
     71953 SYS_C008291          INDEX

SQL> desc SYS_RESERVJRNL_71951
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_SAGA_ID$                                       RAW(16)
 ORA_TXN_ID$                                        RAW(8)
 ORA_STATUS$                                        VARCHAR2(11)
 ORA_STMT_TYPE$                                     VARCHAR2(6)
 ID                                        NOT NULL NUMBER
 BALANCE_OP                                         VARCHAR2(1)
 BALANCE_RESERVED                                   NUMBER
```

# 3.ロックフリー予約の予約可能列を更新すると、データ、ジャーナル表はどう見えるのか？
IDごとにBalance列を異なる内容で更新して、ジャーナル表の情報を確認してみます。

```sql
-- session 1
-- TESTUSERユーザーで実行
SQL> select * from account;

        ID NAME          BALANCE
---------- ---------- ----------
         1 SMITH             100
         2 ALLEN              60
         3 WARD               75

SQL> UPDATE account SET balance = balance - 10 WHERE  id = 1;

1 row updated.

SQL> UPDATE account SET balance = balance - 25 WHERE  id = 2;
UPDATE account SET balance = balance - 25 WHERE  id = 2
*
ERROR at line 1:
ORA-02290: check constraint (TESTUSER.MINIMUM_BALANCE) violated


SQL> UPDATE account SET balance = balance + 35 WHERE  id = 3;

1 row updated.

SQL> select * from account;

        ID NAME          BALANCE
---------- ---------- ----------
         1 SMITH             100
         2 ALLEN              60
         3 WARD               75

```
ID=2を条件にした更新はORA-2290になりました。BALANCE60から25を引くと、35になり、チェック制約の>=50に違反するためです。
更新後の検索結果は、更新前の検索結果と変わりません。更新の内容はジャーナル表で確認することができます。
``` sql
-- session 1
-- TESTUSERユーザで実行
SQL> select * from  SYS_RESERVJRNL_71951;

ORA_SAGA_ID$    ORA_TXN_ID$      ORA_STATUS$ ORA_ST         ID B BALANCE_RESERVED
--------------- ---------------- ----------- ------ ---------- - ----------------
                01001C006C020000 ACTIVE      UPDATE          1 -               10
                01001C006C020000 ACTIVE      UPDATE          3 +               35
```

ロックフリー予約の予約可能列を更新すると、データ、ジャーナル表はどう見えるのか？の回答としては、データは更新前と変わらない、ジャーナル表に各レコード別で更新内容が登録される、となりそうです。

ちなみに引き算や足し算以外だとどうなるかというとエラーになります。
```sql
SQL> UPDATE account SET balance = balance * 2  WHERE  id = 1;
 UPDATE account SET balance = balance * 2  WHERE  id = 1
        *
ERROR at line 1:
ORA-55746: Reservable column update statement only supports + or - operations
on a reservable column.
Help: https://docs.oracle.com/error-help/db/ora-55746/
```

# 4.別セッションからはデータ、ジャーナル表はどう見えるのか？
コミットをしていないので、別のセッションから参照できるデータは、もちろんsession1の更新前のデータのはずです。ジャーナル表のsession 1の更新内容はどうでしょうか。別のsession 2で確認してみます。

``` sql
-- session 2
-- TESTUSERユーザで実行
SQL> select * from account;

        ID NAME          BALANCE
---------- ---------- ----------
         1 SMITH             100
         2 ALLEN              60
         3 WARD               75

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

no rows selected
```
データは想定通り更新前のデータでしたが、ジャーナル表にsession 1の更新内容は見えませんでした。
続けてsession 1で更新済みのID=1のデータをsession 2でも更新してみます。
```sql
-- session 2
-- TESTUSERユーザで実行
SQL> UPDATE account SET balance = balance -10 WHERE id = 1;

1 row updated.

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

        ID B BALANCE_RESERVED
---------- - ----------------
         1 -               10
```
更新ができ、ジャーナル表にはsession 2で更新した内容が入りました。
ジャーナル表の更新内容はその更新を実行したセッションでしか見えないようです。

::: note
ロックフリー予約でない場合、同じ行への更新は、ロックを取るまで待機になりますが、ロックフリー予約であればこのように同時に同じ行に更新ができます。
:::

別セッションからはデータ、ジャーナル表はどう見えるのか？については、データは更新前のデータ、ジャーナル表は自分が更新したセッションしか見えない、となりました。

# 5.別セッションで行われたロックフリー予約列の更新内容は認識されているのか？
別のセッションでロックフリー予約列を更新した場合、その更新内容を踏まえた更新がされるのでしょうか。
再度session 1でID=1のデータを更新してみます。
```sql
SQL> select * from account;

        ID NAME          BALANCE
---------- ---------- ----------
         1 SMITH             100
         2 ALLEN              60
         3 WARD               75

SQL> UPDATE account SET balance = balance -15  WHERE  id = 1;

1 row updated.

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

        ID B BALANCE_RESERVED
---------- - ----------------
         1 -               10
         3 +               35
         1 -               15

SQL> UPDATE account SET balance = balance -20  WHERE  id = 1
UPDATE account SET balance = balance -20  WHERE  id = 1
*
ERROR at line 1:
ORA-02290: check constraint (TESTUSER.MINIMUM_BALANCE) violated
```
1つ目の-15の更新は成功しましたが、2つ目の更新はORA-2290になりました。
ジャーナル表からわかるように、session 1のトランザクションだけでは、ID=1のデータは(100 - 10 -15 -20) >=50であるため、ORA-2290にならないはずです。そのため、session 2の更新 -10が影響し、50を下回ったと考えられます。
session 2でロールバックを実行すれば、エラーなく更新できるのではないでしょうか。試してみます。

``` sql
-- session 2
-- TESTUSERユーザで実行

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

        ID B BALANCE_RESERVED
---------- - ----------------
         1 -               10

SQL> rollback;

Rollback complete.

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

no rows selected
``` 
session 2でロールバックを実行すると、ジャーナル表の内容が削除されました。
改めて、session 1でORA-2290のエラーになったUPDATE文を実行してみます。
```sql
-- session 1
-- TESTUSERユーザで実行
SQL> UPDATE account SET balance = balance -20  WHERE  id = 1
  2  ;

1 row updated.

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

        ID B BALANCE_RESERVED
---------- - ----------------
         1 -               10
         3 +               35
         1 -               15
         1 -               20

SQL> commit;

Commit complete.

SQL> select id,balance_op,balance_reserved from SYS_RESERVJRNL_71951;

no rows selected
```
更新を行うことができました。このことから更新は他のセッションの更新内容もふまえて実行されるということになりそうです。
コミットをするとロールバック同様ジャーナル表の内容が削除されるということも確認できました。
# 6 コミット時に行ロックは取られるのか？
コミットするとベース表に更新が行われるので、行ロックは取得されると思います。確認するため、別のセッションで条件は同じでロックフリー予約列ではない列を更新しました。

```sql
-- session 1
TESTUSERで実行
UPDATE account SET name='TOM'where id=1;

1 row updated.
```
次にこの状態でロックフリー予約列を別セッションで更新してコミットをすると、コミットが待機になりました。

```sql
-- session 2
-- TESTUSERで実行
SQL> UPDATE account SET balance = balance +10 WHERE  id = 1;

1 row updated.

commit;
<待機>
```
v$sessionディクショナリで情報を確認してみます。
session 1がSID185でsession 2が503なのですが、503が行ロック待機で待っていることがわかりました。

```sql
SQL> select SID, EVENT, SECONDS_IN_WAIT, BLOCKING_SESSION from v$session where event like '%enq%'

       SID EVENT                                    SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------------------------------------- --------------- ----------------
       185 enq: TX - row lock (HIGH priority)                    12              503
```
通常の列と同じようにコミットのときには行ロックを取得しに行くことがわかりました。
    
# おわりに
ロックフリー予約で気になったところを確認してみました。

# 参考資料
[Oracle Developent Guide "28 Using Lock-Free Reservation"](https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/using-lock-free-reservation.html#GUID-60D87F8F-AD9B-40A6-BB3C-193FFF0E60BB)