Re: mysterious share table lock

Try indexing the foreign key on bar. The behavior you see should disappear. Oracle will lock the dependent table if the foreign key has no index.

Hth,

--
Sybrand Bakker, Oracle DBA
Benjamin Toronto <btoronto_at_uswest.net> wrote in message news:387C2FBE.9253C5F_at_uswest.net...
> some help on an apparent table locking anomaly; first, try the
> following...
>
> create table foo (col1 number constraint foo_pk primary key);
> create table bar (col1 number constraint bar_fk references foo);
>
> now, start two separate sessions (e.g. SQLPlus). In one session (let's
> call it "session 1", since it's late) we'll operate on table foo, in the
> other (surprise, "session 2"), on table bar. Follow this sequence of
> events, typing the statements in order:
>
> 1. "session 2": insert into bar values (null);
> 2. "session 1": insert into foo values (1);
> 3. "session 1": delete from foo where col1=1;
> (actually, it doesn't matter whether you execute statement 1 or
> statement 2 first, but they must both precede statement 3 in time.)
>
> Is "session 1" blocking for you, too? WHY?
>
> The first reason that came to mind is that the referential integrity
> constraint from bar to foo (see the table definitions above) is somehow
> causing table foo to require a lock on table bar in order to complete
> the delete operation. Sure enough, when you
>
> alter table bar disable constraint bar_fk;
>
> and then retry the above three statements, no blocking occurs, and the
> delete from foo completes successfully.
>
> So, I decided to examine the dba lock views after recreating the above
> blocking situation. dba_dml_locks produces the following for the two
> sessions:
>
> session_id: 12 ("session 2")
> name: bar
> mode_held: Row-X (SX)
> mode_requested: None
> blocking_others: Blocking
>
> session_id:13 ("session 1")
> name: bar
> mode_held: None
> mode_requested: Share
> blocking_others: Not Blocking
>
> which perfectly explains the blocking behavior we're seeing, since a row
> exclusive table lock will not allow a concurrent share table lock
> request. But wait a minute. This is a delete statement, which is
> supposed to acquire a row exclusive table lock (as do inserts and
> updates) on the table from which it is deleting, not a share table
> lock. In fact, the only way a share table lock can be acquired is
> through a
>
> lock table <table> in share mode;
>
> statement (see Oracle 8i Concepts, chapter 27, for example), which is
> definitely not one of the three statements listed above.
>
> So, WHERE did the request for a share table lock come from? I wouldn't
> think "session 1" should be blocking at all, since Oracle likes to brag
> about their "row-level locking".
>
> Any insight would be greatly appreciated. By the way, this is all on
> Enterprise 8.0.5.1.0 on Linux, but I believe the same behavior occurs on
> Personal 8.0.4 on Windows NT and Personal 8.0.3 (the "oops" release that
> includes the Objects and Partitioning options) on Windows 95.
>
> Ben Toronto
> btoronto_at_bigfoot.com
>
Received on Wed Jan 12 2000 - 05:02:27 CST