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

[being21]

--建表
-- 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块还可能会获得错误，而中断程序。
      测试发现如果不加延时，两个块无论谁先执行，另外一个块就无法执行。而且还造成死锁一次，手动解锁。
      大家有什么好的建议么？
*/

[being21]

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

[being21]

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

[being21]

/*
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。
*/

[being21]

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 文章
*/

[being21]

居然没人关注···

[minitoy]

我比较好奇你为什么能连那么多,还有就是消失的3楼去了哪儿

[epsilon-delta]

执行DDL时，oracle都会先commit本session的事务，也就是说你之前的lock table，在ddl运行时就释放了

其实你的杀进程的办法只是降低了被其他session锁表的可能性，实际在你杀玩进程后，运行ddl前，依然会有新的锁加上去的可能性

建议把ddl操作安排在业务空闲时间

如果要根除报错的话，只能考虑将对该表进行加锁的应用暂停，等该ddl程序完成后再启用

[being21]

回复3楼：
实际上真不该做这个测试，做这个研究。
我们的系统是每晚都有批量运行，白天是给客户使用的系统。
每晚8点批量运行。
这不是一个规范化的表，实际表有100多个字段，300万记录。
每天批量都会把300 万记录做一次更新。
大约25分钟执行时间。
和前端讨论过，批量运行，前端禁止访问批量操作表，被认为改动太大，不可行。
有个使用者，嫌我们系统慢（此系统，没人说快），让人设计了一个类似于刷票程序的程序，来执行需要重复操作的软件。晚上不定哪天就挂起来和我们批量一起在晚上跑。
和负责的业务人员讨论过，业务人员表示，无法约束这种行为，虽然我们知道是谁在做。建议我们自己禁止他操作。
太矛盾了，应该做的都不做，不该做的却在这里考虑。
所以···
如果程序设计人员能有好的数据库知识，这一切都不会发生，发生了的时候，实在是无解了。