Re: Table Lock on Select for Update?

gmadhavi_at_pms991.pms.ford.com (Madhavi Lokam) wrote:
>
> 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

NO IT DOESN"T IN THE MANNER YOU DESCRIBE. LOOK FOR MY PREVIOUS POST ON THIS OR JUST TRY IT IN SQL*PLUS. Go into two separate sql*plus sessions. Issue select for udpate against scott.emp for 1 row in session 1, issue select for update against scott.emp for a different row in session 2. Now go back to session 1 and update the row (don't commit). Now go to session 2 and update the row (don't commit). Hey, guess what, neither session blocks. Try it sometime. This is just so easy to see.

It is true that there is a share lock on the table (whether you put it there or not). It is simply to prevent DDL operations on a table (eg: ALTER STATEMENTS). IT DOES NOT DECREASE/IMPEDE THE ABILITY TO INSERT/UPDATE/DELETE against the table.

You have some fundemental misunderstanding about locks.

Look at your statement:

> 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.

You are trying to say that if you don't lock the table, Oracle will lock the table and if you don't lock the table, you can't get concurrency. You are just wrong. Please read the Server Concepts Manual. Or easier still, just try some things in SQL*Plus.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government. Received on Sun May 14 1995 - 00:00:00 CEST