This discussion is archived
12 Replies Latest reply: Apr 17, 2012 11:09 PM by BillyVerreynne RSS

possible to lock stored procedure so only one session may run it at a time?

tem Newbie
Currently Being Moderated
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?

Edited by: tem on Apr 17, 2012 3:20 PM
  • 1. Re: possible to lock stored procedure so only one session may run it at a time?
    rp0428 Guru
    Currently Being Moderated
    >
    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?

    What you are asking about seems like transaction control where you want to serialize access to the procedure.

    For that you should create a control table. Then modify the procedure to check a record in the control table to see if the procedure is currently being executed.
    If not, update the control record status to IN_PROCESS or similar. At the end of the procedure update the control record status to NULL or COMPLETE or something. Make sure the procedure exception handler also clears the control record status as needed.

    Now the procedure cannot execute if it is already executing since the first thing it does is checks the control status, sees the status as IN_PROCESS and just exits without executing.
  • 2. Re: possible to lock stored procedure so only one session may run it at a time?
    Tubby Guru
    Currently Being Moderated
    rp0428 wrote:
    >
    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?
    Not true.

    You can use the DBMS_LOCK package to serialize access to a procedure if you really want to.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lock.htm#ARPLS66769

    Routines of interest would be
    REQUEST
    RELEASE (assuming you passed a value of false for release_on_commit in the request routine)
    and
    ALLOCATE_UNIQUE (if you were interested in taking out "named locks").

    You can find an example here
    http://rwijk.blogspot.ca/2008/07/scalability-of-dbmslockrequest.html

    One word of caution if you're going to utilize the ALLOCATE_UNIQUE routine to do this, as documented the routine does a COMMIT so you'd want to set up some code to do this in an autonomous transaction (a simple routine that does NOTHING more than create a named lock).

    Cheers,
  • 3. Re: possible to lock stored procedure so only one session may run it at a time?
    tem Newbie
    Currently Being Moderated
    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?
  • 4. Re: possible to lock stored procedure so only one session may run it at a time?
    Tubby Guru
    Currently Being Moderated
    tem wrote:
    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?
    I did a quick google search and this seems a little "easier" than the previous post.
    http://jeffkemponoracle.com/2005/10/19/user-named-locks-with-dbms_lock/

    It's really not that complicated, though i would expect you'll have to spend a bit of time reading to understand what each routine does and how to fit that in to your needs.

    Cheers,
  • 5. Re: possible to lock stored procedure so only one session may run it at a time?
    rp0428 Guru
    Currently Being Moderated
    >
    Is it possible to provide a very simple example how to implement such a lock?
    >
    Funny you should ask. I wanted to check out Tubby's suggestion also so did a very simple (i.e. non-useable) example.
    -- 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;
    Now call EXEC TEST_LOCK from two different sessions.

    The second session will time out after 2 seconds. Keep running it and once the first session releases the lock the second will execute.

    Thanks for the question - and thanks Tubby for suggesting DBMS_LOCK - I hadn't used it for this purpose before.
  • 6. Re: possible to lock stored procedure so only one session may run it at a time?
    tem Newbie
    Currently Being Moderated
    Thanks for the updated answers. Much clearer now. Also, thanks for including the EXCEPTION in the code.

    Edited by: tem on Apr 17, 2012 6:19 PM
  • 7. Re: possible to lock stored procedure so only one session may run it at a time?
    rp0428 Guru
    Currently Being Moderated
    >
    Also, thanks for including the EXCEPTION in the code.
    >
    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.
  • 8. Re: possible to lock stored procedure so only one session may run it at a time?
    Tubby Guru
    Currently Being Moderated
    rp0428 wrote:
    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.
    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 :)

    One thing to note about the exception handler, you'd definitely need to ensure that any exceptions release the lock, unless you used TRUE for the release_on_commit parameter when obtaining a lock. When you specify TRUE a COMMIT or ROLLBACK will release the lock. There are times when you need to keep a lock throughout COMMITs in a routine, but hopefully that's the exception and not the norm.

    Cheers,
  • 9. Re: possible to lock stored procedure so only one session may run it at a time?
    tem Newbie
    Currently Being Moderated
    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
  • 10. Re: possible to lock stored procedure so only one session may run it at a time?
    Tubby Guru
    Currently Being Moderated
    tem wrote:
    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
    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.

    I'm not trying to be unhelpful here, just the opposite. The best way for you to learn is hands on ... or so i have found over my career.
  • 12. Re: possible to lock stored procedure so only one session may run it at a time?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    tem wrote:
    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?
    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.

    Such as in thread {message:id=9388540}.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points