一个插入操作可以让另一个 DDL 操作等待吗? [英] Can a insert operation make another DDL operation wait?

问题描述

我正在尝试了解出现以下错误的原因.

I am trying to understand the reason why i get the below error.

`ORA-04021: timeout occurred while waiting to lock object`

此错误是在运行命令 alter table alter table <<T_NAME>> 时从过程中抛出的.截断子分区<.

    v_dyncursor_stmt := 'with obj as (select /*+ materialize */ data_object_id, subobject_name from   user_objects where  object_name = UPPER(''' ||
                    p_table_name ||
                    ''') and object_type = ''TABLE SUBPARTITION'') select '||p_hint||' distinct subobject_name from ' ||
                    p_table_name || ' t, obj where data_object_id = DBMS_MView.PMarker(t.rowid) and ' || p_where;

/* log */
log_text(v_unit_name, 'INFO', 'Open cursor', v_dyncursor_stmt);

/* loop over partitions which needs to be truncated */
v_counter := 0;
open c_subpartitions for v_dyncursor_stmt;
loop
  FETCH c_subpartitions
    INTO v_subpartition_name;
  EXIT WHEN c_subpartitions%NOTFOUND;

  v_statement := 'alter table ' || p_table_name || ' truncate subpartition "' || v_subpartition_name || '"';

  execStmt(v_statement);

代码两次调用上述过程，第一次尝试成功.它可以很好地截断子分区.在第二次尝试中它失败了......下面给出了 execStmt 函数，错误是从 EXCEUTE IMMEDITE 行抛出的......

the code is calling above procedure twice and the first attempt is successful. it truncates the subpartition fine. In the second attempt it is failing... The execStmt function is given below, the error is thrown from EXCEUTE IMMEDITE line...

procedure execStmt(p_statement IN VARCHAR2) IS
  v_unit_name varchar2(1024) := 'execStmt';
v_simulate  varchar2(256);
begin
v_simulate := utilities.get_parameter('PART_PURGE_SIMULATE', '0');
if (v_simulate = '1') then
  log_text(v_unit_name, 'INFO', 'Statement skipped. (PART_PURGE_SIMULATE=1)', 
p_statement);
else
  /* log */
  log_text(v_unit_name, 'INFO', 'Executing statement', p_statement);

  EXECUTE IMMEDIATE p_statement;
end if;

end;

由于这主要发生在周末，我没有机会检查锁定表以查看锁定对象的内容.但我确信这是一张发生大量插入的表.所以我的问题是对表的插入操作可以防止上述 DDL 吗??

As this happens mostly over the weekend, i do not get a chance to inspect the lock tables to see what has locked the object. but i know for sure that it is a table which has alot of inserts happening. So my question is can an insert operation on a table prevent the above DDL ??

从oracle docs，我看到插入获取SX 锁，解释如下，

from oracle docs,i see that an insert aquires a SX lock which is explained as below,

行排他锁 (RX)，也称为子排他表锁 (SX)，表示持有该锁的事务已更新表行或发出 SELECT ... FOR UPDATE.SX 锁允许其他事务在同一表中同时查询、插入、更新、删除或锁定行.因此，SX 锁允许多个事务同时获取同一张表的 SX 和 SS 锁.

推荐答案

发生此错误是因为您尝试截断的分区当时正在使用中.而且正如你所说，这些插入语句正在运行，它会影响 DDL 操作.

This error happens because partition you are trying to truncate is in use at that time. And as mentioned by you, these insert statements are running that time, and it can affect DDL operation.

这篇关于一个插入操作可以让另一个 DDL 操作等待吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！