Re: Table Lock on Select for Update?

smith117_at_delphi.com wrote:
>
> I've heard of one other behavior that may be causing this confusion over table
> level locking.
>
> It is my understanding that if the row you are selecting is actually a join
> across multiple tables, and there is no index on the foreign key, there is some
> table level locking.
>
> Maybe Tom can clarify this.
>
> Other than this, you get unlimited number of row level locks unless you
> specifically request to lock the table.
>
> Dan

Yes, you should index foreign keys (those defined using Oracle Constraints, in a create table command for example). Please see chapter 6 in the "Oracle7 Server Application Developers Guide" under the heading "Concurrency Control, Indexes, and Foreign Keys" for a good explaination of what happens with foreign keys in read/write transactions. Note, it has nothing to do with updating multiple tables or joining data, it happens on simple, single table updates as well.

Please take a look at chapter 10 of the "server concepts manual" for a general discussion on locking in Oracle. It talks abouts updates and table level locks (ones that disallow DDL operations on a table that has an active transaction on it, NOT DML operations).

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