Oracle Database 23ai支持Lock-Free Reservation，中文通常译为“无锁列值保留”。
本文将通过3个部分来阐述Lock-Free Reservation的这个特性：
- 1.应用场景
- 2.实现原理
- 3.使用限制
1.应用场景
Lock-Free Reservation这项特性可用于实现更细粒度的并发控制。
它的本质是相对于传统的行锁，能以更细的粒度（即列值级别）进行锁定，从而减少锁争用，提高并发性能。
例如，当库存充足时，数据仅在提交时锁定，并有可能改善最终用户体验以及事务的吞吐量。
为了避免重复造轮子，本文演示的测试用例部分，直接参考了官方博客中给出的测试用例，原文链接为：
– https://blogs.oracle.com/coretec/post/lock-free-reservation-in-23c
下面我们就依据此测试用例来测试并理解下Lock-Free Reservation的具体功效吧。
1.1 创建测试表
首先，创建测试表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)
);
在开始下一步前，我们先解读下这张测试表的创建语句，以便后面测试能更好的理解：
- CHECK (qty_on_hand >= 0) 这个约束确保了库存数量不能为负数。
- shelf_capacity，这是货架容量，表示每种物品在货架上的最大存放量。
- CONSTRAINT shelf_ck CHECK (qty_on_hand <= shelf_capacity)，这个约束确保 qty_on_hand（当前库存数量）不能超过 shelf_capacity（货架容量）。
1.2 查看测试表信息
这里测试表的RESERVABLE属性列，还可以通过如下SQL来查看，确认表中是否存在RESERVABLE属性的列，以及确定具体是哪一列：
col table_name format a30
col has_reservable_column format a30
col reservable_column format a30
select table_name, has_reservable_column
from user_tables
where table_name = 'INVENTORY';
select column_name, reservable_column
from user_tab_cols
where table_name = 'INVENTORY' and reservable_column = 'YES';
查看表中约束信息：
col search_condition format a40
col constraint_name format a20
select constraint_name, search_condition
from user_constraints
where table_name='INVENTORY';
上面查询结果就是我们创建的表的基础信息:
07:35:47 PRIMARY @ORCL -> JINGYU @PDB1>
TABLE_NAME HAS_RESERVABLE_COLUMN
------------------------------ ------------------------------
INVENTORY YES
COLUMN_NAME RESERVABLE_COLUMN
------------------------------ ------------------------------
QTY_ON_HAND YES
CONSTRAINT_NAME SEARCH_CONDITION
-------------------- ----------------------------------------
SYS_C008423 "ITEM_DISPLAY_NAME" IS NOT NULL
SYS_C008424 "QTY_ON_HAND" IS NOT NULL
SYS_C008425 "SHELF_CAPACITY" IS NOT NULL
QTY_CK qty_on_hand >= 0
SHELF_CK qty_on_hand <= shelf_capacity
INV_PK
1.3 插入测试数据
在测试表中插入3条测试数据，并提交更改：
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;
2.实现原理
首先设计测试场景，然后从测试表现理解实现原理。
2.1 测试无锁列值保留
目前测试表中数据：
07:37:39 PRIMARY @ORCL -> JINGYU @PDB1> 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
Elapsed: 00:00:00.00
--更新item_id=123的qty_on_hand列，原值减10 @session1：
update inventory
set qty_on_hand = qty_on_hand - 10
where item_id = 123;
--更新item_id=123的qty_on_hand列，原值减2 @session2：
update inventory
set qty_on_hand = qty_on_hand - 2
where item_id = 123;
--更新item_id=123的qty_on_hand列，原值减9 @session3：
update inventory
set qty_on_hand = qty_on_hand - 9
where item_id = 123;
--更新item_id=123的qty_on_hand列，原值加20 @session4：
update inventory
set qty_on_hand = qty_on_hand + 20
where item_id = 123;
传统情况下，不同会话同时更新表的同一行数据，会阻塞，但这里上面4个会话都可以正常执行成功。
结果如下：
--@session1:
07:38:03 PRIMARY @ORCL -> JINGYU @PDB1> update inventory
set qty_on_hand = qty_on_hand - 10
where item_id = 123;
07:39:13 2 07:39:13 3
1 row updated.
Elapsed: 00:00:00.01
07:39:13 PRIMARY @ORCL -> JINGYU @PDB1>
--@session2:
07:39:01 PRIMARY @ORCL -> JINGYU @PDB1> update inventory
set qty_on_hand = qty_on_hand - 2
where item_id = 123;
07:39:29 2 07:39:29 3
1 row updated.
Elapsed: 00:00:00.01
07:39:29 PRIMARY @ORCL -> JINGYU @PDB1>
--@session3:
07:39:03 PRIMARY @ORCL -> JINGYU @PDB1> update inventory
set qty_on_hand = qty_on_hand - 9
where item_id = 123;07:39:38 2 07:39:38 3
1 row updated.
Elapsed: 00:00:00.01
--@session4:
07:39:07 PRIMARY @ORCL -> JINGYU @PDB1> update inventory
set qty_on_hand = qty_on_hand + 20
where item_id = 123;07:39:45 2 07:39:45 3
1 row updated.
Elapsed: 00:00:00.01
4个会话更新同一行数据，完全不受影响，实现了并发的新高度，即比行锁更细的颗粒度。
2.2 查看journal table
查看journal table，这个是核心，也是Lock-Free Reservation的底层实现机制：
select object_name, object_type, created
from user_objects order by 3 desc;
结果如下：
OBJECT_NAME OBJECT_TYPE CREATED
------------------------------ ----------------------- ---------
INV_PK INDEX 12-JUN-24
SYS_RESERVJRNL_76171 TABLE 12-JUN-24
INVENTORY TABLE 12-JUN-24
查看这个journal table的表结构：
07:42:53 PRIMARY @ORCL -> JINGYU @PDB1> desc SYS_RESERVJRNL_76171
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
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
07:42:58 PRIMARY @ORCL -> JINGYU @PDB1>
select * from SYS_RESERVJRNL_76171;
分别在上面4个会话查询，结果如下：
--@session1:
07:42:58 PRIMARY @ORCL -> JINGYU @PDB1> select * from SYS_RESERVJRNL_76171;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_ST ITEM_ID Q QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ----------- ------ ---------- - --------------------
05001B0068040000 ACTIVE UPDATE 123 - 10
Elapsed: 00:00:00.00
--@session2:
07:39:30 PRIMARY @ORCL -> JINGYU @PDB1> select * from SYS_RESERVJRNL_76171;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_ST ITEM_ID Q QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ----------- ------ ---------- - --------------------
02000B0050040000 ACTIVE UPDATE 123 - 2
Elapsed: 00:00:00.00
--@session3:
07:39:39 PRIMARY @ORCL -> JINGYU @PDB1> select * from SYS_RESERVJRNL_76171;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_ST ITEM_ID Q QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ----------- ------ ---------- - --------------------
0A000A00B3030000 ACTIVE UPDATE 123 - 9
Elapsed: 00:00:00.00
--@session4:
07:39:46 PRIMARY @ORCL -> JINGYU @PDB1> select * from SYS_RESERVJRNL_76171;
ORA_SAGA_ID$ ORA_TXN_ID$ ORA_STATUS$ ORA_ST ITEM_ID Q QTY_ON_HAND_RESERVED
-------------------------------- ---------------- ----------- ------ ---------- - --------------------
04000E00B2030000 ACTIVE UPDATE 123 + 20
Elapsed: 00:00:00.00
可以看到这个journal table的表SYS_RESERVJRNL_76171详细记录了每个会话对这列的操作。
一旦提交，这个记录就会被清空。有点儿像Oracle的临时表？顺手获取下这个表的创建语句：
select dbms_metadata.get_ddl(‘TABLE’,’SYS_RESERVJRNL_76171′,’JINGYU’) from dual;
07:48:49 PRIMARY @ORCL -> JINGYU @PDB1> select dbms_metadata.get_ddl('TABLE','SYS_RESERVJRNL_76171','JINGYU') from dual;
DBMS_METADATA.GET_DDL('TABLE','SYS_RESERVJRNL_76171','JINGYU')
--------------------------------------------------------------------------------
CREATE TABLE "JINGYU"."SYS_RESERVJRNL_76171"
( "ORA_SAGA_ID$" RAW(16),
"ORA_TXN_ID$" RAW(8),
"ORA_STATUS$" VARCHAR2(11),
"ORA_STMT_TYPE$" VARCHAR2(6),
"ITEM_ID" NUMBER NOT NULL ENABLE,
"QTY_ON_HAND_OP" VARCHAR2(1),
"QTY_ON_HAND_RESERVED" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
从创建语句来看倒没有指定临时表创建语句，但感觉上实现机制上有些类似，以后空了再研究，不是很重要。
总之这个日志表是建立无锁列值保留时，Oracle自动创建的，当然也由Oracle自己维护，用户肯定不能对其直接进行操作，否则会报错：
ORA-55727: DML, ALTER, RENAME, and CREATE INDEX operations are not allowed on the reservation journal table "JINGYU"."SYS_RESERVJRNL_76171".
Help: https://docs.oracle.com/error-help/db/ora-55727/
3.使用限制
最后聊下关于Lock-Free Reservation的目前使用限制：
– 该特性仅限于特定场景，主要是经常需要更新特定列而非整行的场景。
– 更新特定列也不能随便，只能使用原值增加或减少的方式。
– 支持的数据类型有限：仅支持数值型数据列，不适用于所有数据类型。
– 只在23ai数据库版本中提供支持。
3.1 修改非特定列肯定不行
这好像是废话。。。但还是提一句，可不要傻傻的认为表中的所有列都可以。
--@session1:
update inventory
set item_display_name = 'XXBK'
where item_id = 123;
--@session2:
update inventory
set item_display_name = 'ABCD'
where item_id = 123;
比如上面更新这个表的其他列时，session2肯定会等待session1提交或回滚之后才能操作成功。
3.2 修改特定列也有特定限制
特定列就可以随便更新了吗？目前也不是的，只能支持特定的场景，比如使用原值增加或减少的方式。
--@session1:
update inventory
set qty_on_hand = 30
where item_id = 123;
--@session2:
update inventory
set qty_on_hand = 40
where item_id = 123;
上面这种直接更新这个特定列的值，也是会报错：
ORA-55746: Reservable column update statement only supports + or - operations on a reservable column.
Help: https://docs.oracle.com/error-help/db/ora-55746/
3.3 不支持非数值型
如果你定义了非数值型的保留列，建表就会直接报错，明确提醒你只支持NUMBER, INTEGER, FLOAT这些数据类型：
ORA-55748: Reservable column property specified on column "QTY_ON_HAND" is supported only on columns of data types Oracle NUMBER, INTEGER, and FLOAT.
Help: https://docs.oracle.com/error-help/db/ora-55748/
3.4 修改表中列的RESERVABLE属性
如果你最终发现，你的业务根本不需要列的RESERVABLE属性，那么就可以修改列属性，去掉RESERVABLE属性：
alter table inventory modify (qty_on_hand NOT RESERVABLE);
这样，这个列就会支持常规更新操作，但同时，就会和普通列一样持有锁：
--@session1:
update inventory
set qty_on_hand = 30
where item_id = 123;
--@session2:
update inventory
set qty_on_hand = 40
where item_id = 123;
3.5 测试环境清理
最后测试回退相关操作，删除测试表：
drop table inventory;
好了，有关Oracle Database 23ai支持Lock-Free Reservation特性实现并发新高度的测试就到这里了。大家可以想想自己公司的业务中是否有合适的场景可以通过这个特性大幅增加并发能力，欢迎一起讨论。
