会话间通信 DBMS_ALERT-阿里云开发者社区

Inter-Session Communication DBMS_ALERT
会话间通信 DBMS_ALERT

提问:如何让程序获知数据被改变?
回答:
1.轮询表, 查询count(*)等查看记录是否增加了
2.轮询审计表(audit table), 查询count(*)或时间戳字段, 得知表是否有更新
3.DBMS_ALERT
4.DBMS_AQ ...

dbms_alert首次出现在Oracle版本7中, 是数据库会话间通信的一种实现方式,
提供了一种向多用户广播数据库事件(database events)即警报(alerts)的机制
是依赖dbms_pipe和dbms_lock实现的
可用于, 有时想监控数据库表的变化, 应用程序不得不反复查询, 造成开销很大
dbms_alert的机制使得当发生变化时, 数据库可以主动的去通知应用程序

参考文档: 13 DBMS_ALERT:

Alerts are transaction-based. This means that the waiting session is not alerted until the transaction signalling the alert commits. There can be any number of concurrent signalers of a given alert, and there can be any number of concurrent waiters on a given alert.
警报是基于事务的. 这意味着直到发起警报的会话提交了事务,等待警报的会话才收到警报. 一个指定的警报可以同时有任意多个发起者, 也可以有任意多个接受者.

A waiting application is blocked in the database and cannot do any other work.
等待中的应用程序被阻塞, 不能做其它操作

An application can register for multiple events and can then wait for any of them to occur using the WAITANY procedure.
一个应用程序可以注册多个事件, 然后使用WAITANY存储过程, 等待它们中任意一个(就是多对多的关系, 一个警报可以有多个接收者, 一个接收者可以接收多个警报)

An application can also supply an optional timeout parameter to the WAITONE or WAITANY procedures. A timeout of 0 returns immediately if there is no pending alert.
一个应用程序也可以对WAITONE或WAITANY存储过程指定一个超时参数. 如果没有待决的警报, 超时参数为0导致立即返回.

The signalling session can optionally pass a message that is received by the waiting session.
发信号的会话可以向等待会话传递一个消息.

Alerts can be signalled more often than the corresponding application wait calls. In such cases, the older alerts are discarded. The application always gets the latest alert (based on transaction commit times).
发出的警报可以多于应用程序的等待调用. 在这种情况下, 旧的警报被丢弃. 应用程序总是得到最新的警报(基于事务提交的时间).

If the application does not require transaction-based alerts, the DBMS_PIPE package may provide a useful alternative.
如果应用程序不需要基于事务的警报, 那么DBMS_PIPE包可供选择.

If the transaction is rolled back after the call to SIGNAL, no alert occurs.
如果在调用SIGNAL后事务回滚了, 没有警报发生

It is possible to receive an alert, read the data, and find that no data has changed. This is because the data changed after the prior alert, but before the data was read for that prior alert.
有可能收到警报,再去读数据,发现数据没有改变. 这是因为数据是在发出警报后,读数据前改变的(这怎么可能? 先发警报再改数据? 发警报可能比事务提交要快?)

Usually, Oracle is event-driven; this means that there are no polling loops. There are two cases where polling loops can occur:
通常, Oracle是事件驱动的; 这意味着没有轮询循环. 轮训循环有两种情况:

Shared mode. If your database is running in shared mode, a polling loop is required to check for alerts from another instance. The polling loop defaults to one second and can be set by the SET_DEFAULTS procedure.
共享模式. 如果你的数据库运行于共享模式, 就需要轮询循环检查另一个实例的警报. 默认轮询周期是1秒钟, 可以通过SET_DEFAULT存储过程设置.

WAITANY procedure. If you use the WAITANY procedure, and if a signalling session does a signal but does not commit within one second of the signal, a polling loop is required so that this uncommitted alert does not camouflage other alerts. The polling loop begins at a one second interval and exponentially backs off to 30-second intervals.
WAITANY过程. 如果使用了WAITANY过程, 并且如果发出了一个警报,在1秒内没有提交事务的话, 需要一个轮询以便这个未提交的警报不会阻挡住别的警报. 轮询间隔以1秒开始, 然后以指数增长直到30秒.

测试:

1. 授权

conn / as sysdba
grant execute on dbms_alert to a;

2.
新打开一个会话, 接收警报

conn a/a
set pages 50000 line 130
set serveroutput on size unlimited

注册一个警报

exec dbms_alert.register('alert_test');

3.
查看警报信息
SYS用户运行

SQL> select * from dbms_alert_info;

NAME                           SID                            C
------------------------------ ------------------------------ -
MESSAGE
--------------------------------------------------------------------------------
ALERT_TEST                     065C00C00001                   N



SQL> desc dbms_alert_info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                      NOT NULL VARCHAR2(30)
 SID                                       NOT NULL VARCHAR2(30)
 CHANGED                                            VARCHAR2(1)
 MESSAGE                                            VARCHAR2(1800)

SQL>

DBMS_ALERT_INFO
NAME : 警报名. 可以有同名的, 也就是说可以有多个会话注册和接收同一个警报
SID : 等于DBMS_SESSION.UNIQUE_SESSION_ID. 分为3个部分, 是 SID + SERIAL# + InstanceNumber
见How Is SID In DBMS_ALERT_INFO Related To SID In V$Session

select name
       , to_number(substr(sid,1,4),'xxxx') sid
       , to_number(substr(sid,5,4),'xxxx') serial#
       , to_number(substr(sid,9,4),'xxxx') instance#
  from dbms_alert_info;

NAME                                  SID    SERIAL#  INSTANCE#
------------------------------ ---------- ---------- ----------
ALERT_TEST                           1628        192          1

CHANGED: N:没有发出警报或警报已被接收 Y:警报已发出还未被接收
MESSAGE: 警报消息

4.
注册完后, 等待接收警报

var v_messge varchar2(1000)
var v_status number
exec dbms_alert.waitone('alert_test', :v_messge, :v_status);
print :v_status :v_messge

调用waitone后没有返回, 一直在等待警报

5.
打开另一个会话, 发出警报

conn a/a
set pages 50000 line 130
set serveroutput on size unlimited
exec dbms_alert.signal('alert_test', 'hello world!');
commit;

事务提交后才发出警报

6.
等待接收警报的会话接收到了警报, 打印出信息

SQL> print :v_status :v_messge

  V_STATUS
----------
         0


V_MESSGE
----------------------------------------------------------------------------------------------------------------------------------
hello world!

查询dbms_alert_info信息

SQL> select * from dbms_alert_info;

NAME                           SID                            C
------------------------------ ------------------------------ -
MESSAGE
--------------------------------------------------------------------------------
ALERT_TEST                     065C00C00001                   N
hello world!

这里CHANGED还是N, 因为警报发出后立刻被收到了
如果发出的警报没有被收到, CHANGED是Y

7. 问题:注册时挂起
如果注册警报调用dbms_alert.register时就停住了,没有返回
说明之前有别的会话发出了警告,但没有提交事务, 这样注册过程就被阻塞住了
只要提交或回滚该事务即可解决

参考:
PL/SQL Session Hangs When Executing "Dbms_alert.Register(''Varchar'')" Statement
DBMS_ALERT.WAITONE HANGS WAITING ON DBMS_ALERT.SIGNAL

8. 多次警报

exec dbms_alert.signal('alert_test', 'hello one')
commit;
exec dbms_alert.signal('alert_test', 'hello two')
commit;

SQL> select * from dbms_alert_info;

NAME                           SID                            C
------------------------------ ------------------------------ -
MESSAGE
--------------------------------------------------------------------------------
ALERT_TEST                     065C00C00001                   Y
hello two

看到只有最新的一条警报生效

接收警报

SQL> exec dbms_alert.waitone('alert_test', :v_messge, :v_status);

PL/SQL procedure successfully completed.

SQL> print :v_status :v_messge

  V_STATUS
----------
         0


V_MESSGE
----------------------------------------------------------------------------------------------------------------------------------
hello two

也是最新一条

接收后CHANGED字段会变为N

9. 删除警报
删除之前定义的警报
必须在注册该警报的会话上运行

exec dbms_alert.remove('alert_test')

如果注册警报的会话退出了, 用其它会话删除不掉, dbms_alert_info表中还存在该记录

SQL> select * from dbms_alert_info;

NAME                           SID                            C
------------------------------ ------------------------------ -
MESSAGE
--------------------------------------------------------------------------------
ALERT_TEST                     065C00C00001                   N
hello 2

参考文档 How To Remove Alerts From DBMS_ALERTS_INFO Table 上说重新注册,再删除就能删掉
因为前一个会话已经退出了, 这时重新注册oracle会自动删除原记录并增加一条新的(或者说是覆盖), 所以可以删除

10. 删除管道
虽然删除了警报, 但是, dbms_alert创建的管道仍在系统中, 没有删除. 查看v$db_pipes:

SQL> col name for a40
SQL> select * from v$db_pipes;

   OWNERID NAME                                     TYPE     PIPE_SIZE
---------- ---------------------------------------- ------- ----------
           ORA$ALERT$065C00C20001                   PUBLIC        1687
           ORA$ALERT$066300730001                   PUBLIC        1687
           ORA$ALERT$066C021C0001                   PUBLIC        1687
           ORA$ALERT$066C021A0001                   PUBLIC        1687
           ORA$ALERT$065D00670001                   PUBLIC        1687

SQL>

这是因为DBMS_ALERT使用的是隐式管道, 使用DBMS_PIPE.PURGE清空, 并没有直接删除, 等待系统自动删除
可用dbms_pipe.remove_pipe将其删掉

declare
  v int;
begin
  for x in (select name from v$db_pipes where name like 'ORA$ALERT$%')
  loop
    v := dbms_pipe.remove_pipe(x.name);
  end loop;
end;
/

11. 源码

package dbms_alert is

  ------------
  --  OVERVIEW
  --
  --  This package provides support for the asynchronous (as opposed to
  --  polling) notification of database events.  By appropriate use of
  --  this package and database triggers, an application can cause itself
  --  to be notified whenever values of interest in the database are
  --  changed.
  --
  --  For example, suppose a graphics tool is displaying a graph of some
  --  data from a database table.  The graphics tool can, after reading and
  --  graphing the data, wait on a database alert ('dbms_alert.waitone')
  --  covering the data just read.  The tool will automatically wake up when
  --  the data is changed by any other user.  All that is required is that a
  --  trigger be placed on the database table which then performs a signal
  --  ('dbms_alert.signal') whenever the trigger is fired.
  --
  --  Alerts are transaction based.  This means that the waiting session
  --  does not get alerted until the transaction signalling the alert commits.
  --
  --  There can be any number of concurrent signallers of a given alert, and
  --  there can be any number of concurrent waiters on a given alert.
  --
  --  A waiting application will be blocked in the database and cannot do
  --  any other work.
  --
  --  Most of the calls in the package, except for 'signal', do commits.
  --

  -----------
  --  EXAMPLE
  --
  --  Suppose the application wishes to graph average salaries, say by
  --  department, for all employees.  So the application needs to know
  --  whenever 'emp' is changed.  The application would look like this:
  --
  --      dbms_alert.register('emp_table_alert');
  --    readagain:
  --      
  --      dbms_alert.waitone('emp_table_alert', :message, :status);
  --      if status = 0 then goto readagain; else 
  --
  --  The 'emp' table would have a trigger similar to the following:
  --
  --    create trigger emptrig after insert or update or delete on emp
  --    begin
  --      dbms_alert.signal('emp_table_alert', 'message_text');
  --    end;
  --
  --  When the application is no longer interested in the alert, it does
  --    dbms_alert.remove('emp_table_alert');
  --  This is important since it reduces the amount of work required by
  --  the alert signaller.
  --
  --  If a session exits (or dies) while there exist registered alerts,
  --  they will eventually be cleaned up by future users of this package.
  --
  --  The above example guarantees that the application will always see
  --  the latest data, although it may not see every intermediate value.


  --------------
  --  VARIATIONS
  --
  --  The application can register for multiple events and can then wait for
  --  any of them to occur using the 'waitany' call.
  --
  --  An application can also supply an optional 'timeout' parameter to the
  --  'waitone' or 'waitany' calls.  A 'timeout' of 0 returns immediately
  --  if there is no pending alert.
  --
  --  The signalling session can optionally pass a message which will be
  --  received by the waiting session.
  --
  --  Alerts may be signalled more often than the corresponding application
  --  'wait' calls.  In such cases the older alerts are discaded.  The
  --  application always gets the latest alert (based on transaction commit
  --  times).
  --
  --  If the application does not require transaction based alerts, then the
  --  'dbms_pipe' package may provide a useful alternative
  --
  --  If the transaction is rolled back after the call to 'dbms_alert.signal',
  --  no alert will occur.
  --
  --  It is possible to receive an alert, read the data, and find that no
  --  data has changed.  This is because the data changed after the *prior*
  --  alert, but before the data was read for that *prior* alert.


  --------------------------
  --  IMPLEMENTATION DETAILS
  --
  --  In most cases the implementation is event-driven, i.e., there are no
  --  polling loops.  There are two cases where polling loops can occur:
  --
  --    1) Parallel mode.  If your database is running parallel mode then
  --       a polling loop is required to check for alerts from another
  --       instance.  The polling loop defaults to one second and is settable
  --       by the 'set_defaults' call.
  --    2) Waitany call.  If you use the 'waitany' call, and a signalling
  --       session does a signal but does not commit within one second of the
  --       signal, then a polling loop is required so that this uncommitted
  --       alert does not camouflage other alerts.  The polling loop begins
  --       at a one second interval and exponentially backs off to 30 second
  --       intervals.
  --
  --  This package uses the dbms_lock package (for synchronization between
  --  signallers and waiters) and the dbms_pipe package (for asynchronous
  --  event dispatching).

  -------------------------------------------------------
  --  INTERACTION WITH MULTI-THREADED AND PARALLEL SERVER
  --
  --  When running with the parallel server AND multi-threaded server, a
  --  multi-threaded (dispatcher) "shared server" will be bound to a
  --  session (and therefore not shareable) during the time a session has
  --  any alerts "registered", OR from the time a session "signals" an
  --  alert until the time the session commits.  Therefore, applications
  --  which register for alerts should use "dedicated servers" rather than
  --  connecting through the dispatcher (to a "shared server") since
  --  registration typically lasts for a long time, and applications which
  --  cause "signals" should have relatively short transactions so as not
  --  to tie up "shared servers" for too long.

  ------------
  --  SECURITY
  --
  --  Security on this package may be controlled by granting execute on
  --  this package to just those users or roles that you trust.  You may
  --  wish to write a cover package on top of this one which restricts
  --  the alertnames used.  Execute privilege on this cover package can
  --  then be granted rather than on this package.


  -------------
  --  RESOURCES
  --
  --  This package uses one database pipe and two locks for each alert a
  --  session has registered.


  ---------------------
  --  SPECIAL CONSTANTS
  --
  maxwait constant integer :=  86400000; -- 1000 days
  --  The maximum time to wait for an alert (essentially forever).


  ----------------------------
  --  PROCEDURES AND FUNCTIONS
  --
  procedure set_defaults(sensitivity in number);
  --  Set various defaults for this package.
  --  Input parameters:
  --    sensitivity
  --      In case a polling loop is required (see "Implementation Details"
  --      above), this is the time to sleep between polls.  Deafult is 5 sec.
  --
  procedure register(name in varchar2);
  --  Register interest in an alert.  A session may register interest in
  --    an unlimited number of alerts.  Alerts should be de-registered when
  --    the session no longer has any interest (see 'remove').  This call
  --    always performs a 'commit'.
  --  Input parameters:
  --    name
  --      The name of the alert in which this session is interested.
  --      WARNING:  Alert names beginning with 'ORA$' are reserved for use for
  --      products provided by Oracle Corporation.  Name must be 30 bytes
  --      or less.  The name is case-insensitive.
  --
  procedure remove(name in varchar2);
  --  Remove alert from registration list.  Do this when the session is no
  --    longer interested in an alert.  Removing an alert is important
  --    since it will reduce the amount of work done by signalers of the alert.
  --    If a session dies without removing the alert, that alert will
  --    eventually (but not immediately) be cleaned up.  This call always
  --    performs a commit.
  --  Input parameters:
  --    name
  --      The name of the alert to be removed from registration list. The
  --      name is case-insensitive.
  --
  procedure removeall;
  --  Remove all alerts for this session from registration list.  Do this
  --    when the session is no longer interested in any alerts.  Removing
  --    alerts is important since it will reduce the amount of work done
  --    by signalers of the alert.  If a session dies without removing all
  --    of its alerts, the alerts will eventually (but not immediately)
  --    be cleaned up.  This call always performs a commit.
  --
  --    This procedure is called automatically upon first reference to this
  --    package during a session.  Therefore no alerts from prior sessions
  --    which may have terminated abnormally can affect this session.
  procedure waitany(name out varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
  --  Wait for an alert to occur for any of the alerts for which this
  --    session is registered.  Although probably unusual, the same session
  --    that waits for the alert may also first signal the alert.  In this
  --    case remember to commit after the signal and prior to the wait.
  --    Otherwise a lock request exception (status 4) will occur.  This
  --    call always performs a commit.
  --  Input parameters:
  --    timeout
  --      The maximum time to wait for an alert.  If no alert occurs before
  --      timeout seconds, then this call will return with status of 1.
  --  Output parameters:
  --    name
  --      The name of the alert that occurred, in uppercase.
  --    message
  --      The message associated with the alert.  This is the message
  --      provided by the 'signal' call.  Note that if multiple signals
  --      on this alert occurred before the waitany call, then the message
  --      will correspond to the most recent signal call.  Messages from
  --      prior signal calls will be discarded.
  --    status
  --      0 - alert occurred
  --      1 - timeout occurred
  --  Errors raised:
  --    -20000, ORU-10024: there are no alerts registered.
  --       Cause: You must register an alert before waiting.
  --
  procedure waitone(name in varchar2,
                    message out varchar2,
                    status out integer,
                    timeout in number default maxwait);
  --  Wait for specified alert to occur. If the alert was signalled since
  --    the register or last waitone/waitany, then this call will return
  --    immediately.  The same session that waits for the alert may also
  --    first signal the alert.  In this case remember to commit after the
  --    signal and prior to the wait.  Otherwise a lock request exception
  --    (status 4) will occur.  This call always performs a commit.
  --  Input parameters:
  --    name
  --      The name of the alert to wait for. The name is case-insensitive.
  --    timeout
  --      The maximum time to wait for this alert.  If no alert occurs before
  --      timeout seconds, then this call will return with status of 1.
  --      If the named alert has not been registered then the this call
  --      will return after the timeout period expires.
  --  Output parameters:
  --    message
  --      The message associated with the alert.  This is the message
  --      provided by the 'signal' call.  Note that if multiple signals
  --      on this alert occurred before the waitone call, then the message
  --      will correspond to the most recent signal call.  Messages from
  --      prior signal calls will be discarded.  The message may be up to
  --      1800 bytes.
  --    status
  --      0 - alert occurred
  --      1 - timeout occurred
  --
  procedure signal(name in varchar2,
                   message in varchar2);
  --  Signal an alert.
  --  Input parameters:
  --    name
  --      Name of the alert to signal.  The effect of the signal call only
  --      occurs when the transaction in which it is made commits.  If the
  --      transaction rolls back, then the effect of the signal call is as
  --      if it had never occurred.  All sessions that have registered
  --      interest in this alert will be notified.  If the interested sessions
  --      are currently waiting, they will be awakened.  If the interested
  --      sessions are not currently waiting, then they will be notified the
  --      next time they do a wait call.  Multiple sessions may concurrently
  --      perform signals on the same alert.  However the first session
  --      will block concurrent sessions until the first session commits.
  --      Name must be 30 bytes or less. It is case-insensitive.  This call
  --      does not perform a commit.
  --    message
  --      Message to associate with this alert.  This will be passed to
  --      the waiting session.  The waiting session may be able to avoid
  --      reading the database after the alert occurs by using the
  --      information in this message.  The message must be 1800 bytes or less.

end;

PACKAGE BODY dbms_alert IS
  P_INT           NUMBER         := 5;
  THIS_SESSION_ID   VARCHAR2(30)   := DBMS_SESSION.UNIQUE_SESSION_ID;
  PARALLEL          BOOLEAN        := DBMS_UTILITY.IS_CLUSTER_DATABASE;
  SIGPIPE           VARCHAR2(30)   := 'ORA$ALERT$' || THIS_SESSION_ID;
  MSGSEQ            BINARY_INTEGER := 0;
  FIRSTREGISTER     BOOLEAN        := TRUE;
  INSTANTIATING_PKG BOOLEAN        := TRUE;


FUNCTION MINIMUM(V1 NUMBER, V2 NUMBER) RETURN NUMBER IS
BEGIN
  IF V1 < V2 THEN
    RETURN V1;
  ELSE
    RETURN V2;
  END IF;
END;


PROCEDURE SET_DEFAULTS(SENSITIVITY IN NUMBER) IS
BEGIN
  IF SENSITIVITY >= 0 THEN
    P_INT := SENSITIVITY;
  END IF;
END;


PROCEDURE REGISTER(NAME IN VARCHAR2) IS
  STATUS  INTEGER;
  LSTATUS INTEGER;
  LOCKID  INTEGER;
  CURSOR  C1 IS
    SELECT DISTINCT SUBSTR(KGLNAOBJ,11) SID FROM X$KGLOB
     WHERE KGLHDNSP = 7
     AND   KGLNAOBJ LIKE 'ORA$ALERT$%'
     AND   BITAND(KGLHDFLG,128)!=0
    UNION
    SELECT DISTINCT SID FROM DBMS_ALERT_INFO;
BEGIN
  IF INSTANTIATING_PKG THEN
    REMOVEALL;
    INSTANTIATING_PKG := FALSE;
  END IF;
  IF (FIRSTREGISTER) THEN
    IF DBMS_UTILITY.IS_CLUSTER_DATABASE THEN
      FOR REC IN C1 LOOP
        LOCKID := DBMS_UTILITY.GET_HASH_VALUE(REC.SID, 2000002048, 2048);
        LSTATUS := DBMS_LOCK.REQUEST(LOCKID, DBMS_LOCK.X_MODE,
                     TIMEOUT => 0, RELEASE_ON_COMMIT => TRUE);
        IF LSTATUS = 0 THEN
          DBMS_PIPE.PURGE('ORA$ALERT$' || REC.SID);
          DELETE DBMS_ALERT_INFO WHERE SID = REC.SID;
          COMMIT;
        ELSIF LSTATUS NOT IN (1,2,4) THEN
          RAISE_APPLICATION_ERROR(-20000,
            'ORU-10025: lock request error, status: ' || TO_CHAR(LSTATUS));
        END IF;
      END LOOP;
      LSTATUS := DBMS_LOCK.REQUEST(DBMS_UTILITY.GET_HASH_VALUE(THIS_SESSION_ID,
        2000002048,
        2048),
        DBMS_LOCK.S_MODE, TIMEOUT => 60);
      IF LSTATUS != 0  AND LSTATUS != 4 THEN
        RAISE_APPLICATION_ERROR(-20000,
          'ORU-10021: lock request error, status: ' || TO_CHAR(LSTATUS));
      END IF;
    ELSE
      FOR REC IN C1 LOOP
        IF NOT DBMS_SESSION.IS_SESSION_ALIVE(REC.SID) THEN
          DBMS_PIPE.PURGE('ORA$ALERT$' || REC.SID);
          DELETE DBMS_ALERT_INFO WHERE SID = REC.SID;
          COMMIT;
        END IF;
      END LOOP;
    END IF;
    FIRSTREGISTER := FALSE;
  END IF;
  STATUS := DBMS_LOCK.REQUEST(DBMS_UTILITY.GET_HASH_VALUE(UPPER(NAME),
    2000000000, 2048), DBMS_LOCK.X_MODE,
    DBMS_LOCK.MAXWAIT, RELEASE_ON_COMMIT => TRUE);
  IF STATUS != 0 THEN
    RAISE_APPLICATION_ERROR(-20000,
      'ORU-10002: lock request error, status: ' || TO_CHAR(STATUS));
  END IF;
  INSERT INTO DBMS_ALERT_INFO VALUES (UPPER(REGISTER.NAME), THIS_SESSION_ID,
    'N', NULL);
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN COMMIT;
END;


PROCEDURE REMOVE(NAME IN VARCHAR2) IS
BEGIN
  IF INSTANTIATING_PKG THEN
    REMOVEALL;
    INSTANTIATING_PKG := FALSE;
  END IF;
  DELETE FROM DBMS_ALERT_INFO
   WHERE NAME  = UPPER(REMOVE.NAME)
     AND SID   = THIS_SESSION_ID;
  COMMIT;
END;


PROCEDURE PIPE_WAIT(MAXTIME NUMBER, CUMTIME IN OUT NUMBER) IS
  STATUS INTEGER;
  TMO    NUMBER := MAXTIME;
BEGIN
  IF PARALLEL THEN
    TMO := MINIMUM(TMO, P_INT);
  END IF;
  IF TMO = MAXWAIT THEN
    TMO := DBMS_PIPE.MAXWAIT;
  END IF;
  STATUS := DBMS_PIPE.RECEIVE_MESSAGE(SIGPIPE, TMO);
  IF STATUS = 1 THEN
    CUMTIME := CUMTIME + TMO;
    RETURN;
  END IF;
  IF STATUS <> 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'ORU-10015: error:' || TO_CHAR(STATUS)
      || ' waiting for pipe message.');
  END IF;
  RETURN;
END;


PROCEDURE OPTIMISTIC(
  NAME    OUT VARCHAR2,
  MESSAGE OUT VARCHAR2,
  STATUS  OUT INTEGER)
IS
  LOCKID  INTEGER;
  LSTATUS INTEGER;
  CURSOR  C1 IS
    SELECT NAME FROM DBMS_ALERT_INFO
     WHERE SID = THIS_SESSION_ID
     AND   CHANGED = 'Y';
BEGIN
  STATUS := 1;
  FOR REC IN C1 LOOP
    LOCKID := DBMS_UTILITY.GET_HASH_VALUE(REC.NAME, 2000000000, 2048);
    LSTATUS := DBMS_LOCK.REQUEST(LOCKID, DBMS_LOCK.SX_MODE, TIMEOUT => 0,
      RELEASE_ON_COMMIT => TRUE);
    IF LSTATUS <> 1 THEN
      IF LSTATUS <> 0 THEN
        RAISE_APPLICATION_ERROR(-20000, 'ORU-10019: error ' ||
          TO_CHAR(LSTATUS) || ' on lock request.');
      END IF;
      UPDATE DBMS_ALERT_INFO SET CHANGED = 'N'
       WHERE SID = THIS_SESSION_ID
       AND   NAME = REC.NAME;
      SELECT MESSAGE INTO MESSAGE FROM DBMS_ALERT_INFO
       WHERE SID = THIS_SESSION_ID
       AND   NAME = REC.NAME;
      COMMIT;
      DBMS_PIPE.PURGE(SIGPIPE);
      NAME := REC.NAME;
      STATUS := 0;
      RETURN;
    END IF;
  END LOOP;
  RETURN;
END;


PROCEDURE WAITANY(
  NAME    OUT VARCHAR2,
  MESSAGE OUT VARCHAR2,
  STATUS  OUT INTEGER,
  TIMEOUT IN  NUMBER    DEFAULT MAXWAIT)
IS
  WAITIME  NUMBER        := 0;
  CUMTIME  NUMBER        := 0;
  LOCKID   INTEGER;
  ST       INTEGER;
  LSTATUS  INTEGER;
  TIMEDOUT BOOLEAN;
  CHANGED  VARCHAR2(1);
  FOUNDONE BOOLEAN;
  CURSOR   C1 IS
    SELECT NAME FROM DBMS_ALERT_INFO
     WHERE SID = THIS_SESSION_ID;
BEGIN
  IF INSTANTIATING_PKG THEN
    REMOVEALL;
    INSTANTIATING_PKG := FALSE;
  END IF;
  OPTIMISTIC(NAME, MESSAGE, ST);
  IF ST = 0 THEN
    STATUS := ST;
    RETURN;
  END IF;
  WAITIME := 1;
  CUMTIME := 0;
  LOOP
    TIMEDOUT := FALSE;
    FOUNDONE := FALSE;
    FOR REC IN C1 LOOP
      FOUNDONE := TRUE;
      LOCKID := DBMS_UTILITY.GET_HASH_VALUE(REC.NAME, 2000000000, 2048);
      LSTATUS := DBMS_LOCK.REQUEST(LOCKID, DBMS_LOCK.SX_MODE, WAITIME,
        RELEASE_ON_COMMIT => TRUE);
      IF LSTATUS = 1 THEN
        OPTIMISTIC(NAME, MESSAGE, ST);
        IF ST = 0 THEN
          STATUS := 0;
          RETURN;
        END IF;
        CUMTIME := CUMTIME + WAITIME;
        IF CUMTIME >= TIMEOUT THEN
          STATUS := 1;
          RETURN;
        END IF;
        TIMEDOUT := TRUE;
        GOTO CONTINUE;
      ELSIF LSTATUS <> 0 THEN
        RAISE_APPLICATION_ERROR(-20000,
          'ORU-10020: error ' || TO_CHAR(LSTATUS) || ' on lock request.');
      ELSE
        SELECT CHANGED, MESSAGE INTO CHANGED, MESSAGE FROM DBMS_ALERT_INFO
         WHERE SID = THIS_SESSION_ID
         AND   NAME = REC.NAME;
        IF CHANGED = 'Y' THEN
          UPDATE DBMS_ALERT_INFO SET CHANGED = 'N'
           WHERE SID = THIS_SESSION_ID
           AND   NAME = REC.NAME;
          COMMIT;
          NAME := REC.NAME;
          STATUS := 0;
          DBMS_PIPE.PURGE(SIGPIPE);
          RETURN;
        END IF;
        LSTATUS := DBMS_LOCK.RELEASE(LOCKID);
      END IF;
      <<continue>>
      NULL;
    END LOOP;
    IF NOT FOUNDONE THEN
      RAISE_APPLICATION_ERROR(-20000,
        'ORU-10024: there are no alerts registered.');
    END IF;
    IF TIMEDOUT THEN
      WAITIME := MINIMUM(WAITIME*2, 32);
      WAITIME := MINIMUM(WAITIME, TIMEOUT-CUMTIME);
    ELSE
      PIPE_WAIT(TIMEOUT-CUMTIME, CUMTIME);
    END IF;
    IF CUMTIME >= TIMEOUT THEN
      STATUS := 1;
      RETURN;
    END IF;
  END LOOP;
END;


PROCEDURE WAITONE(
  NAME    IN  VARCHAR2,
  MESSAGE OUT VARCHAR2,
  STATUS  OUT INTEGER,
  TIMEOUT IN  NUMBER    DEFAULT MAXWAIT)
IS
  CUMTIME NUMBER  := 0;
  LOCKID  INTEGER := DBMS_UTILITY.GET_HASH_VALUE(UPPER(NAME),
    2000000000, 2048);
  LSTATUS INTEGER;
BEGIN
  IF INSTANTIATING_PKG THEN
    REMOVEALL;
    INSTANTIATING_PKG := FALSE;
  END IF;
  LOOP
    LSTATUS := DBMS_LOCK.REQUEST(LOCKID, DBMS_LOCK.SX_MODE, TIMEOUT-CUMTIME,
      RELEASE_ON_COMMIT => TRUE);
    IF LSTATUS = 1 THEN
      STATUS := 1;
      RETURN;
    END IF;
    IF LSTATUS = 4 THEN
      RAISE_APPLICATION_ERROR(-20000,
        'ORU-10037: attempting to wait on uncommitted signal from same session');
    END IF;
    IF LSTATUS <> 0 THEN
      RAISE_APPLICATION_ERROR(-20000,
        'ORU-10023: error ' || TO_CHAR(LSTATUS) || ' on lock request.');
    END IF;
    UPDATE DBMS_ALERT_INFO SET CHANGED = 'N'
     WHERE NAME    = UPPER(WAITONE.NAME)
       AND SID     = THIS_SESSION_ID
       AND CHANGED = 'Y';
    IF SQL%ROWCOUNT != 0 THEN
      SELECT MESSAGE INTO MESSAGE FROM DBMS_ALERT_INFO
       WHERE NAME    = UPPER(WAITONE.NAME)
         AND SID     = THIS_SESSION_ID;
      COMMIT;
      DBMS_PIPE.PURGE(SIGPIPE);
      STATUS := 0;
      RETURN;
    END IF;
    LSTATUS := DBMS_LOCK.RELEASE(LOCKID);
    PIPE_WAIT(TIMEOUT, CUMTIME);
    IF CUMTIME >= TIMEOUT THEN
      STATUS := 1;
      RETURN;
    END IF;
  END LOOP;
END;


PROCEDURE SIGNAL_PIPE(PIPENAME VARCHAR2) IS
  MSGID    VARCHAR2(40);
  TMPMSGID VARCHAR2(40);
  STATUS   INTEGER;
BEGIN
  MSGID := THIS_SESSION_ID || ':' || TO_CHAR(MSGSEQ);
  MSGSEQ := MSGSEQ + 1;
  DBMS_PIPE.PACK_MESSAGE(MSGID);
  STATUS := DBMS_PIPE.SEND_MESSAGE(PIPENAME);
  IF STATUS <> 0 THEN
    RAISE_APPLICATION_ERROR(-20000,
      'ORU-10016: error:' || TO_CHAR(STATUS) || ' sending on pipe ' ||
      PIPENAME);
  END IF;
  STATUS := DBMS_PIPE.RECEIVE_MESSAGE(PIPENAME, 0);
  IF STATUS = 1 THEN
    RETURN;
  END IF;
  IF STATUS <> 0 THEN
    RAISE_APPLICATION_ERROR(-20000,
      'ORU-10017: error:' || TO_CHAR(STATUS) || ' receiving on pipe ' ||
      PIPENAME);
  END IF;
  DBMS_PIPE.UNPACK_MESSAGE(TMPMSGID);
  IF TMPMSGID = MSGID THEN
    DBMS_PIPE.PACK_MESSAGE(MSGID);
    STATUS := DBMS_PIPE.SEND_MESSAGE(PIPENAME);
    IF STATUS <> 0 THEN
      RAISE_APPLICATION_ERROR(-20000,
        'ORU-10018: error:' || TO_CHAR(STATUS) || ' sending on pipe ' ||
        PIPENAME);
    END IF;
  END IF;
END;


PROCEDURE SIGNAL(NAME IN VARCHAR2, MESSAGE IN VARCHAR2) IS
  STATUS  INTEGER;
  CURSOR  C2(ALERTNAME VARCHAR2) IS
    SELECT SID FROM DBMS_ALERT_INFO
     WHERE NAME = UPPER(ALERTNAME);
BEGIN
  STATUS := DBMS_LOCK.REQUEST(DBMS_UTILITY.GET_HASH_VALUE(UPPER(NAME),
    2000000000, 2048), DBMS_LOCK.S_MODE,
    DBMS_LOCK.MAXWAIT, RELEASE_ON_COMMIT => TRUE);
  IF STATUS != 0  AND STATUS != 4 THEN
    RAISE_APPLICATION_ERROR(-20000,
      'ORU-10001: lock request error, status: ' || TO_CHAR(STATUS));
  END IF;
  UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = SIGNAL.MESSAGE
   WHERE NAME = UPPER(SIGNAL.NAME);
  IF DBMS_UTILITY.IS_CLUSTER_DATABASE THEN
    FOR REC IN C2(NAME) LOOP
      STATUS := DBMS_LOCK.REQUEST(DBMS_UTILITY.GET_HASH_VALUE(REC.SID,
        2000002048,
        2048),
        DBMS_LOCK.SX_MODE, TIMEOUT => 0,
        RELEASE_ON_COMMIT => TRUE);
      IF STATUS = 0 THEN
        DBMS_PIPE.PURGE('ORA$ALERT$' || REC.SID);
        STATUS := DBMS_LOCK.RELEASE(DBMS_UTILITY.GET_HASH_VALUE(REC.SID,
          2000002048,
          2048));
      ELSE
        IF STATUS != 1 AND STATUS != 4 THEN
          RAISE_APPLICATION_ERROR(-20000,
            'ORU-10022: lock request error, status: ' || TO_CHAR(STATUS));
        END IF;
        SIGNAL_PIPE('ORA$ALERT$' || REC.SID);
      END IF;
    END LOOP;
  ELSE
    FOR REC IN C2(NAME) LOOP
      IF  NOT DBMS_SESSION.IS_SESSION_ALIVE(REC.SID) THEN
        DBMS_PIPE.PURGE('ORA$ALERT$' || REC.SID);
      ELSE
        SIGNAL_PIPE('ORA$ALERT$' || REC.SID);
      END IF;
    END LOOP;
  END IF;
END;


PROCEDURE REMOVEALL IS
BEGIN
  DELETE FROM DBMS_ALERT_INFO WHERE SID = THIS_SESSION_ID;
  DBMS_PIPE.PURGE(SIGPIPE);
  COMMIT;
END;


END;

12.并发性
从源码可以看出, DBMS_ALERT是基于数据库表(DBMS_ALERT_INFO)和DBMS_LOCK, DBMS_PIPE实现的的
由于对表DBMS_ALERT_INFO操作前加上了锁, 是按警告的名称来申请锁的, 事务提交后才释放, 所以对警告的操作是串行的

比如, 有一个通知数据修改的触发器, 如果多个会话同时修改了数据, 同时触发, 发出警报. 这其中只能有一个会话申请到锁, 其它会话被阻塞. 申请到锁的会话发出警报, 释放锁后, 其它会话中一个会话申请锁成功,
剩下的会话还是被阻塞, ..., 依此类推
可以用DBMS_JOB解决此问题, DBMS_JOB是一种常见的, 使操作并行化的小技巧

但是, 即便解决了串行化问题, 大量并发的情况下, 频繁操作DBMS_ALERT_INFO表, 也会带来性能上的问题

外部链接:
dbms_alert
DBMS_ALERT: Broadcasting Alerts to Users
Using DBMS_ALERT To Notify Sessions Of Database Events
PACKAGE DBMS_ALERT Specification

Telling a Forms Application that a change has been made on the database.
介绍了如何让程序获知数据被改变的一些方法

这讲的是EnterpriseDB, 不知道是个啥数据库, 好像是Postgres什么一类的, 跟Oracle很像
DBMS_PIPE & DBMS_ALERT In EnterpriseDB

出处： http://www.cnblogs.com/zhangdongsheng/

作者：张东升

会话间通信 DBMS_ALERT

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

会话间通信 DBMS_ALERT

热门文章

最新文章

相关电子书