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

Home -> Community -> Mailing Lists -> Oracle-L -> row level (transactional) locking problem

row level (transactional) locking problem

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: Mon, 9 Feb 2004 10:56:48 -0500 (EST)
Message-ID: <20040209155648.17309.qmail@web41407.mail.yahoo.com>


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
-----------------------------------------------------------------
Received on Mon Feb 09 2004 - 09:56:48 CST

Original text of this message

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