Re: Table Lock on Select for Update?

Pauli Salmu <p.salmu_at_mn.medstroms.se> wrote:
>
> thomson_at_phoenix.fs.com (Doug Thomson) wrote:
> >
> > V7 Server Concepts Manual, page 10-4:
> >
> > "Oracle never escalates locks".
> >
> ...except when there is a column with foreign key but without
> an index.

Ah, but this is not lock escalation. Lock escalation is when you start out at some low level of granularity (say the row level) and at some point in time "escalate" your row locks to page locks or table locks. As documented in the Application Developers Guide under the heading "Concurrency Control, Indexes, Foreign Keys", the expected initial behavior is to place a share lock on the referenced table and an exclusive lock on the child (preventing others from updating but not reading the child table). This is not escalation. It would be escalation if we took some row level X locks on the child and when we hit some threshold promoted that to a table lock.

To enforce foreign key constraints via declarative integrity you must decide whether you want to use an index, to allow us to lock the index row, or not.

To find out more about this behavior, I would suggest anyone interested take a look at chapter six in the Application Developers Guide. It goes into good detail on the reasoning and implementation behind this.

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