PL/SQL 过程的同步.如何保证一次只执行一个程序?

2021-12-24 00:00:00 oracle plsql

某些 PL/SQL 过程需要花费大量时间进行评估(如重新计算和更新大量数据).

Some PL/SQL procedure take a lot of time for evaluation (as recalculate and update a lot of data).

我想将评估此过程的执行上下文的数量限制为单个.

I want to limit numbers of execution context which evaluate this procedure to single.

如果其他上下文执行它，则什么都不做...

Just do nothing if some another context execute it...

但我不知道在 PL/SQL 代码中使用哪些原子操作.

But I don't know which atomic operations to use in PL/SQL code.

注意:如果服务器在过程评估中间停止，我担心使用表行进行同步会导致数据库中的状态不一致......

Note: I afraid that using table row for synchronization can lead to inconsistent state in DB if server halted on middle of procedure evaluation...

推荐答案

您可以使用 DBMS_LOCK.request 生成唯一的锁句柄.只有一个会话可以同时持有这个锁.如果会话的数据库重启意外结束，锁会自动释放.

You can use DBMS_LOCK.request to generate a unique lock handle. Only one session can hold this lock at the same time. If the database restarts of the session ends unexpectedly, the lock will be released automatically.

您在请求锁时决定是否跨提交持有锁.

You decide when requesting the lock if the lock will be held across commits or not.

这是一个例子:

SQL> CREATE OR REPLACE PROCEDURE serial IS
  2     l_lock_handle  VARCHAR2(128 BYTE);
  3     l_lock_request INTEGER;
  4  BEGIN
  5     dbms_lock.allocate_unique(lockname => 'MY_SERIAL_PROC',
  6                               lockhandle => l_lock_handle);
  7     l_lock_request := dbms_lock.request(lockhandle => l_lock_handle,
  8                                         timeout => 5,
  9                                         release_on_commit => FALSE);
 10     CASE l_lock_request
 11        WHEN 0 THEN
 12           NULL; -- success
 13        WHEN 1 THEN
 14           raise_application_error(-20002, 'lock already reserved');
 15        ELSE
 16           raise_application_error(-20001, 'Lock error: ' || l_lock_request);
 17     END CASE;
 18     BEGIN
 19        ---------- serialized block of code           ----------
 20        ---------- (lock will be kept accross commit) ----------
 21        dbms_lock.sleep(30);
 22        ---------- End of serialized code             ----------
 23     EXCEPTION
 24        WHEN OTHERS THEN -- release lock in case of uncatched error
 25           l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 26           RAISE;
 27     END;
 28     l_lock_request := dbms_lock.release(lockhandle => l_lock_handle);
 29  END;
 30  /

Procedure created

我将同时运行两个会话:

I'll run two sessions at the same time:

Session A> exec serial;                

                                       Session B> -- Before session A ends
                                       Session B> exec serial;

                                       ERROR at line 1:
                                       ORA-20002: lock already reserved
                                       ORA-06512: at "APPS.SERIAL", line 13
                                       ORA-06512: at line 1


PL/SQL procedure successfully completed

                                       Session B> -- After session A ends
                                       Session B> exec serial;

                                       PL/SQL procedure successfully completed.

相关文章