Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2008

    Unanswered: synchronous calling of stored procedure

    Hi all,
    I writing a stored procedure (say P1) in which I have some business logic and from that procedure I am calling another procedure (say P2). The P1 procedure will be called from multiple instances. Now I want that at any given time the P2 procedure should be called by 1 instance only. i.e., even though if two instaces call the P2 procedure then that procedure should be execute one by one for those instances.
    Please help me in how to achieve this functionality.

    Thanks in advance,

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    You can do this by obtaining a lock. The simplest is to obtain a lock on a row of some table:

    procedure p2 is
      select col from some_table where pk=...
      for update;
      -- Now continue
    The second call to P2 will have to wait for the other caller's transaction* to finish before it can continue.

    Alternatively, you can use the DBMS_LOCK package to define your own locking mechanism.

    * Note: my p2 code above locks a record in some_table for the duration of the whole transaction, not just the procedure call. This may be too much. You could use an autonomous transaction to prevent this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts