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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
