Re: Table Lock on Select for Update?

gmadhavi_at_pms991.pms.ford.com (Madhavi Lokam) wrote:
>
> In article <17399783FS86.BJSIEBEN_at_bcsc02.gov.bc.ca>, BJSIEBEN_at_bcsc02.gov.bc.ca writes:
> |> 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
>
> Yes , This is true if you are not using LOCK TABLE before update.
  ^^^^
NO it is not true.

>
> Without LOCK TABLE , an insert, update or delete commands lock
>the whole table till the operation is complete.
>

The above statement is sort of contradictory-- Without the LOCK TABLE, the table is LOCKED???? Nah, it should be WITH the LOCK TABLE, the whole table is LOCKED, not withOUT.

BEAR IN MIND, the table is locked in SHARE MODE TO PREVENT DDL OPERATIONS ON THE TABLE. IT DOES NOT PREVENT TWO OR EVEN HUNDREDS OF PEOPLE FROM UPDATING IN THAT TABLE!!!! IT ONLY PREVENTS THE TABLE STRUCTURE/SET OF CONSTRAINTS FROM BEING CHANGED WHILE PEOPLE ARE ACCESSING THE TABLE. Also, this share lock is placed when the select for update is done, it is not 'esculated' when the update takes place.

Try the following three sesssions in SQL*Plus using multiple windows for example:

SESSION 1                     SESSION 2          SESSION 3
 

SQL> select *
  2 from scott.emp
  3 where empno = 7934
  4 for UPDATE;

1 row retrieved

                         SQL> select *
                           2  from scott.emp
                           3  where empno = 7902
                           4  for UPDATE;

                         1 row retrieved

                                             SQL>  alter table scott.emp
                                               2  add constraint
                                               3  empno_check
                                               4  check (empno<9999);
                                             ERROR at line 4:
                                             ORA-00054: resource busy and acquire with NOWAIT specified

SQL> update scott.emp
  2 set job=lower(job)
  3 where empno=7934;  

1 row updated.

                       SQL> update scott.emp
                         2  set job=lower(job)
                         3  where empno=7902;
 
                       1 row updated.

SQL> commit;  

                       SQL> commit;

                                             SQL> -- NOW THE ALTER WILL WORD

As you can see, two people select for update on the same table, two people update the same table, two people commit their transactions. Scott.emp was not locked to prevent updates (or inserts or deletes). It was, however, locked in a mode to prevent someone from adding a constraint (or a column or an etc....) while the updates were taking place.

> If you want to give restricted access to other users when the
>update/delete/insert is in progress, use the LOCK TABLE COMMAND
>with appropriate
> Lock Mode before that other DML command is issued.

NEVER USE THE LOCK TABLE COMMAND to help out simple inserts/updates deletes. In general, NEVER USER THE LOCK TABLE COMMAND. You don't need to unless you really want to serialize access to that table.

People never have restricted access to perform DML on a table (two people can't update the same row but that is all). People have no DDL access (alter) to the table while an open transaction on the table exists.

>
> Madhavi Lokam
> Consultant
> FORD MOTOR COMPANY
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu May 11 1995 - 00:00:00 CEST