12 Replies Latest reply: Apr 18, 2012 1:09 AM by Billy~Verreynne RSS

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

    tem
      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
          >
          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
            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
              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
                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
                  >
                  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
                    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
                      >
                      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
                        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
                          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
                            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?
                              Billy~Verreynne
                              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}.