Re: Table Lock on Select for Update?

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 Received on Wed May 10 1995 - 00:00:00 CEST