If they are using DBMS_ALERT then since it uses
dbms_lock, it could be possible that the locking
problem is one of the application's making (not the
database). I can't remember the specifics, but things
like a long gap between signalling the alert and the
subsequent commit rings a bell as a cause of "locking"
problems...
hth
connor
- K Gopalakrishnan <kaygopal_at_yahoo.com> wrote: >
What is the PCTFREE setting for the tables? Hope it
> is not 0.
>
>
> Here is the quote form one of my article which
> explains this behavior
>
> --------------BEGIN QUOTE----------------
>
> Each datablock will have an Interested Transaction
> List (ITL) that holds the
> transaction id of that block during the life cycle
> of the transaction
> modifying that datablock. A transaction, which
> modifies a record in the
> datablock, must get an ITL slot in that datablock.
> The number of ITL slots
> in a datablock is defined by the INITRANS (which
> defaults 1 for data blocks
> and 2 for index blocks) and MAXTRANS.
>
> While formatting a new block Oracle creates the
> transaction slots specified
> by INITRANS parameter. MAXTRANS specifies maximum
> number of ITLs created for
> a datablock and it defaults to 255. In practice you
> don’t need more MAXTRANS
> unless your AVG_ROW_LENGTH is very small and the
> segment is frequently
> updated.
>
> The creation of additional Interested Transaction
> Lists (ITL) slots is
> subject to free space in the datablock because each
> ITL takes approximately
> 24 bytes of free space in the variable header of
> that datablock. Initial
> space reserved by INITRANS cannot be reused for data
> insertion. But if a
> datablock is fully packed due to less PCTFREE or
> PCTFREE=0 and when two
> transactions are accessing the same block, one has
> to wait till the
> transaction commits (or rollbacks). Here row level
> locks are escalated in to
> block level locks.
>
>
> ---------------END
> QUOTE-----------------------------------
>
>
> And I don't see any reason for row locks becoming
> table locks unless you
> have an un indexed foreign key.
>
>
> Best Regards,
> K Gopalakrishnan
> Bangalore, INDIA
>
>
>
> -----Original Message-----
> Mascranghe
> Sent: Tuesday, February 12, 2002 4:33 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi all
>
> We are running on 8.0.5.2.1 database. Once we had a
> database creash and was
> restored. After that the users have been
> experiencing locks. What happens is
> when one user locks some rows, other users are also
> getting stuck. But they
> are not locking the same rows. We are looking at the
> code to see whether any
> unusual things are there. One of the programs in the
> system uses DBMS_ALERT.
>
>
> How can we find more information - what rows are
> being locked, and any other
> relevant info about locks?
>
> Is there any ways in which a row locks turns out to
> be a table lock ?
>
> Thanks
> Alroy
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Alroy Mascranghe
> INET: alroy_at_informatics.lk
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
>
>
> Do You Yahoo!?
> Get your free @yahoo.com address at
> http://mail.yahoo.com
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Feb 12 2002 - 11:05:03 CST
