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’); update scott.mycheck set t=0; |
||
| t2 | alter session set txn_priority = low;
select sys_context(‘userenv’,’SID’); update scott.mycheck set t=10; |
||
| t3 | — is waiting |
||
| t4=t3+10 | 1 row updated. |
||
| t5=t4+10 | SQL> select * from scott.mycheck; |
SQL> select * from scott.mycheck; |
1 row updated.SQL> select * |
高优先级事务 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.
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。