Often you can just code a select cursor then use an update statement within the
cursor loop. The update by PK should have a where clause which checks that the
row still meets the select criteria. If the row has been changes so that it
now longer meets the select criteria the update will update zero rows which is
not an error and processing just continues.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of walid kaakati
Sent: Tuesday, April 12, 2016 7:32 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Recommendations For Select for Update Statments
Hallo List,
I working on an application that generates SQL Statements with SELECT FOR
UPDATE clause, these Statements cause a lock on the select rows and hence
blocking other sessions,sometimes the lock stay for half an hour according to
the selected data.
From analzying the Locks ,i found the locking due to Logical Errors in the
development of the applciation .
My question from PL-SQL viewpoint is their an alternative for this mechanisem
that requires a lock on the Selected rows for Update ? any recommedation to
minimize this Locking ?
my (Database is 10g ).
Best Wishes,