| 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
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#
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
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
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.
-- 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
 |
 |