create or replace procedure p_test as
l_lock_id number;
resource_busy exception;
pragma exception_init(resource_busy,-54);
begin
if ( dbms_lock.request(id => 112233,
lockmode => dbms_lock.x_mode,
timeout => 0,
release_on_commit => TRUE ) <> 0 )
then
raise resource_busy;
end if;
dbms_lock.sleep(100);
commit;
l_lock_id number;
resource_busy exception;
pragma exception_init(resource_busy,-54);
begin
if ( dbms_lock.request(id => 112233,
lockmode => dbms_lock.x_mode,
timeout => 0,
release_on_commit => TRUE ) <> 0 )
then
raise resource_busy;
end if;
dbms_lock.sleep(100);
commit;
end;
session1:运行100s后结束
sql>call p_test();
session2:
sql> call p_test(); call p_test() * 第 1 行出现错误: ORA-00054: 资源正忙,但指定以 NOWAIT 方式获取资源,或者超时失效 ORA-06512: 在 "TEST.P_TEST",line 11
