Re: How to : only one user in a branch can do the batch process at a time ?

On Feb 22, 11:31 pm, krisl..._at_gmail.com wrote:
> dear gurus,
>
> In ADF web application, accessed by multiple branch (20), there is a
> batch process to create invoices from orders. There are 3-5 users in a
> branch. one user can process about 30 - 50 orders to become
> invoices.
> Business rule is : In one batch process, each users must produce
> invoices with gapless invoice number. e.g : user_1 create 30 invoices
> no.001 - 030, user_2 create 40 invoices no.031-070 ... and so on...
>
> So we want to protect that at a time, in one branch, only one user can
> do the batch process. User_2 must wait until user_1 is done to start
> the process.
>
> We use the table below to maintain the latest invoice number created :
> CREATE TABLE doc_number
> (
>   DOC_TYPE  VARCHAR2(6 BYTE)                    NOT NULL,
>   BRANCH_CODE VARCHAR2(6 BYTE)                    NOT NULL,
>   DOC_NBR   NUMBER(12)                          DEFAULT 0,
>   CONSTRAINT "DOC_NUMBER_PK" PRIMARY KEY (DOC_TYPE, DOC_NBR)
> )
>
> The question is :
> I think of locking a spicific row of the doc_number table (select ...
> for update wait 10) at the beginning of the batch process stored
> procedure.
> But what if the session/connection left orphan and the row is lockde
> forever before the dba kill it  ?
>
> So is there a better approach to limit the user execution of the
> process ?
>
> Thank you very much for your help,
> Krist

For single threading batch processes (even if invoked via a screen) we like the user lock feature. See the PL/SQL Packages and Types manual entry for dbms_lock.

Have the process lock request an exclusive user lock on the invoice number logical resource. Next your application would execute your select for update invoice routine, Finally when done release the user lock. You can code the get lock call to either wiat or immediately error off if the lock is in use.

HTH -- Mark D Powell -- Received on Sat Feb 23 2008 - 15:34:38 CST