Re: Table-locks

bma_at_NOSPAM.geocities.com (The BMA man) wrote:
>Hello group.
>
>I have a little problem - hope someone can help :-))
>
>My system is Oracle 7.3 running on Win NT 4.0
>
>As far as I know, normally Oracle doen't lock on table-level,
 but
>locks on a record-level.
>And - as far as I know - a select, even into a cursor, does NOT
 lock
>anything, only if it's a select for update.
>
>Am I on level so far?

Actually every DML statement results in Oracle obtaining an object level lock on the target. This object level lock normally only prevents another session from issuing DDL statements against the object. Another session can normally issue without conflict inserts, updates, and deletes against the object as long as it does not target the same rows held by the first session.

>
>My problem now is I have a program (c++/pro*c/sql-net) that
 does a lot
>of creating data from external sources.
>Recently someone made som PL/SQL which among other things opens
 a
>cursor (NOT select for update, only select) on a table, my
 program
>inserts data into.
>
>Now it seems that when the PL/SQL runs it locks the entire
 table????
>Anyway my program fails to do a simple insert into if the PL/SQL
>program is running.
>
>Can someone tell me if there is different rules for locking
>tables/record when it is PL/SQL than otherwise? Or can you
 tell me
>what the problem could be beside som locks?

The locking rules do not change for sql issued via sqlplus, pl/sql, and/or via pro*c.

>
>Finally I will like to excuse my bad english - I'm from
 Denmark :-)
>
>Regards - and hope to hear from you in this group
>
>Brian Madsen
>Senior software construktor.
>
>(IF you reply by e-mail please do remove "NOSPAM." from my
 address.)
>

You should run a lock tracing script while the pl/sql is running and your program is waiting to perform the insert to try to see what is happening. Also run an sql trace on your program during this process.

Verify that the pl/sql program does not issue a lock table command to override Oracle's default locking. This would create the problem you see and overriding the default locking is normally both unnecessary and unwise.

I hope this helps.

• Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice. --

  ---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Aug 09 2000 - 00:00:00 CDT