ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 死锁一例:自己锁自己(执行catproc后)

死锁一例:自己锁自己(执行catproc后)

原创 Linux操作系统 作者:wqhhust 时间:2008-03-11 21:06:54 0 删除 编辑
oracle中配置的streams工作不正常了,察看系统对象时,发现有一些对象处于无效状态。于是执行catproc,但在执行过程中出现了死锁。

原因是有一些系统级别的触发器,对任何一个对象执行编译,修改,删除动作时,就会触发这些触发器。执行catproc后,一些对象被replace掉,比如view,那么基于这些view的对象就会被invalidate,这时oracle会尝试重新编译这些无效对象,不幸的是,这些系统级别的触发器也处于无效状态。所以系统首先得编译这些无效的触发器,这让就形成了死锁了。

执行catproc时的错误信息如下:
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object SYS.AFTER_ALTER


CREATE SEQUENCE sys.aq$_iotenqtxid START WITH 1 CACHE 1000
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object SYS.AFTER_ALTER

以下是发现死锁后做的一些尝试:

SQL> alter trigger sys.alter_after compile;
alter trigger sys.alter_after compile
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_ALTER_CTABLE_BEFORE
ORA-04098: trigger 'SYS.AFTER_ALTER' is invalid and failed re-validation

SQL> alter trigger sys.alter_after disable;
alter trigger sys.alter_after disable
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_ALTER_CTABLE_BEFORE
ORA-04098: trigger 'SYS.AFTER_ALTER' is invalid and failed re-validation


SQL> drop trigger sys.alter_after;
drop trigger sys.alter_after
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of
SYS.CDC_DROP_CTABLE_BEFORE
ORA-04098: trigger 'SYS.AFTER_ALTER' is invalid and failed re-validation

SQL> drop trigger cdc_drop_ctable_before;
drop trigger cdc_drop_ctable_before
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.AW_DROP_TRG
ORA-04098: trigger 'SYS.AFTER_ALTER' is invalid and failed re-validation


触发器after_alter 的代码如下

CREATE OR REPLACE TRIGGER after_alter AFTER ALTER on database
BEGIN
IF (ora_dict_obj_type='USER') THEN
insert into event_table
values (ora_sysevent,
ora_login_user,
ora_instance_num,
ora_database_name,
ora_dict_obj_name,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_des_encrypted_password,
sysdate);
END IF;
END;
/

如何解决这个死锁呢?oracle有一个隐含参数 _SYSTEM_TRIG_ENABLED,将该参数设置为false:
_SYSTEM_TRIG_ENABLED = FALSE
这样在运行catproc时,如果系统尝试编译无效对象,就不会触发上述的系统级别处发起了。

教训:执行catproc很安全吧,oracle support都经常如此建议。但在没有搞清楚系统之前,还是小心的好。不然出现这种类型的死锁,就必须宕机。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30426/viewspace-203995/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2008-03-09

  • 博文量
    9
  • 访问量
    14367