首页 » AI, ORACLE 9i-23ai » Oracle 23ai新特性: 事务优先级(Priority Transactions),自动回滚

Oracle 23ai新特性: 事务优先级(Priority Transactions),自动回滚

相信oracle DBA一定遇到过enq: TX – row lock等待,假设一个会话修改了table中的数据,但一直未提交,此时其它会话相同行的任何DML事务都会挂起,并v$session.event显示enq: TX – row lock contention的等待事件,直到持有行锁的会话commit或rollback结束事务释放锁定行。如果在应用程序中未提交, DBA此时就要介入做kill session或cancel SQL终止SQL保留session。为此我还专门写了一个shell kill_tx_blocker_session.sh加crontab去调度自动kill tx row lock的堵塞者, 在19c 20c中可以使用参数max_idle_blocker_time 自动终止blocker《Oracle 12c 19c Automatic terminal/kill session feature& DCD》,  前几天发现在oracle 23版本引入了个有意思的功能”事务优先级“,可以在 LOW、MEDIUM 和 HIGH(默认值)之间进行选择, 如果“低级”的会话事务堵塞了更高级会话事务,在超过预定时间(秒)数据后会自动处理[rollback(default) 或者commit] ,默认事务都是High级别,自动事务回滚减少了管理负担。

主要有几个数据库参数

SQL> @pd "txn%_priority"
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      3227   C9B txn_priority                                             HIGH                           Priority of a transaction in a session
      3229   C9D txn_auto_rollback_high_priority_wait_target              2147483647                     Auto abort wait for high pri txns
      3231   C9F txn_auto_rollback_medium_priority_wait_target            2147483647                     Auto abort wait for medium pri txns
      3232   CA0 priority_txns_mode                                       ROLLBACK                       Modes for Priority Transactions feature
      3233   CA1 txn_auto_rollback_mode                                   ROLLBACK                       Modes for Priority Transactions feature

SQL> @pd "priority%txn%"
Show all parameters and session values from x$ksppi/x$ksppcv...

       NUM N_HEX NAME                                                     VALUE                          DESCRIPTION
---------- ----- -------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------
      3228   C9C priority_txns_high_wait_target                           2147483647                     Auto abort wait for high pri txns
      3229   C9D txn_auto_rollback_high_priority_wait_target              2147483647                     Auto abort wait for high pri txns
      3230   C9E priority_txns_medium_wait_target                         2147483647                     Auto abort wait for medium pri txns
      3231   C9F txn_auto_rollback_medium_priority_wait_target            2147483647                     Auto abort wait for medium pri txns
      3232   CA0 priority_txns_mode                                       ROLLBACK                       Modes for Priority Transactions feature

相关的数据字典的变化

SQL> @col "txn%priority"

OWNER                TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            N NUM_DISTINCT LOW_VALUE                                                                                                                                                                                                                                                       HIGH_VALUE                                                                                                                                                                                                                                                         DENSITY  NUM_NULLS NUM_BUCKETS
-------------------- ------------------------------ ------------------------------ -------------------- - ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- -----------
SYS                  GV_$TRANSACTION                TXN_PRIORITY                   VARCHAR2             Y
                     V_$TRANSACTION                 TXN_PRIORITY                   VARCHAR2             Y


SQL> @col "priority%txn"

OWNER                TABLE_NAME                     COLUMN_NAME                    DATA_TYPE            N NUM_DISTINCT LOW_VALUE                                                                                                                                                                                                                                                       HIGH_VALUE                                                                                                                                                                                                                                                         DENSITY  NUM_NULLS NUM_BUCKETS
-------------------- ------------------------------ ------------------------------ -------------------- - ------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- -----------
SYS                  GV_$TRANSACTION                PRIORITY_TXNS_WAIT_TARGET      NUMBER               Y
                     V_$TRANSACTION                 PRIORITY_TXNS_WAIT_TARGET      NUMBER               Y


SQL> @desc v$transaction
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ADDR                                     RAW(8)
...
   45      PRV_XID                                  RAW(8)
   46      PTX_XID                                  RAW(8)
   47      TXN_PRIORITY                             VARCHAR2(7)
   48      PRIORITY_TXNS_WAIT_TARGET                NUMBER
   49      CON_ID                                   NUMBER


等待事件的变化

SQL> @sed "enq: TX - row lock"
Show wait event descriptions matching %enq: TX - row lock%..

EVENT# EVENT_NAME                                              WAIT_CLASS           PARAMETER1                PARAMETER2                PARAMETER3                ENQUEUE_NAME                   REQ_REASON                       REQ_DESCRIPTION
------ ------------------------------------------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------------ -------------------------------- ----------------------------------------------------------------------------------------------------
   344 enq: TX - row lock (HIGH priority)                      Application          name|mode                 usn<<16 | slot            sequence                  Transaction                    row lock (HIGH priority)         HIGH priority transaction waiting for a row lock held by another transaction
   346 enq: TX - row lock (LOW priority)                       Application          name|mode                 usn<<16 | slot            sequence                  Transaction                    row lock (LOW priority)          LOW priority transaction waiting for a row lock held by another transaction
   345 enq: TX - row lock (MEDIUM priority)                    Application          name|mode                 usn<<16 | slot            sequence                  Transaction                    row lock (MEDIUM priority)       MEDIUM priority transaction waiting for a row lock held by another transaction
   343 enq: TX - row lock contention                           Application          name|mode                 usn<<16 | slot            sequence                  Transaction                    row lock contention              Lock held on a particular row by a transaction to prevent other transactions from modifying it


相关报错

[oracle@localhost ~]$ while [ $event -ne 63400 ]
> do
> oerr ora $event
> event=`expr $event + 1`
> done
63300, 00000, "Transaction is automatically rolled back since it is blocking a higher priority transaction from another session."
// *Cause: The transaction is holding a row lock and blocking a transaction
//         with a higher priority. The blocked transaction has already been
//         waiting for target wait time and then request to automatically
//         rollback this blocker transaction.
// *Action: Check and rollback the current transaction.
63301, 00000, "ROLLBACK is being used on an automatically rolled back XA branch. Use XA_END and XA_ROLLBACK instead."
// *Cause: User issue ROLLBACK on a automatically rolled back xa branch,
//         they should use XA_END and XA_ROLLBACK instead.
// *Action: Use XA_END and XA_ROLLBACK for the current transaction.
63302, 00000, "Transaction must roll back"
// *Cause: User must issue rollback to current transaction to acknowledge that
//         they know that the current transaction has been automatically rolled
//         back.
// *Action: Issue ROLLBACK for the local transaction and issue XA_END and
//          XA_ROLLBACK for the XA transaction.
63303, 00000, "Cannot set the parameter txn_priority inside an Oracle Scheduler job"
// *Cause: An attempt is being made to set the parameter txn_priority inside an
//         Oracle Scheduler job, which is not allowed.
// *Action: Set the txn_priority parameter inside an Oracle connection
//          or server process.
//

演示功能
priority_txns_[high|medium]_wait_target参数是等待秒数,需要系统级修改, TXN_PRIORITY参数是事务优先级,可以session级修改.

SQL> alter system set priority_txns_medium_wait_target=10;
alter system set priority_txns_medium_wait_target=10
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-12754: Feature 'Automatic Transaction Rollback' is disabled due to missing capability 'Database Editions'.
Help: https://docs.oracle.com/error-help/db/ora-02097/

SQL> alter session set txn_priority = low; 
ERROR:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-12754: Feature 'Automatic Transaction Rollback' is disabled due to missing capability 'Database Editions'.
Help: https://docs.oracle.com/error-help/db/ora-02097/

Note:
注意该在当前的dev free版没有办法体验,需要企业版功能.

SQL> show parameter priori
PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
inmemory_prefer_xmem_priority                                string
priority_txns_high_wait_target                               integer     5
priority_txns_medium_wait_target                             integer     20
priority_txns_mode                                           string      ROLLBACK
txn_auto_rollback_high_priority_wait_target                  integer     2147483647
txn_auto_rollback_medium_priority_wait_target                integer     2147483647
txn_priority                                                 string      HIGH

#session sid 4
SQL> update test4 set id=100 where id=10;

1 row updated.

# session sid 202
SQL> update test4 set id=100 where id=10;
-- waiting 

# session 3
SQL> select sid, event, seconds_in_wait, blocking_session from v$session where event like '%enq%';

       SID EVENT                                    SECONDS_IN_WAIT BLOCKING_SESSION
---------- ---------------------------------------- --------------- ----------------
       202 enq: TX - row lock (HIGH priority)                  3222                4

SQL> select sid,TXN_PRIORITY,PRIORITY_TXNS_WAIT_TARGET,blocking_session,SECONDS_IN_WAIT  from v$transaction t,v$session s where t.ses_addr=s.saddr;

       SID TXN_PRI PRIORITY_TXNS_WAIT_TARGET BLOCKING_SESSION SECONDS_IN_WAIT
---------- ------- ------------------------- ---------------- ---------------
         4 HIGH                            5                             2999

Note:
绕过了参数,但是session 级没有办法修改,测试事务优先级同级的不会自动timeout和auto rollback。

Waiting Transaction’s Priority Wait Event
HIGH enq:TX - row lock contention (HIGH pri)
MEDIUM enq: TX - row lock contention (MEDIUM pri)
LOW enq: TX - row lock contention (LOW pri)

Ulrike Schwinn的的记录

Time Transaction1 Low Transaction2 Low Transaction3 High (Default)
t1 alter session  set txn_priority = low;

select sys_context(‘userenv’,’SID’);
SID
—–
630

update scott.mycheck set t=0;
1 rows updated.

t2 alter session set txn_priority = low;

select sys_context(‘userenv’,’SID’);
SID
—–
940

update scott.mycheck set t=10;
— is waiting

t3 select SYS_CONTEXT('USERENV','SID');
SID
-----
1093
update scott.mycheck set t=1000;
— is waiting
t4=t3+10  1 row updated.
t5=t4+10 SQL> select * from scott.mycheck;
*
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 * from scott.mycheck;
*
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/
1 row updated.
SQL> select *
from scott.mycheck;
---------
1000

高优先级事务 3 (1093) 至少等待 10 秒(从时间 t3 开始),之后事务 1 (630) 将回滚。在此之后,事务 2 将获得行锁,因为它在事务 3 之前请求了行锁。因此,事务 3 将从事务 2 获得行锁的时间起再等待 10 秒,之后事务 2 将回滚。因此,等待目标参数值并不意味着高优先级服务程序在获取行锁之前等待的最长时间。 低优先级事务终止后,会话不会被终止并保持活动状态,必须先确认事务的自动回滚,然后其会话才能继续执行进一步的 SQL。这步感觉不是很合理,应用程序必须捕捉错误做rollback,会话才能继续做其它事务,但参数txn_auto_rollback_high_priority_wait_target和TXN_AUTO_ROLLBACK_MEDIUM_PRIORITY_WAIT_TARGET应该就是做自动rollback的。 FatDBA 在他的blog上显示auto rollback后会话终止了,这恐怕要等后期正式版本GA后测试了。

小结:
Oracle 23ai的新特性事务优先级和自动rollback功能,用于简化管理,解决著名的enq: tx row lock自动终止低级别的事务,该功能需要2个前提条件,1是等待时间,2是不同的会话事务优先级。默认同级并不会处理。可以使用类似我脚本shell+crontab那样kill或cancel SQL.

打赏

,

目前这篇文章还没有评论(Rss)

我要评论