Posts
会话间通信 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 1CHANGED: 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
-fin-
No comments:
Post a Comment