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: row level (transactional) locking problem

Re: row level (transactional) locking problem

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 9 Feb 2004 17:09:08 -0000
Message-ID: <019101c3ef2f$6f55a110$6702a8c0@Primary>

Sorry, forgot the row_wait_obj# = -1.

It depends on your Oracle version as to
what goes on in these columns. I think that somewhere in v9, any type of block wait gets recorded in the columns, but the obj# is set to -1 when the wait completes.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person   who can answer the questions, but the   person who can question the answers -- T. Schick Jr

Next public appearances:
 March 2004 Hotsos Symposium - The Burden of Proof  March 2004 Charlotte NC OUG - CBO Tutorial  April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Can somebody explain to me under what circumstances row_wait_obj is -1, but the rest of row_wait_* columns in v$session still point to the real file/block/row?

SQL> select sid, status, last_call_et, row_wait_file#, row_wait_row#, row_wait_obj#
  2 from v$session where row_wait_block# = 38466;

                Last          Row        Row       
Row
                Call         Wait       Wait      
Wait
 Sid STATUS      ET         File#       Row#      
Obj#
---- -------- -------- ---------- ----------

 165 INACTIVE 20094 3 26 -1
 226 ACTIVE 1716 3 26 5004

According to the locking information in DD (from utllockt, v$lock, and catblock stuff
[dba_waiters/blockers etc]) session 165 blocks 226
(and a few others). For instance:

                           Mode    Mode

WAIT_SESS HOLD_SESS TYPE Held Request Lock1 --------- --------- ---- ------ ------- --------------

     226 165 Tran Exclus Exclusi 262161

... and the full list of entries pertaining to 165/226 in v$lock:

SQL> select * from v$lock where sid in (165, 226);
(output formatted to fit the screen):

Sid TY ID1 ID2 LMODE REQUEST CTIME ------ ------ ----- ----- ------- ----------

165 TX 262161 45045     6       0      18576
165 TM   5004     0     3       0      31937
165 TM   3974     0     3       0      18576
165 TM   3831     0     3       0      18576
165 TM   3967     0     3       0      18576
165 TM   3846     0     3       0      18576
165 TM   3790     0     3       0      18576
165 TM   3834     0     3       0      18576
....
226 TM   5004     0     3       0        101
226 TX 262161 45045     0       6        101


... but 165 doesn't have any pending transactions (no entries in v$transaction). In fact it's being idle for the last 5 hours (v$session.status='INACTIVE' and last_call_et=18575)

Here's a block dump of 3/38466:

buffer tsn: 2 rdba: 0x00c09642 (3/38466) scn: 0x0000.0b6f62c2 seq: 0x01 flg: 0x00 tail: 0x62c20601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data ....

 Itl           Xid                  Uba         Flag 
Lck        Scn/Fsc

0x01 xid: 0x0004.011.0000aff5 uba: 0x0080531f.0c49.2a ---- 1 fsc 0x0000.00000000 0x02 xid: 0x0004.010.0000aff1 uba: 0x00805320.0c49.08 C--- 0 scn 0x0000.0b6f2e8b ....
nrow=35
....
tl: 229 fb: --H-FL-- lb: 0x1 cc: 46
....

So everything seems to be consistent - there's only one slot taken in this block (by sid=226 I guess), the second slot is no longer active (cleaned up during delayed block cleanout I presume) and yet session 226 is blocked by 165. Am I missing something obvious?

Just for completence - I think part of the problem in this app is the ITL shortage on table 5004 as there are quite a few 'TX' entries in v$lock requesting mode 4 locks and sitting on the update statements. Not sure if this is related to the question above.

Oracle 8.1.7.4.1 on W2K (sorry I didn't do it)

Thanks,
Boris Dali.



Post your free ad now! http://personals.yahoo.ca

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Mon Feb 09 2004 - 11:09:08 CST

Original text of this message

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