Re: Table Lock on Select for Update?

In article <3oqh6o$jlk_at_inet-nntp-gw-1.us.oracle.com>, "Thomas J. Kyte" <tkyte_at_us.oracle.com> writes:
|> BJSIEBEN_at_bcsc02.gov.bc.ca wrote:
|> >
|> > If anyone can confirm what I've heard about Select for Update. What I
|> > heard was: when the select for update is issued, Oracle locks the row(s)
|> > in the select, but when the actual update occuer (ie. update....) Oracle
|> > will lock the entire table while the actual update is happening.
|> >
|> > Has anyone else heard this? I find it hard to believe the entire table
|> > is locked for the brief time the actual update occurs. We are using
|> > Oracle7 on AIX.
|> >
|> > ......Barry Sieben
|>
|>
|> Nope, this is absolutely wrong. For example, Oracle*Forms uses select
|> for update all the time-- for each and every row actually updated--
|> prior to updating the row. More than one person at a time can
|> *definitely* update the table (or insert or delete) as long as they
|> do not touch the same exact row that has been selected for update or
|> actually updated.
|>
|> We do put a share lock on the table so no "alter" type of DDL can
|> be issued. This prevents someone from changing the physical structure
|> of the table (eg: you can't add a column to a table while an update
|> is taking place, you can't drop the table, you can't truncate it, etc).
|> Perhaps this is the 'table lock' you were hearing about. It only prevents
|> structural changes to the table, it does not limit concurrency with respect
|> to inserts/updates/deletes.
|>
|> Hope this helps
|>
|> Thomas Kyte
|> tkyte_at_us.oracle.com
|> Oracle Government
|>

Yes, If you put the share lock on the table then it allows concurrent updates

But you don't have any lock of this kind then oracle locKs the entire table when an update is issued.

Madhavi Lokam
consultant
Ford Motor Company Received on Thu May 11 1995 - 00:00:00 CEST