nowait_dml结合.txt

orale培训教材包括了所有的sql说明和实例

create or replace procedure set_order ( price in number, empno char, stime date)
is 
  id number:=0;
begin 
  select count(*) into id from sm_saleorderlist;
  id := id + 1;
  insert into sm_saleorderlist values (id , price , empno, stime);
  commit;
end;

call set_order(22,'0000000003',sysdate);

--有无问题？并发！在select count(*) into id from sm_saleorderlist;后有人
--insert into sm_saleorderlist values ..并commit.当执行到
--insert into sm_saleorderlist values (id , price , empno, stime);id值未更新。插入可能出问题。

create or replace procedure set_order ( price in number, empno char, stime date)
is 
  id number:=0;
begin
  --先lock住表避免被人插入
  --lock table sm_saleorderlist in row exclusive mode ; 
	--此处要避免其它session dml,必须用table级的lock. row级达不到要求
    lock table sm_saleorderlist in share mode ;
    --但in share mode nowait不能使控制立刻返回。
  select count(*) into id from sm_saleorderlist;
  id := id + 1;
  insert into sm_saleorderlist values (id , price , empno, stime);
  commit;
end;

call set_order(22,'0000000003',sysdate);


---------------------------------nowait 和lock --------------------------
ORA-00054 resource busy and acquire with NOWAIT specified

Cause: The NOWAIT keyword forced a return to 
the command prompt because a resource was unavailable for 
a LOCK TABLE or SELECT FOR UPDATE command.

Action: Try the command after a few minutes or 
enter the command without the NOWAIT keyword.
使用NOWAIT将立刻返回一个错误信息，控制权回到用户。
不用NOWAIT，则LOCK住，控制权不会用户直到其它SESSION  COMMIT!

--sqlplus1
lock table a in share mode;


--sqlplus2
declare 

begin
  lock table a in row exclusive mode nowait;
  insert into a values ('9');
end;

ERROR 位于第 1 行:
ORA-00054: 资源正忙，要求指定 NOWAIT
ORA-06512: 在line 4
--出现如下err信息，不被lock住。控制立刻返回用户。

declare 

begin
  --lock table a in row exclusive mode nowait;
  insert into a values ('9');
end;
----没err信息，被lock住。

结论：在lock机制中，可以用lock table table名 in .. mode nowait使数据并发冲突
时控制立刻返回用户。




------------------------------nowait-------------------------------
create or replace procedure set_order ( price in number, empno char, stime date)
is 
  id number:=0;
begin
  --先lock住表避免被人插入
  --lock table sm_saleorderlist in row exclusive mode nowait; 
	--此处要避免其它session dml,必须用table级的lock. row级达不到要求
    lock table sm_saleorderlist in share mode nowait;
   
  select count(*) into id from sm_saleorderlist;
  id := id + 1;
  insert into sm_saleorderlist values (id , price , empno, stime);
  commit;
end;

--试验：
sqlplus(1)

  insert into sm_saleorderlist values(99,1,1,sysdate);
--不commit;

sqlplus(2)
SQL>  call set_order(22,'0000000003',sysdate);
 call set_order(22,'0000000003',sysdate)
      *
ERROR 位于第 1 行:
ORA-00054: 资源正忙，要求指定 NOWAIT
ORA-06512: 在"SCOTT.SET_ORDER", line 8
ORA-06512: 在line 1
--SET_ORDER 中的lock table sm_saleorderlist in share mode nowait;起作用。控制立刻返回