分配IP地址--朝阳提出的问题(2) (Give you some color to see see!)

按照昨天讨论的方法，我写了一个简单的存储过程模拟申请IP的过程，并请朝阳看看，有没有什么问题。

我的大概步骤是这样的：

create table test_ip_table(id number(9) primary key, ip_addr varchar2(20), used varchar2(3));create table test_ip_table_log(seq number, id number, sleep_time number, apply varchar2(6), now_used number, release number); create sequence SEQ_test_ip_tableminvalue 1maxvalue 999999999999999start with 1increment by 1cache 10;
insert into test_ip_tableselect rownum,'192.168.0.'||rownum,'N'from dba_tables awhere rownum<=5;commit;

用于申请IP的过程:

create or replace procedure proc_test_ip_table(num in number) is n_seq number(9); n_id number(9); v_state varchar2(3); v_state2 varchar2(3); v_result varchar2(6); n_sleep number(9,3); -- v_cnt number; n_release_id number; n_rn number; -- v_msg varchar2(2000); begin --PART 1 申请IP begin select SEQ_test_ip_table.Nextval into n_seq from dual; select mod(n_seq,num) into n_id from dual; select used into v_state from test_ip_table where id = n_id; if(v_state='N') then --随机休眠一段时间，模拟未及时提交 select trunc(dbms_random.value(0,20),2) into n_sleep from dual; dbms_lock.sleep(n_sleep); --置已用标志 --再次判断改IP是否被人使用 select used into v_state2 from test_ip_table where id = n_id; if v_state='N' then update test_ip_table set used='Y' where id=n_id; v_result:='SUCC';--成功申请 commit; else v_result:='FAIL';--未成功提交 end if; else v_result:='CANNOT';--IP不可用 end if; select count(*) into v_cnt from test_ip_table where used='Y'; insert into test_ip_table_log(seq,id,sleep_time,apply,now_used) values(n_seq,n_id,n_sleep,v_result,v_cnt); commit; --PART 2 随机释放一个目前在用的IP if (sign(dbms_random.value(-1,1))>=0) then select count(*) into v_cnt from test_ip_table where used='Y'; if v_cnt>0 then select trunc(dbms_random.value(1,v_cnt)) into n_rn from dual; if n_rn >0 then select id into n_release_id from (select rownum rn,id from test_ip_table where used='Y') where rn=n_rn; end if; update test_ip_table set used='N' where id=n_release_id; update test_ip_table_log set release=n_release_id where seq=n_seq; end if; commit; end if; exception when others then rollback; v_msg:=sqlerrm; end; end proc_test_ip_table;

然后测试时，开10个session同时执行proc_test_ip_table(5);分别若干次。

纪录的log如下：SQL> select * from test_ip_table_log; SEQ ID SLEEP_TIME APPLY NOW_USED RELEASE---------- ---------- ---------- ------ ---------- ---------- 491 1 0.95 SUCC 1 496 1 CANNOT 1 1 494 4 2.54 SUCC 1 497 2 16.16 SUCC 2 499 4 CANNOT 2 493 3 20.96 SUCC 3 3 502 2 CANNOT 2 498 3 18.41 SUCC 3 504 4 CANNOT 3

原来考虑可能会出现的v_result:='FAIL';–未成功提交并没有出现