| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: row level (transactional) locking problem
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#
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
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
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.
______________________________________________________________________=20Post your free ad now! http://personals.yahoo.ca
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |