Problem with making one transaction in SQR

[bdpecci@AEP.COM]

It sounds as though you don't want another transaction to update the records
between your select and then update.  One potential solution is to perform the
update first and then the select.  The update will cause the records to be
locked and no other transaction will be able to update the same records until
you have committed.  I have used this strategy successfully but it has very
infrequently caused deadlocks.

However, we are currently moving to a select and then update strategy to enable
multiple programs to select the data concurrently.  We select the rowid and
update using the value.  We commit at a frequency passed to the program.  This
strategy allows us to reduce the potential for deadlocks.  The only reason we
can switch to this strategy is because the fields we are updating in each
extract are not relevant to the other extracts.  The updated fields are process
instance numbers and distribution status flags unique to each extract.

As a side note, our jobs are not restartable.  If they fail, we delete the
output file (since there's no guarantee the operating system writes are
synchronized with the database transaction), and rerun the job from the
beginning.