Re: urgent help needed with record locking problem

Is it possible to treat A and B as one transaction? If so, remove the commit from
B and place it within A's scope. This will maintain you're described for update record
locking scenario keeping your concurrency model intact.

If A and B can't be considered together as a transaction then I'll assume A and B are
mutually exclusive i.e. they have no common table columns as part of their repsective
cursors. (If they're not and A is a cursor that's opened first, B's subsequently opened
cursor will wait indefinitely until A commits. Not the case you've articulated below.)
If they are exclusive, the question that begs to be asked is why does cursor A have to be
opened first (for update) relative to B. Why not call B first and then A?

Separate from this, a code sample is the best way to truly articulate your difficulties.

PB

mike wrote:

> To all Oracle Guru's:
>
> ...cut...
>
> 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.

> ...cut...

> 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.
>
> ...cut...
Received on Tue Jan 12 1999 - 19:09:01 CST