在PL/SQL中，我们有时候需要保证某个Procedure不被并发执行。一般采用select for update对一个固定数据表的数据行进行锁定。

除此之外，还可以使用dbms_lock包提供的request()和release()方法进行锁定。

 

下面例子的逻辑是，在插入A之前用count()判断，如果A中没有数据就插入。在高并发的环境下，没有锁定就可能造成多条插入。为了避免并发错误，使用dbms_lock进行手动控制。

drop table a; create table a(x int); drop table b; create table b(sid number, dt timestamp default systimestamp); create or replace procedure lock_thing is   l_result int;   l_cnt int; begin -- get the lock with ID=0   -- release_on_commit=false means keep lock until explicitly released or until end-of-session   l_result := dbms_lock.request(id => 0, release_on_commit => false); -------------------------------------------   -- do your own logic here   -------------------------------------------   if l_result = 0 then -- running log     insert into b(sid) values(sys_context('userenv','bg_job_id')); select count(1) into l_cnt from a; if l_cnt = 0 then insert into a(x) values(sys_context('userenv','bg_job_id')); end if; end if; commit; -- explicitly release it   l_result := dbms_lock.release(id => 0); exception when others then     l_result := dbms_lock.release(id => 0); end; /

 用一个程序模拟并发调用procedure，最后的结果是A中仅有一条数据。（NOTE: 这个并发模拟并不严格）

-- use some backgound jobs to simulate concurrent calls declare   l_job int; begin delete a; delete b; commit; for i in 1 .. 100 loop     dbms_job.submit(job => l_job, what => 'begin lock_thing(); end;'); commit; end loop; end; / -- only one row in table A select a.*, b.* from a, b where a.x(+)=b.sid order by b.sid;