触发器可以锁定吗?如何确定它是? [英] Can a trigger be locked; how would one determine that it is?

问题描述

在回答在我的应用程序运行时替换oracle触发器，我会错过任何更改吗?，我一直在寻找查看触发器是否已被INSERT语句锁定.不是，我在互联网上找不到任何可以触发的东西.

In answering Will I miss any changes if I replace an oracle trigger while my application is running?, I went looking to see if the trigger was locked by an INSERT statement. It wasn't and I can't find anything on the internet to suggest that a trigger can be locked.

如果我在一个会话中运行以下命令:

If I run the following in one session:

create table test_trigger (id number);
create table test_trigger_h (id number);

create or replace trigger test_trigger_t 
 after insert on test_trigger for each row
begin
  insert into test_trigger_h (id) values (:new.id);
end;    
/

insert into test_trigger
 select level
   from dual
connect by level <= 1000000;

然后在第二个会话中尝试找出正在发生的锁定，我得到以下信息:

and then in a second session try to find out what locks are occurring I get the following:

select object_name, object_type
     , case l.block
            when 0 then 'Not Blocking'
            when 1 then 'Blocking'
            when 2 then 'Global'
       end as status
     , case v.locked_mode
            when 0 then 'None'
            when 1 then 'Null'
            when 2 then 'Row-S (SS)'
            when 3 then 'Row-X (SX)'
            when 4 then 'Share'
            when 5 then 'S/Row-X (SSX)'
            when 6 then 'Exclusive'
            else to_char(lmode)
       end as mode_held
  from v$locked_object v
  join dba_objects d
    on v.object_id = d.object_id
  join v$lock l
    on v.object_id = l.id1
  join v$session s
    on v.session_id = s.sid
       ;

OBJECT_NAME          OBJECT_TYPE          STATUS          MODE_HELD
-------------------- -------------------- --------------- ---------------
TEST_TRIGGER         TABLE                Not Blocking    Row-X (SX)
TEST_TRIGGER_H       TABLE                Not Blocking    Row-X (SX)

根据Oracle，触发器是 not 被锁定.

According to Oracle, the trigger is not being locked.

但是，如果我尝试在INSERT语句运行时替换触发器，则直到该语句完成(不包括提交)后才替换触发器，这意味着触发器 已锁定

However, if I try to replace the trigger whilst the INSERT statement is running it will not be replaced until after the statement has completed (not including a commit), which implies that the trigger is locked.

在这种情况下，触发器是否已锁定?如果是，则如何确定触发器是

In this situation, is the trigger locked and if so how would one determine that it is?

推荐答案

要确定是否已锁定触发器(以及任何其他存储过程)，请使用

To determine if a trigger(as well as any other stored procedure) is locked or not, the V$ACCESS dynamic performance view can be queried.

Session #1

insert into test_trigger
 select level
   from dual
connect by level <= 1000000; 

Session #2

SQL> select *
  2    from v$access
  3   where object = upper('test_trigger_t')
  4  ;


Sid  Owner  Object         Type    Con_Id 
--------------------------------------
441  HR     TEST_TRIGGER_T TRIGGER  3 

这类锁是库高速缓存管脚(库高速缓存锁是资源(TM锁类型)锁)，需要确保在执行会话时防止对象被修改.它.

Those kinds of locks are library cache pins(library cache locks are resource(TM type of lock) locks), needed to ensure that an object is protected from being modified while session is executing it.

--session sid # 441
insert into test_trigger
  select level
    from dual
 connect by level <= 1000000;


-- session sid #24
create or replace trigger test_trigger_t 
after insert on test_trigger for each row
begin
  insert into test_trigger_h (id) values (:new.id);
end;  

-- Session # 3
select vs.sid
     , vs.username
     , vw.event
  from v$session       vs
  join v$session_wait  vw
    on (vw.sid = vs.sid)
  join v$access        va
    on (va.owner = vs.username)
 where vs.username = 'HR'

结果:

Sid Username Event 
--------------------------
24 HR library cache pin 
....
441 HR log file switch (checkpoint incomplete) 

在这里我们可以看到会话#441等待日志文件切换，而会话#24等待库高速缓存引脚.

Here we can see that the session #441 waits for a log file switching and session #24 waits for library cache pin.

这篇关于触发器可以锁定吗?如何确定它是?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！