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的机制使得当发生变化时, 数据库可以主动的去通知应用程序 参考文档: : 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 sysdbagrant execute on dbms_alert to a;2. 新打开一个会话, 接收警报
conn a/aset pages 50000 line 130set 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 NSQL> desc dbms_alert_info Name Null? Type ----------------------------------------- -------- ---------------------------- NAME NOT NULL VARCHAR2(30) SID NOT NULL VARCHAR2(30) CHANGED VARCHAR2(1) MESSAGE VARCHAR2(1800)SQL>NAME : 警报名. 可以有同名的, 也就是说可以有多个会话注册和接收同一个警报 SID : 等于DBMS_SESSION.UNIQUE_SESSION_ID. 分为3个部分, 是 SID + SERIAL# + InstanceNumber 见
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 1CHANGED: N:没有发出警报或警报已被接收 Y:警报已发出还未被接收 MESSAGE: 警报消息 4. 注册完后, 等待接收警报
var v_messge varchar2(1000)var v_status numberexec dbms_alert.waitone('alert_test', :v_messge, :v_status);print :v_status :v_messge 调用waitone后没有返回, 一直在等待警报 5. 打开另一个会话, 发出警报 conn a/aset pages 50000 line 130set serveroutput on size unlimitedexec dbms_alert.signal('alert_test', 'hello world!');commit; 事务提交后才发出警报 6. 等待接收警报的会话接收到了警报, 打印出信息 SQL> print :v_status :v_messge V_STATUS---------- 0V_MESSGE----------------------------------------------------------------------------------------------------------------------------------hello world!查询dbms_alert_info信息
SQL> select * from dbms_alert_info;NAME SID C------------------------------ ------------------------------ -MESSAGE--------------------------------------------------------------------------------ALERT_TEST 065C00C00001 Nhello world!这里CHANGED还是N, 因为警报发出后立刻被收到了 如果发出的警报没有被收到, CHANGED是Y 7. 问题:注册时挂起 如果注册警报调用dbms_alert.register时就停住了,没有返回 说明之前有别的会话发出了警告,但没有提交事务, 这样注册过程就被阻塞住了 只要提交或回滚该事务即可解决 参考: 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 Yhello 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---------- 0V_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 Nhello 2参考文档 上说重新注册,再删除就能删掉 因为前一个会话已经退出了, 这时重新注册oracle会自动删除原记录并增加一条新的(或者说是覆盖), 所以可以删除 10. 删除管道 虽然删除了警报, 但是, dbms_alert创建的管道仍在系统中, 没有删除. 查看v$db_pipes:
SQL> col name for a40SQL> 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 1687SQL>这是因为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 ISBEGIN IF V1 < V2 THEN RETURN V1; ELSE RETURN V2; END IF;END;PROCEDURE SET_DEFAULTS(SENSITIVITY IN NUMBER) ISBEGIN 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) ISBEGIN 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; < > 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 ISBEGIN 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表, 也会带来性能上的问题 外部链接: 介绍了如何让程序获知数据被改变的一些方法 这讲的是EnterpriseDB, 不知道是个啥数据库, 好像是Postgres什么一类的, 跟Oracle很像