oracle 9i r2 锁争用：DDL 和 DML 和 LOCK TABLE tablename IN exclusive mode

Question

--建表
-- Create table
create table ACRM_ERRLOG
(
  ERR_INFO VARCHAR2(4000),
  ERR_TIME DATE
)
tablespace USER01
;
-- Create/Recreate indexes 
create index IDX_ACRM_ERRLOG on ACRM_ERRLOG (ERR_INFO)
  tablespace USER01
;
--DML操作块
  DECLARE
    v_errormsg VARCHAR2(200);
    v_count     NUMBER;
  BEGIN
    v_errormsg := 0;
    v_count    := 5;    LOOP
      BEGIN
        v_errormsg := 1;
        INSERT INTO acrm_errlog
        SELECT v_count,to_date('20100903', 'yyyymmdd')
          FROM ifsap_bankctl
        ;
        COMMIT;
        DBMS_LOCK.SLEEP(1);
        v_errormsg := 2;
        UPDATE acrm_errlog
           SET err_time = to_date('20100903', 'yyyymmdd')
         WHERE err_info = v_count;
        COMMIT;
        DBMS_LOCK.SLEEP(1);
      END;
      v_count := v_count + 1;
      EXIT WHEN v_count = 1000;
      if v_count = 10 then
        dbms_output.put_line(v_count);
      end if;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(v_errormsg);
      ROLLBACK;
      RAISE;
  END;
--DDL操作块  DECLARE
    v_errormsg VARCHAR2(200);
    v_count    NUMBER;
  BEGIN
    v_errormsg := 0;
    v_count    := 0;
    LOOP
      v_errormsg := 10;
      LOCK TABLE ACRM_ERRLOG IN exclusive mode ;
      --DBMS_LOCK.SLEEP(1);
      v_errormsg := 11;
      EXECUTE IMMEDIATE 'drop index IDX_ACRM_ERRLOG';
      DBMS_LOCK.SLEEP(1);
      v_errormsg := 12;
      LOCK TABLE ACRM_ERRLOG IN exclusive mode ;
      --DBMS_LOCK.SLEEP(1);
      v_errormsg := 13;
      EXECUTE IMMEDIATE 'create index IDX_ACRM_ERRLOG on ACRM_ERRLOG (err_info)';
      DBMS_LOCK.SLEEP(1);
      v_errormsg := 1;
      LOCK TABLE ACRM_ERRLOG IN EXCLUSIVE MODE;
      --DBMS_LOCK.SLEEP(1);
      v_errormsg := 2;
      EXECUTE IMMEDIATE 'rename ACRM_ERRLOG  to ACRM_ERRLOGX';
      v_errormsg := 3;
      EXECUTE IMMEDIATE 'rename ACRM_ERRLOGX to ACRM_ERRLOG ';
      DBMS_LOCK.SLEEP(1);
      v_count := v_count + 1;
      EXIT WHEN v_count = 1000;
      if v_count = 10 then
        dbms_output.put_line(v_count);
      end if;
    END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(v_errormsg);
      ROLLBACK;
      RAISE;
  END;
/*
问题：两个块同时运行，假设DML块一直都在运行，如果在运行DDL块的时候，保证DDL块能正确操作，不返回错误？
      我尝试在DDL操作前加了 LOCK TABLE ACRM_ERRLOG IN exclusive mode ;，但是不起作用。
      DDL块还可能会获得错误，而中断程序。
      测试发现如果不加延时，两个块无论谁先执行，另外一个块就无法执行。而且还造成死锁一次，手动解锁。
      大家有什么好的建议么？
*/

Answer 1

补充一下错误信息：ORA-00054: resource busy and acquire with NOWAIT specified
我理解不了为什么我加LOCK TABLE ACRM_ERRLOG IN exclusive mode后，还能返回错误信息。

Answer 2

再补充一下，如果我单独执行一个
dml 语句，未提交，然后再执行那个DDL块，就会一直等待到dml提交。
但是在手动条件下成功。

Answer 3

/*
ADDR KADDR                           SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
23 C00000009C37FDF8 C00000009C37FF70 73 TX 131092 220498 6 0 1092 0
24 C00000009C30CB08 C00000009C30CB30 73 TM 169756 0 3 0 1092 1
25 C00000009C30CBC8 C00000009C30CBF0 74 TM 169756 0 0 6 1064 0
*/
select * from v$lock;
/*
可以看见dml操作的sid 拥有两个锁，一个TX,一个TM。
*/

Answer 4

DECLARE
  v_errormsg VARCHAR2(200);
  v_count    NUMBER;
  v_sessid   VARCHAR2(50);
BEGIN
  v_errormsg := 0;
  v_count    := 0;
  LOOP
    v_errormsg := 1;
    BEGIN
      << kill_lock >>
      LOOP
        BEGIN
          BEGIN
          SELECT s.sid || ',' || s.serial# INTO v_sessid
            FROM v$locked_object v
                ,dba_objects     d
                ,v$lock          l
                ,v$session       s
           WHERE v.object_id = d.object_id
             AND v.object_id = l.id1
             AND v.session_id = s.sid
             AND d.object_name = 'ACRM_ERRLOG'
             AND rownum = 1;
          EXCEPTION
            WHEN OTHERS THEN 
              EXIT;
          END;
        EXECUTE IMMEDIATE 'alter system kill session '||''''||v_sessid||'''';
        EXCEPTION
          WHEN OTHERS THEN
            NULL;
        END;
      END LOOP;
    END;
    v_errormsg := 2;
    BEGIN
      EXECUTE IMMEDIATE 'rename ACRM_ERRLOG  to ACRM_ERRLOGX';
      EXCEPTION
        WHEN OTHERS THEN
          GOTO kill_lock;
    END;
    v_errormsg := 3;
    EXECUTE IMMEDIATE 'rename ACRM_ERRLOGX to ACRM_ERRLOG ';
    DBMS_LOCK.SLEEP(1);
    v_count := v_count + 1;
    EXIT WHEN v_count = 1000;
    IF v_count = 10 THEN
      dbms_output.put_line(v_count);
    END IF;
  END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(v_errormsg);
      ROLLBACK;
      RAISE;
END;
/*
根据实际环境，把DDL部分修改了。杀掉DML锁进程。
因为DML部分是我模仿前端程序对数据库的操作。
如果大家有不杀进程的方法，欢迎提供。
参考文档：一些 ORA-00054: resource busy and acquire with NOWAIT specified 文章
*/

Answer 5

执行DDL时，oracle都会先commit本session的事务，也就是说你之前的lock table，在ddl运行时就释放了其实你的杀进程的办法只是降低了被其他session锁表的可能性，实际在你杀玩进程后，运行ddl前，依然会有新的锁加上去的可能性建议把ddl操作安排在业务空闲时间如果要根除报错的话，只能考虑将对该表进行加锁的应用暂停，等该ddl程序完成后再启用