rp0428 wrote:Not true.
Is it possible in 11.2 Oracle database, perhaps using DBMS_LOCK, to lock a stored procedure so only a single session may run this procedure at any moment in time?
No - what is it you are trying to accomplish?
tem wrote:I did a quick google search and this seems a little "easier" than the previous post.
Thanks Tubby, I thought so. But the example you linked to is quite complex. Is it possible to provide a very simple example how to implement such a lock?
Now call EXEC TEST_LOCK from two different sessions.
-- function to get a handle - this could be in a package - the AUTONOMOUS_TRANSACTION is only needed if you care about the COMMIT that gets done. create or replace function get_lock_handle (p_name IN varchar2) return VARCHAR2 is --PRAGMA AUTONOMOUS_TRANSACTION; v_handle VARCHAR2(2000); begin DBMS_LOCK.ALLOCATE_UNIQUE (P_NAME, v_handle); RETURN v_handle; end; -- actual procedure you want to serialize create or replace procedure test_lock is v_handle VARCHAR2(2000); v_lock_result INTEGER; v_i INTEGER; begin v_handle := get_lock_handle('test_lock'); -- here you could pass the name of the procedure as the name of the lock v_lock_result := dbms_lock.request(v_handle, timeout => 2); -- only wait 2 seconds to get the lock if (v_lock_result = 0) then v_i := 1; loop if (v_i = 10) then exit; end if; v_i := v_i + 1; dbms_output.put_line ('procedure is executing'); dbms_lock.sleep(5); end loop; else dbms_output.put_line('procedure is locked'); return; end if; v_lock_result := DBMS_LOCK.RELEASE(v_handle); EXCEPTION WHEN OTHERS THEN v_lock_result := DBMS_LOCK.RELEASE(v_handle); end;
rp0428 wrote:Thanks for demo-ing that out ! I've written this code a couple of times before and was headed out the door when i last posted so didn't have the time or the inclination to write it out again :)
No problem - but don't ever code an exception handler like that using a WHEN OTHERS. I only did that to illustrate that you need to make sure that the procedure ALWAYS releases the lock - don't leave any holes where you return without releasing the lock.
An exception handler should be coded to properly log and/or handle the exceptions that might arise; never just make it disappear like this example does.
tem wrote:Test it out and see, you've got all the code RP nicely supplied for you. You just need to spend 10 minutes coming up with a test case to check it out.
Thanks so much Tubby, if I change to use TRUE for the release_on_commit parameter, I would still need the PRAGMA AUTONOMOUS_TRANSACTION in the "get_handle" function because that allows me to do the COMMIT, right?
Edited by: tem on Apr 17, 2012 8:30 PM
tem wrote:It is always worth one's while to first research a subject issue - and chances are good that it has been discussed numerous time before.
Is it possible in 11.2 Oracle database, perhaps using DBMS_LOCK, to lock a stored procedure so only a single session may run this procedure at any moment in time? If so, how to do it?