Re: Is it possible to use nolock type of hint in query
- From: "Norman Dunbar" <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>
- Date: Tue, 15 Aug 2006 08:34:55 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
