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: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Mon, 9 Feb 2004 11:56:52 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B45236@bosmail00.bos.il.pqe>


Boris,

As to your question regarding Row wait object # =3D -1, the Reference = Manual tells us this about V$SESSION:
ROW_WAIT_OBJ# NUMBER=20
Object ID for the table containing the ROWID specified in ROW_WAIT_ROW#=20

ROW_WAIT_FILE# NUMBER=20
Identifier for the datafile containing the ROWID specified in = ROW_WAIT_ROW#. This column is valid only if the session is currently = waiting for another transaction to commit and the value of ROW_WAIT_OBJ# = is not -1=20

ROW_WAIT_BLOCK# NUMBER=20
Identifier for the block containing the ROWID specified in = ROW_WAIT_ROW#. This column is valid only if the session is currently = waiting for another transaction to commit and the value of ROW_WAIT_OBJ# = is not -1=20

ROW_WAIT_ROW# NUMBER=20
The current ROWID being locked. This column is valid only if the session = is currently waiting for another transaction to commit and the value of = ROW_WAIT_OBJ# is not -1=20

So, FILE#/BLOCK#/ROW# are not valid, cause OBJECT#=3D-1.

Now, as the the locks themselves. How did you determine that session = 165 is not holding a lock? It sure
looks like it is, to me.

What does the following query yield?

select sid from v$session vs, v$transaction vt where vs.addr =3D vt.sess_addr

and vt.xidusn =3D 4
and vt.xidslot =3D 17
and vt.xidsqn =3D 45045;

I'm guessing that will return 165, indicating that it's the holder of = the lock, and that 226 is blocking on it.

Hope that helps,

-Mark

-----Original Message-----

From: Boris Dali [mailto:boris_dali_at_yahoo.ca] Sent: Monday, February 09, 2004 10:57 AM To: oracle-l_at_freelists.org
Subject: row level (transactional) locking problem

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# =3D 38466;

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


 165 INACTIVE 20094 3 26 =20
-1

 226 ACTIVE 1716 3 26 =20 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:=20

                           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=3D'INACTIVE' and last_call_et=3D18575)

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=3Dtrans data ....

 Itl           Xid                  Uba         Flag=20
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=3D35
....
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=3D226 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.

______________________________________________________________________=20
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 - 10:56:52 CST

Original text of this message

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