RE: Recommendations For Select for Update Statments

From: Powell, Mark <mark.powell2_at_hpe.com>
Date: Tue, 12 Apr 2016 19:10:55 +0000
Message-ID: <1E24812FBE5611419EFAFC488D7CCDD132AEEFA3_at_G4W3290.americas.hpqcorp.net>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of walid kaakati Sent: Tuesday, April 12, 2016 7:32 AM
To: oracle-l_at_freelists.org
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,

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 12 2016 - 21:10:55 CEST