mysterious share table lock

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 - 01:39:42 CST