Re: strang table locking

In article <980962014.6907.0.nnrp-13.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> Comments in line:
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Alex Filonov wrote in message <959gef$k4r$1_at_nnrp1.deja.com>...
> >Kinda strange logic, guys. The trace file says that this is a table
> >exclusive lock.
> >
>
> No, table locks would be TM locks, the supplied listing showed
>
> PROD_USER 13 124 TX Exclusive None
> UPDATE object set flag = 1 WHERE plan = 'A' AND Id = 3466629
>
> PROD_USER 29 9034 TX None Share
> UPDATE object set flag = 1 WHERE plan = 'B' AND Id = 3466631;
>
> TX locks are transaction locks.

I was wrong, sorry. TX are row transaction locks.

>
> >
> > INITTRANS says how many transactions may be kept in a
> >block. If you have more transactions than that, you'll get a block
> >latch, not a table lock.
> >

I was wrong again. INITTRANS is an initial number of transaction. If block doesn't have enough transaction entries, new transaction entry is dinamically allocated ( Oracle 8 SQL Reference). No locks or latches are created in this case.

>
> No, you get a TX share lock.
>
> >
> > Try to index all foreign keys in
 the
 detail
> >table, it should help.
 

> >
>
> No, the original post also said:
>
> I tried to disable all foreign keys but it didn't help.
>
> so there are no foreign keys in the detail table. You may also
> note that the pk/fk problem can only occur when you try to
> delete a row from the PK table, or change the PK value of
> an existing row. A statement of the form:
> UPDATE object set flag = 1 WHERE plan = 'B' AND Id = 3466631;
> is unlikely to be changing a PK..
>

If you don't have an index on a foreign key, Oracle locks the whole parent table in share mode (look Oracle 8 Tuning, Part 10 Data Access Methods).
If you do have an index, Oracle uses pin mechanism and doesn't lock parent table.
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 31 2001 - 19:18:25 CET