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

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,

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 Received on Fri Feb 22 2008 - 22:31:19 CST