数据库管理-第201期优先级事务-01（20240611）-CSDN博客

本文链接：https://blog.csdn.net/yhw1809/article/details/139601863

数据库管理201期 2024-06-11

数据库管理-第201期优先级事务-01（20240611）

数据库管理-第201期优先级事务-01（20240611）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database（Oracle与MySQL）
PostgreSQL ACE Partner
10年数据库行业经验，现主要从事数据库服务工作
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP、认证技术专家、年度墨力之星，ITPUB认证专家、专家百人团成员，OCM讲师，PolarDB开源社区技术顾问，HaloDB外聘技术顾问、OceanBase观察团成员，青学会（青年数据库学习互助会）外部顾问
圈内拥有“总监”、“保安”、“国产数据库最大敌人”等称号，非著名社恐（社交恐怖分子）
公众号：胖头鱼的鱼缸；CSDN：胖头鱼的鱼缸（尹海文）；墨天轮：胖头鱼的鱼缸；ITPUB：yhw1809。
除授权转载并标明出处外，均为“非法”抄袭

好几期没写技术了，作为技术博主这是不合格的，这一期回归Oracle Database 23ai的一个新特性Priority Transactions。

1 概念

当会话使用INSERT、UPDATA、MERGE或SELECT…FOR UPDATE来修改数据时，就会对需要修改的数据对应的行保持一个行锁来避免其他会话对这些数据进行修改，避免数据错误操作。这个锁会维持到提交(commit)或者回滚(rollback)。在某些情况下，事务可以长时间保持行锁。例如，应用程序修改了一些行，但由于应用程序中的异常而不提交或终止事务。传统上，当一个事务在行锁上被另一个事务长时间阻塞时，它需要数据库管理员使用ALTER SYSTEM KILL SESSION命令手动终止阻塞事务。
从Oracle Database 23ai开始，Oracle数据库提供了一个参数来控制何时将哪些保持行锁的事务自动回滚。数据库会回滚这些事务，但是会话依然存活，应用程序必须通过发出rollback SQL语句来确认事务的自动回滚。这个功能就叫做Priority Transactions，优先级事务。
应用程序可以指定其事务的优先级。如果低优先级事务的行锁阻止了高优先级事务，Oracle数据库将自动回滚低优先级事务，以使高优先级事务继续进行。数据库管理员可以配置回滚低优先级事务的时间。
但是当低优先级事务未阻塞任何其他会话时，这个会话永远不会被回滚。

2 使用优先级事务

2.1 配置会话优先级

可以使用下面的ALTER SESSION语句来配置会话的优先级：

ALTER SESSION SET "txn_priority" = "HIGH";

txn_priority可设置为LOW, MEDIUM和HIGH。所有的会话默认优先级都为HIGH，所以默认情况下没有会话会被自动回滚。如果在事务启动后修改此参数，则不会动态更改当前事务的优先级。会话中创建的下一个事务将使用更新后的优先级。
如果为行锁阻止了高优先级（HIGH）事务，则只有当持有者为低优先级（LOW）或中优先级（MEDIUM）时，Oracle数据库才能回滚持有行锁的事务。Oracle永远不会回滚高优先级（HIGH）事务。
如果一个中优先级（MEDIUM）的事务因行锁而被阻止，则只有当持有者为低优先级（LOW）时，Oracle数据库才能回滚持有行锁的事务。
如果一个低（LOW）优先级事务因行锁而被阻止，Oracle数据库将不会尝试回滚持有该行锁的事务，无论其优先级如何。

2.2 配置系统级别等待目标

Oracle数据库提供了参数来控制阻塞多长时间后自动回滚持有锁的会话：PRIORITY_TXNS_HIGH_WAIT_TARGET和PRIORITY_TXNS_MEDIUM_WAIT_TARGET，以秒为单位设置最长持续时间，即优先级为HIGH和MEDIUM的事务将在数据库回滚持有行锁的优先级较低的事务之前的等待时间。阻止程序事务被回滚，但其相应的会话不会被终止并保持活动状态。应用程序必须通过捕获ORA-63300并发出rollback SQL语句来确认此自动回滚。如果没有发出ROLLBACK，那么会话中的所有SQL语句将继续接收ORA-63302。并没有提供低优先级的等待目标参数，因为阻塞会话的优先级为low，Oracle数据库不会回滚阻塞程序事务。

ALTER SYSTEM SET priority_txns_high_wait_target = 15;
ALTER SYSTEM SET priority_txns_medium_wait_target = 15;

当优先级较高的事务被优先级较低的事务阻塞时，系统至少等待指定的时间，然后再回滚阻塞的事务。当有多个被素色的事务试图获取同一行锁时，等待时间可能比指定的目标时间长。例如，假设默认的高优先级等待目标设置为20秒。采取以下行动：

在时间t1，事务1，一个低优先级事务，锁定特定行。
10秒后的时间t2，事务2，一个低优先级事务，试图锁定同一行并等待。
5秒钟后的时间t3，事务3，一个高优先级事务，试图更新同一行。

假设没有事务执行提交，则高优先级事务从时间t3开始等待至少20秒，之后第一个事务被回滚。在此之后，事务2获得行锁，因为它在事务3之前请求了行锁。因此，从事务2获得行锁起，事务3将再等待20秒，之后事务2将回滚。因此，等待目标参数值并不意味着高优先级会话在获得行锁之前等待的最长时间。

2.3 确认自动回滚

事务的自动回滚必须得到确认，然后其会话才能继续执行进一步的SQL。可以通过发出事务回滚来提供确认。
当事务自动回滚时，活动会话中当前正在执行的SQL或空闲会话中的下一条SQL语句将获得ORA-63300。随后的SQL语句将抛出ORA-63302，直到发出回滚为止。因此，应用程序逻辑的结构必须捕获两个错误ORA-63300和ORA-63302，然后发出回滚。
下表列出了确认回滚的可用方法：

2.4 配置优先级事务模式

优先级事务支持两种模式，可以在启用此功能之前进行尝试。
优先级事务的默认模式是ROLLBACK。在此模式中，如果PRIORITY_TXNS_HIGH_WAIT_TARGET和PRIORITY_TXNS_MEDIUM_WAIT_ARGET配置得当，则持有行锁并阻止较高优先级事务的事务将自动回滚，并允许较高优先级的等待事务继续执行。
TRACK模式的目的是让数据库管理员试用优先级事务功能。在TRACK模式下，数据库将递增V$SYSSTAT中的统计信息（在2.2中展示的），反映此功能将回滚的事务数，而不是实际回滚任何事务。应用程序可以使用此模式在切换到ROLLBACK模式之前调整正确的等待目标值。
要将优先级事务模式设置为TRACK，请使用以下命令：

ALTER SYSTEM SET "priority_txns_mode"="TRACK";
-- 恢复为ROLLBACK模式
ALTER SYSTEM SET "priority_txns_mode"="ROLLBACK";

2.5 使用优先级事务模式确定系统级等待目标

优先级事务模式可用于帮助确定系统级等待目标。
为了帮助设置PRIORITY_TXNS_HIGH_WAIT_TARGET和PRIORITY_TXNS_MEDIUM_WAIT_ARGET到适当值，可以将PRIORITY-TXNS_MODE设置为TRACK并监视行锁定争用等待事件时间。
在TRACK模式下运行您的常规工作负载几个小时（或任何适当的时间），并监视特定优先级的时间事务，通常等待行锁定。例如，如果您观察到您的高优先级事务通常在行锁上等待最多10秒，则建议将PRIORITY_TXNS_HIGH_WAIT_TARGET的值设置为90秒以上的值，以便Oracle数据库在对数据库优先级会话的操作时不会回滚任何持有行锁的正常事务。确定这些参数的适当值后，可以关闭TRACK模式并切换到ROLLBACK模式，使用这些值配置系统级等待目标参数，然后开始使用优先级事务。
当存在对行锁的争用时，等待行锁的事务会等待一个常见的等待事件enq: TX - row lock contention。通过设置事务的txn_priority参数和系统的wait_target参数来启用优先级事务，等待事务将根据等待事务的优先级等待等待事件。

可以通过以下语句查询优先级会话信息：

SQL> SELECT TO_CHAR(xidusn) || '.' || TO_CHAR(xidslot) || '.' || TO_CHAR(xidsqn) AS transaction_id, sid, event, seconds_in_wait, blocking_session
     FROM   v$session, v$transaction
     WHERE  event LIKE '%enq%' AND v$session.saddr = v$transaction.ses_addr;

TRANSACTION_ID  SID     EVENT                              SECONDS_IN_WAIT BLOCKING_SESSION
--------------- ------ ----------------------------------- --------------- ----------------
2.17.1619          187 enq: TX - row lock (HIGH priority)              361              204
5.32.1557           51 enq: TX - row lock (LOW priority)               359              204