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

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

Re: Off Topic: Row Locking - Row Id

From: Saurabh Sharma <saurabhs_at_fcsltd.com>
Date: Mon, 12 Feb 2001 01:30:45 -0800
Message-ID: <F001.002B1743.20010212004042@fatcity.com>

hi,
you can simply include the hidden(or embedded) column in your querry as u write other columns.
this is the column which is locked in every table. and can give u the desired value for each row..
try
select col1, col2,....., rowid from tablename;

saurabh
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, February 12, 2001 10:35 AM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Saurabh Sharma
  INET: saurabhs_at_fcsltd.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 Mon Feb 12 2001 - 03:30:45 CST

Original text of this message

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