Re: Is it possible to use nolock type of hint in query

Morning Jared,

>> I don't see how that invalidates the use of SELECT FOR UPDATE.
Technically, it doesn't. It's just that developers (some of) don't have a flipping clue. SELECT FOR UPDATE is a simple and lazy way to prevent the developer having to (a) think; and (b) code more code IMHO.

>> The key is when to use it.

Exactly!

>> Query data at the beginning of the transaction.
Nope. They SELECT FOR UPDATE at the beginning of the query !

<SNIP>

>> When the user is ready to commit the transaction:
>> * Now do the SELECT FOR UPDATE
>> * If the rows can't be locked, the transaction obviously is void
>> Poor user must start over.

Not in my code! The poor user is advised that someone else has changed the data since it was read and would they like to (a) abort completely and start again; or (b) see what changes have been made; or (rarely) (c) force the changes through anyway.

If they choose the (b) option the have the option then to apply some or all changes over the new data or abort completely.

>> This leaves the row(s) locked just long enough for the app to
determine
>> if it is safe to proceed.

A Utopian solution. Once more I refer to Spatial. If I wish to update a column of Spatial data, and it is indexed, there is a SELECT FOR UPDATE on MDSYS.SDO_ALL_INDEX_METADATA which means that from now on, until I commit/rollback, no other user can perform any insert or updates on that column in that table. Enqueue waits result. Other users get somewhat teed off with me.

It's down to the application of course, to keep these transactions short and sweet to avoid holding locks for ages, but this in turn is down to the developers and so we start the nasty vicious circle all over again :o(

>> I believe that Oracle Forms does something like this.
I've never used forms, so I have no experience of it yet.

>> The problem seems to be (as with many things involving databases)
>> the misuse of the SELECT FOR UPDATE statement.
I fully agree. But, on the other hand, just because you *can* do something, that doesn't mean that you *should* :o)

Cheers,
Norman.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Norman Dunbar.
Contract Oracle DBA.
Rivers House, Leeds.

Internal : 7 28 2051
External : 0113 231 2051


Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.

We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation.  Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506.  Find out more about the Environment Agency at www.environment-agency.gov.uk
--
http://www.freelists.org/webpage/oracle-l