Subject RE: [IBO] Re: select ... for update (2nd post)
Author Norman Dunbar
Morning all,

just to add to the post below.

In Oracle - which I do in my real job - 'select ... for update' actually
locks the rows selected.
Normally Oracle will not lock a row (Oracle only locks rows, not pages or
tables - although you *can* explicitly lock a table) until you try to update
it. However, the 'select ... for update' pessimistically locks all the rows
at once.

This means of course, that a 'wait' will occur if someone else has the some
of the rows you want to update locked. To avoid this, you can 'select ...
for update nowait' instead which throws an exception (in the database) if
any of the rows to be updtated are locked.

In Oracle 9i, you can even 'select ... for update wait 5' to wait 5 seconds
for any locks to be released before throwing an exception.

Sorry - this is a bit OT for this list, but (slightly) interesting all the
same (maybe !).


Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
Tel: 0113 289 6265
Fax: 0113 289 3146

-----Original Message-----
From: pepmallorca [mailto:pepmallorca@...]
Sent: Tuesday, March 05, 2002 5:24 PM
Subject: [IBO] Re: select ... for update (2nd post)


select ...for update is used in some databases like ORACLE, to
indicate the columns to be updated in a CURSOR:
This email is intended only for the use of the addressees named above and
may be confidential or legally privileged. If you are not an addressee you
must not read it and must not use any information contained in it, nor copy
it, nor inform any person other than Lynx Financial Systems or the
addressees of its existence or contents. If you have received this email
and are not a named addressee, please delete it and notify the Lynx
Financial Systems IT Department on 0113 2892990.