Re: strang table locking

This looks like a problem with initrans - Your two ID values suggest that the
two rows may be in the same block -
if the block is packed, and INITRANS
is set to 1, then the first transaction
gets the ITL entry and the second transaction puts in a TX lock request in mode 4 for
the entry, and has to wait for the first transaction to commit before it can even lock the row.

Changing initrans will solve the problem for future data, but not fix the problem retrospectively for old data. NB You may also need to increase initrans on the primary key index - but this is less likely as the default value on indexes is 2.

--
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



susana73_at_hotmail.com wrote in message <950i0p$7tc$1_at_nnrp1.deja.com>...


>I have an app that open 2 database connections concurrently.  It hangs

>couple times a week due to table lock.  The following is a report shows

>what happen:

>

>USERNAME    SID    SERIAL#  TY  HELD        REQUEST

>------------------------------------------------------------

>SQL

>------------------------------------------------------------

>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;

>

>The two sessions(SID 13,29) are updating different rows so I have no

>idea how this can generate locks.  I do not set up any locking

>feature.  I tried to disable all foreign keys but it didn't help.  I

>also tried rebuild the corresponding tables and indexes(I suspect due

>to corrupted indexes) but still didn't help.

>

>Please help!

>

>Susan

>

>

>Sent via Deja.com

>http://www.deja.com/