Oracle 选择使用PRAGMA autonousous_transaction在SQL包中进行更新

你好，

我有一个软件包，其中包含一些用于同步多个服务器执行的操作的功能。
当服务器选择一个ID并对其执行操作时，其他服务器无法选择相同的ID。
为了实现这一点，我使用了一个同步标志和一个包。当使用该包执行select操作时，我想更新同步标志，然后将管道返回到后端代码。
问题是，如果2台服务器在同时运行包上的select可以选择相同的ID。
正因为如此，我想用一个

SELECT FOR UPDATE

为了不被其他服务器选择，锁定到行的语句。
我的问题是，每次我尝试运行包的选择，我得到这个错误:

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DFEFRRTP1.COIN_PACKAGE", line 88
06519. 00000 -  "active autonomous transaction detected and rolled back"
*Cause:    Before returning from an autonomous PL/SQL block, all autonomous
           transactions started within the block must be completed (either
           committed or rolled back). If not, the active autonomous
           transaction is implicitly rolled back and this error is raised.
*Action:   Ensure that before returning from an autonomous PL/SQL block,
           any active autonomous transactions are explicitly committed
           or rolled back.

下面是我的函数从包主体的示例:

FUNCTION GET_MONITORING_ON_APPS
    RETURN MONITORING_ON_APPLICATIONS PIPELINED
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    REC MONITORING_ON_RECORD;
    l_MONITORING_ON_RECORDS MONITORING_ON_RECORD;
    CURSOR MONITORING_ON_RECORDS
    IS
     SELECT A.ID FROM A
   JOIN B CH ON A.ID = B.ID AND B.STATUS_ID = 21
   JOIN C ON A.ID = C.ID AND TR.OTHER_ID = (SELECT MAX(OTHER_ID) FROM C WHERE OTHER_ID= A.ID) FOR UPDATE SKIP LOCKED;
  BEGIN
    OPEN MONITORING_ON_RECORDS;
    LOOP
      FETCH MONITORING_ON_RECORDS INTO l_MONITORING_ON_RECORDS;
      EXIT WHEN MONITORING_ON_RECORDS%NOTFOUND;
      
      UPDATE C
      SET SYNC_FLAG = 1,
        SYNC_TIMESTAMP = SYSTIMESTAMP
      WHERE CURRENT OF MONITORING_ON_RECORDS;
      PIPE ROW(l_MONITORING_ON_RECORDS);
    END LOOP;
    COMMIT;
    RETURN;
  END GET_MONITORING_ON_APPS;

我希望你能理解这一点...是我在这个论坛上的第一个问题，所以请放轻松: D
谢谢!

When a server select an ID and perform operations on it the other servers can not select the same ID.


它的排序听起来像你重新发明高级排队，这是Oracle数据库的一个免费组件。在上面给出的示例中，操作的样式将遵循 (伪代码)

-单个过程可以:

for each ID in
( SELECT A.ID FROM A
   JOIN B CH ON A.ID = B.ID AND B.STATUS_ID = 21
   JOIN C ON A.ID = C.ID AND TR.OTHER_ID = (SELECT MAX(OTHER_ID) FROM C WHERE OTHER_ID= A.ID) FOR UPDATE SKIP LOCKED;
)
do
   dbms_aq.enqueue(id)
done

然后你可以有尽可能多的并发进程，你喜欢这样做:

repeat forever
  msg := dbms_aq.dequeue
  do your processing on (msg.id) 
  commit;
end loop

在您的情况下，它是 “管道行” 和自主交易的组合。Piping意味着您在所有行用尽之前从函数 “返回”，因此出现 “活动” 事务错误-当我们将控制权传递回调用环境时，我们仍然 * 在游标循环中。

即使抛开并发和跳过锁定，我们也可以看到

SQL> create table t as
  2  select rownum x from dual connect
  3  by level <= 100;

Table created.

SQL>
SQL> create or replace
  2  function fnc return sys.odcinumberlist pipelined as
  3    pragma autonomous_transaction;
  4  begin
  5   for i in ( select * from t for update )
  6   loop
  7     pipe row ( i.x );
  8   end loop;
  9   commit;
 10   return;
 11  end;
 12  /

Function created.

SQL>
SQL> select * from fnc();
select * from fnc()
              *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "MCDONAC.FNC", line 6
ORA-06512: at "MCDONAC.FNC", line 6

要启用所需的内容 (无需进行过多的代码返工)，可以获取行，将其标记为属于会话，然后将其返回给客户端，例如

SQL> create table t as
  2  select rownum x from dual connect
  3  by level <= 100;

Table created.

SQL>
SQL> alter table t add acquired_by number;

Table altered.

SQL>
SQL> create or replace
  2  function fnc return sys.odcinumberlist pipelined as
  3    pragma autonomous_transaction;
  4    id_list sys.odcinumberlist := sys.odcinumberlist();
  5  begin
  6   select x
  7   bulk collect into id_list
  8   from t
  9   where acquired_by is null
 10   for update skip locked ;
 11
 12   forall i in 1 .. id_list.count
 13     update t set acquired_by = sys_context('userenv','sid')
 14     where x = id_list(i);
 15     commit;
 16
 17   for i in 1 .. id_list.count
 18   loop
 19     pipe row (id_list(i));
 20   end loop;
 21   return;
 22  end;
 23  /

Function created.

SQL>
SQL> select * from fnc();

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
...
...
          98
          99
         100

100 rows selected.

SQL>

现在，您可以处理已检索的ID。如果您希望以某种方式跨多个会话 “负载平衡”，那么您将在查询上添加一个限制器，例如 (rownum <500)，以便多个会话可以抓取一批行并处理它们。

但是AQ对我来说听起来更好。