| 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
This transaction has XID 4.17.45045 (when translated to decimal) which is the transaction slot held by session 165, not 226
ID1 = 4 * 65536 + 17
ID2 = 45045
0x01
xid: 0x0004.011.0000aff5
uba: 0x0080531f.0c49.2a ---- 1 fsc 0x0000.00000000
165 really is blocking 266.
If you can't see 165 in v$transaction, then
it looks as if something has gone wrong. Is it
possible that 165 is an incoming distributed
transaction ? I know an incoming read-only
transaction takes an undo slot but hides its
v$transaction entry - I haven't checked to
see if an incoming update transaction does
exactly the same.
Check column TADDR from v$session for
sid 165 to see if it null - if it is, then something
has gone wrong, otherwise the value will
map to ktcxbxba in x$ktcxb which is the
x$ underlying v$transaction, and you may
be able to pick up further information from there.
TX mode 4 can be produced by several other types of activity, and multiple concurrent locks would be a little rare if it were ITL.
For example, do you have any bitmap indexes on that table, or is that table an Index Organized Table. In the former case, updates to indexed columns can cause other sessions to wait on a bitmap section in mode 4; in the latter, a 'row' lock manifests as mode 4 rather than the mode 6 you would expect for a heap table.
I believe the fact that the session is INACTIVE simply means that it is between database calls at the moment - you could check what it is waiting on - I'd guess
SQL*Next message from client
or maybe
SQL*Net message from dblink
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:06:35 CST
 |
 |