从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
ROLLBACKSQL 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.
具体是如何实现事务优先级其实是通过一系列的控制参数实现的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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
HIGHpriority 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
MEDIUMpriority transaction will wait for a row lock before the Priority Transactions feature rolls back a lower priority transaction holding the lock.
|
1 2 3 4 5 6 7 |
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：
|
1 2 3 4 5 6 7 8 |
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：
|
1 |
SQL> delete from test.t where data_object_id is null; |
验证结果：
session 2在等待priority_txns_high_wait_target时间后，session 1的事务被自动回滚，但是session继续保留。alert中将打印自动终止事务的相关信息
|
1 |
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必须手动回滚才能继续后续操作
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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应该只是开发用的参数，设置并无作用
|
1 2 3 4 5 6 7 |
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
|
1 |
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都增加了事务优先级的相关信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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。应该是功能还没完善，毕竟官方正式版本还未发布
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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，事务自动回滚了 |