我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

本文转自朋友的真实案例分享。

学习记录：23ai新特性：Priority Transactions

从23ai开始，oracle通过事务优先级实现了一种自动回滚的事务机制。

Starting with Oracle Database 23ai, the database provides parameters to control when and which transactions holding rowlocks can be automatically rolled back. Oracle database rolls back the transaction but the session stays alive. The application must acknowledge the automatic rollback of the transaction by issuing a ROLLBACK SQL statement.

Applications can specify the priority of their transactions. If a low priority transaction blocks a high priority transaction on rowlocks, Oracle database will automatically roll back the low priority transaction to let the high priority transaction(s) progress.

The database administrator can configure the time after which the low priority transaction is rolled back.

Note that if a transaction is holding a rowlock and not blocking any transaction, such a transaction is never rolled back.

具体是如何实现事务优先级其实是通过一系列的控制参数实现的。

SQL> @sp priority%txn
 
-- show parameter by sp
 
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
priority_txns_high_wait_target       integer    2147483647
priority_txns_medium_wait_target     integer    2147483647
priority_txns_mode                   string     ROLLBACK
 
-- show hidden parameter by sp
old   3: where x.indx=y.indx and ksppinm like '_%&p%'
new   3: where x.indx=y.indx and ksppinm like '_%priority%txn%'
 
no rows selected
 
SQL> @sp txn%pri
 
-- show parameter by sp
 
NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
txn_auto_rollback_high_priority_wait integer    2147483647
_target
txn_auto_rollback_medium_priority_wa integer    2147483647
it_target
txn_priority                         string     HIGH

应用程序可以根据事务重要性通过alter session命令修改参数txn_priority来设置事务优先级，txn_priority有3个选项(默认HIGH)：

LOW：If a LOW priority transaction is blocked for a row lock, Oracle database will not attempt to roll back the transaction holding the row lock irrespective of its priority.
MEDIUM：If a MEDIUM priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW priority.
HIGH：If a HIGH priority transaction is blocked for a row lock, Oracle database can roll back the transaction that is holding the row lock only if the holder is LOW or MEDIUM priority.
Oracle database never rolls back a HIGH priority transaction.

priority_txns_high_wait_target和priority_txns_medium_wait_target控制不同事务优先级的等待时间单位为秒

priority_txns_high_wait_target：specifies the maximum number of seconds that a HIGH priority transaction will wait for a row lock before the Priority Transactions feature rolls back a lower priority transaction holding the lock
priority_txns_medium_wait_target：specifies the maximum number of seconds that a MEDIUM priority transaction will wait for a row lock before the Priority Transactions feature rolls back a lower priority transaction holding the lock.
SQL> alter system set priority_txns_high_wait_target=5;
 
System altered.
 
SQL> alter system set priority_txns_medium_wait_target=10;
 
System altered.
测试验证demo：

session 1：

SQL> alter session set txn_priority=low;
 
Session altered.
 
 
SQL> delete from test.t where data_object_id is null;
 
63748 rows deleted.
session 2：

SQL> delete from test.t where data_object_id is null;
验证结果：

session 2在等待priority_txns_high_wait_target时间后，session 1的事务被自动回滚，但是session继续保留。alert中将打印自动终止事务的相关信息

VASTDATA(3):Transaction (sid: 281, serial: 52687, xid: 6.3.1066, txn_priority: "LOW") terminated by transaction (sid: 403, serial: 9549, xid: xid not available, txn_priority: "HIGH") because of the parameter "priority_txns_high_wait_target = 5".
session 1必须手动回滚才能继续后续操作

SQL> select xid,status,txn_priority,priority_txns_wait_target from  v$transaction;
select xid,status,txn_priority,priority_txns_wait_target from  v$transaction
*
ERROR at line 1:
ORA-63302: Transaction must roll back
ORA-63300: Transaction is automatically rolled back since it is blocking a
higher priority transaction from another session.
Help: https://docs.oracle.com/error-help/db/ora-63302/
 
 
SQL> select xid,status,txn_priority,priority_txns_wait_target from  v$transaction;
select xid,status,txn_priority,priority_txns_wait_target from  v$transaction
*
ERROR at line 1:
ORA-63302: Transaction must roll back
Help: https://docs.oracle.com/error-help/db/ora-63302/
txn_auto_rollback_high_priority_wait_target和txn_auto_rollback_medium_priority_wait_target应该只是开发用的参数，设置并无作用

SQL> alter system set priority_txns_high_wait_target=20;
 
System altered.
 
SQL> alter system set TXN_AUTO_ROLLBACK_HIGH_PRIORITY_WAIT_TARGET=1;
 
System altered.
最终生效的是priority_txns_high_wait_target

VASTDATA(3):Transaction (sid: 281, serial: 52022, xid: 2.27.1034, txn_priority: "LOW") terminated by transaction (sid: 403, serial: 57553, xid: xid not available, txn_priority: "HIGH") because of the parameter "priority_txns_high_wait_target = 20".
此外在event、sysstat、v$transaction都增加了事务优先级的相关信息

SQL> select xid,status,txn_priority,priority_txns_wait_target from  v$transaction;
 
XID              STATUS           TXN_PRI PRIORITY_TXNS_WAIT_TARGET
---------------- ---------------- ------- -------------------------
01000D0013040000 ACTIVE           HIGH                           20
 
SQL> select name,value from v$sysstat where name  like '%txn%priority%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
txns rollback priority_txns_high_wait_target                              6
txns rollback priority_txns_medium_wait_target                            0
txns track mode priority_txns_high_wait_target                            0
txns track mode priority_txns_medium_wait_target                          0
 
SQL> select name from v$event_name where name like '%enq: TX - row lock%';    
 
NAME
----------------------------------------------------------------
enq: TX - row lock contention
enq: TX - row lock (HIGH priority)
enq: TX - row lock (MEDIUM priority)
enq: TX - row lock (LOW priority)

v$transaction新增了txn_priority和priority_txns_wait_target字段
sysstat新增了txns rollback priority的相关统计
event细化了不同事务优先级下的TX row lock队列等待
还有一点非常奇怪，在low事务优先级手动rollback之后，后续的操作事务就会自动回滚了，不需要手动rollback。应该是功能还没完善，毕竟官方正式版本还未发布

SQL> delete from test.t where rownum=1;
delete from test.t where rownum=1
*
ERROR at line 1:
ORA-63302: Transaction must roll back
Help: https://docs.oracle.com/error-help/db/ora-63302/
 
 
SQL> rollback;
 
Rollback complete.
 
SQL>  delete from test.t where rownum=1;
 
1 row deleted.
--等待20s，事务自动回滚
 
SQL> delete from test.t where rownum=1;
--不需要再去手动rollback，事务自动回滚了

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)