Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Off Topic: Row Locking - Row Id

Off Topic: Row Locking - Row Id

From: Johan Locke_at_i-Commerce Services <Locke_at_i-Commerce>
Date: Sun, 11 Feb 2001 21:10:44 -0800
Message-ID: <F001.002B1669.20010211210552@fatcity.com>

Hi

Anybody have any idea where I can find the rowid of a row that is being locked within a table?

Kind Regards
JL

-----Original Message-----
Sent: Monday, February 12, 2001 5:40 AM
To: Multiple recipients of list ORACLE-L

Hi

    In my opinion, this is an ITL issue. When a process need an ITL and have to wait for it , then it pseudo randomly selects a locked row (from that block) and enqueues itself in to the waiters queue. But the row_waited information in v$session will be null. In rare cases, it is possible for the deadlock to occur if the ITL waiter holds the row that is needed by the other process.

    I would ask, what is the frequency of this deadlock ? Is this the first occurrence ? If it is the first occurrence, then I would wait for the next occurrence and then spend time and resource.

   Hope this helps!!
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and does not bind my employer. Use at your risk"  

                    elkinsl_at_flash

                    .net                 To:     Multiple recipients of list
ORACLE-L <ORACLE-L_at_fatcity.com>  
                    Sent by:             cc:

                    root_at_fatcity.        Subject:     Deadlock
Interpretation Assistance Requested            
                    com

 

 

                    02/10/01

                    07:00 PM

                    Please

                    respond to

                    ORACLE-L

 

 





Listers,

HP-UX 11.0, V7.3.4.3. Deadlock trace file snippet:

SELECT * FROM UNIT_STATISTICS WHERE UNIT_ID = :b1 AND MONTH = :b2 AND YEAR = :b3 AND RANK_CODE = :b4 FOR UPDATE OF QUANTITY <snip>
Deadlock graph:

                       ---------Blocker(s)--------  ---------Waiter(s)

---
Resource Name          process session holds waits  process session holds
waits
TX-00180008-000042d6       837     635     X            784     481
S
TX-00160010-00004412       784     481     X            837     635
X
Rows waited on:
Session 481: no row
Session 635: obj - rowid = 00000722 - 00000289.0033.0102

I've never really encountered all that many deadlocks before. The ones I
*have* seen in the past were the "classic" TX locks where user A has a row
locked that user B needs and vice versa and the mode requested was X. On
Friday, the DBA's sent me a trace file from a deadlock (with the info above
from that trace file) and asked me to investigate. The deadlocks they had
seen in the past were due to application coding issues, hence their tossing
this to the development side of the house.

After a lot of research on Metalink, the Steve Adams site
(http://www.ixora.com.au), and Usenet archive searches (www.deja.com), the
S
mode wait for session 481 (and no row) makes me think this isn't the
typical
application induced deadlock due to the way and order in which locks are
acquired.

There are 3 foreign keys on the table, and, each of them are indexed. There
is no bitmap index. PCT_FREE is 10 and PCT_USED is 40. I don't really know
all that much about how heavily DML is issued against the table. But, after
reading material on when the wait is in S mode, I wonder if this might be
an
ITL issue. From what I've read the past 2 days, there could be other
reasons
for the S mode wait, but, waits for Unique/PK enforcement, insufficient ITL
slots, and bitmap index were the most common reasons mentioned. Because the
statement reported was a SELECT FOR UPDATE, I've eliminated (correctly?)
the
check for uniqueness wait during inserts, and, with no bitmap index on the
table, that leaves the ITL slots as the main candidate.

What I need to do is determine if this is indeed an application coding
issue, or, if I need to kick this back to the DBA's and let them research
it. And I don't mean that in a finger pointing way. The DBA's and
developers
there work well together. From what I've read and learned so far, this
deadlock doesn't seem to be an application coding issue. I am thinking
about
saying that and asking them (if they haven't already) to open a TAR and
provide the trace file to Oracle Support.

If anyone has any comments or suggestions, I would appreciate hearing them
(because if this could still be due to an application coding issue, more
research needs to be done on the development and/or my side of the house).

Regards,

Larry G. Elkins
elkinsl_at_flash.net

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: elkinsl_at_flash.net

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




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Riyaj_Shamsudeen_at_i2.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Johan Locke_at_i-Commerce Services
  INET: Johan.Locke_at_za.didata.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).
Received on Sun Feb 11 2001 - 23:10:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US