Re: Oracle Locks

Rajesh, normally Oracle will obtain and release the row and table locks necessary to support consistent data access via SQL DML statements automatically. The manuals talk about locks to provide you as a DBA and/or developer the knowledge necessary to understand the performance impact and interrelationship of SQL statements across user sessions of your application design.

When you issue an update statement a row level lock on the target row(s) is obtained on your behalf and held until you issue a commit or rollback. If a second
user attempts to update the same row they will wait on your lock is released. This example brings up the important issue of frequency of commit. I have seen where reducing the number of commits greatly reduced the clock time of a job. But less commits can create lock waits where other people want to access the same rows for update.

Select for update must obtain a row level lock for every row that meets the where clause restrictions before it can give the first row to your application. Multiple users trying to issue the same SQL are going to encounter lock wait situations.

When a foreign key is defined on table B that references table A and no index on table B exists for the columns in the FK then a delete against table A causes Oracle to obtain a table level share lock against table B until the update against A is committed.

These are the kind of things you want to pick up from your reading of the manual. Time and experience will increase your understanding of the fine points. The explicit lock table commands are mainly for special situations and types of application designs that are not that common in today's programming. If you have a situation where you are considering using table locks then you may want to investigate the dbms_lock package.

Related topics for your discussion/research : v$lock, sys.dba_dml_locks, sys.dba_ddl_locks

Rajesh Bhave <rajeshb_at_india.hp.com> wrote in article <3653EA4B.37017997_at_india.hp.com>...
> Can someone get time to start discussion as to which lock does what and
> is useful when ? I am really confused going through the manuals. Thanks
> for your time.
> Thanks in advance
> Rajesh
>
>
Received on Thu Nov 19 1998 - 10:22:34 CST