urgent help needed with record locking problem

To all Oracle Guru's:

We need some help in a bad way. The situation is this: We are a VAX shop with a Cobol flat file system (RMS) that is migrating our existing code to Oracle. In our Cobol programs we've replaced our flat file I/O's with embedded SQL to handle database I/O. Our problem does not neccessarily have to do with Cobol, Oracle or VAX/VMS, this is a relational database design/coding issue.

Our problem scenario is this:
We run program A that does a select for update on certain tables to "lock" certain rows. Certain fields for these tables will be populated or updated with new information. Before program A will write or update this information (and commit it) we call another cobol program (program B) to process information. This "called" program will write or update data in the database which will be commited
to the database before it's program execution ends. When Program B is finished and control is returned to program A the record locks for program A are gone. This is because of program B doing a commit on it's own database updates. This can cause many potential problems in our system if we can't maintain our record locks.

Obviously we'd like to find a solution with minimal design and/or coding impact but some of the solutions that we've come up with have significant impact on changing our system. What we've come up with is:

1. use a separate flat file or table that stores what rows in what tables and what user has "locked". this has significant coding/design impact.
2. remove functionality from the system. i.e. redesign code so it doesnt have to call program B. This prevents us from delivering a

    functionally equivalent system.

Any help would be greatly appreciated. We are under a severe deadline and need to wrap up our system testing a.s.a.p.

Thanks in advance.

Please send email to:
michael.depace<nospam>@lendersservice.<nospam>com Received on Tue Jan 12 1999 - 12:30:52 CST