From Locke@i-Commerce Mon, 12 Feb 2001 23:12:42 -0800 From: "Johan Locke@i-Commerce Services" Date: Mon, 12 Feb 2001 23:12:42 -0800 Subject: RE: Off Topic: Row Locking - Row Id Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi Not realy what I was looking for. Thanks anyways Regards JL -----Original Message----- Sent: Monday, February 12, 2001 10:41 AM To: Multiple recipients of list ORACLE-L 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 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@flash > > .net To: Multiple recipients of list > ORACLE-L > Sent by: cc: > > root@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 > > 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@flash.net > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: elkinsl@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@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@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@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@i-Commerce Services > INET: Johan.Locke@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@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@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@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@i-Commerce Services INET: Johan.Locke@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@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).