Mark,
Thanks for your reply.
So given row_wait_object#=-1 the rest of the
row_wait_* columns are irrelevant and the session is
NOT waiting for a row level lock? But it probably did
in the past and when the lock cleared Oracle resets
object# only?
> How did you determine that session = 165 is not
> holding a lock? It sure looks like it is, to me.
I didn't say 165 is not holding a lock (at least I
didn't intend to say that as it sure thing does - see
v$lock output). I said it doesn't seem to have any
transactions pending. The query I used is below:
SELECT nice bunch of stuff
FROM v$rollname a
,v$rollstat b
,v$session c
,v$transaction e
,v$process p
WHERE a.usn = b.usn
AND b.usn = e.xidusn
AND c.taddr = e.addr
AND c.paddr=p.addr
.... which yelds nothing for 165, but it does for 226.
If I approach if from the locking angle that I do see
output for both 165 and 226:
SELECT nice bunch of stuff
FROM v$rollname r
,v$rollstat rs
,v$lock l
,v$session s
WHERE l.Sid = s.Sid(+)
AND trunc( l.Id1/65536) = R.Usn
AND l.Type = 'TX'
AND r.usn = rs.usn
Checking last sql gives "update" that 226 is waiting
on and "select" that 165 ran last (5 hours ago). So
how 165 can block 226?
Thanks,
Boris Dali.
- "Bobak, Mark" <Mark.Bobak_at_il.proquest.com> wrote:
> 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 = vt.sess_addr
> and vt.xidusn = 4
> and vt.xidslot = 17
> and vt.xidsqn = 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.
=== message truncated ===
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 - 11:57:15 CST
