Re: row level (transactional) locking problem
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 9 Feb 2004 13:23:04 -0500 (EST)
Jonathan,
Thanks for your reply (I didn't know how to map XID
from a block dump to v$lock.id1/id2 info)
As to distributed transactions, IOTs, bitmap indexes -
NO to all.
I will definitely check (when I get there again -
secure site w/ no remote access) v$session.taddr for
165 (may be the way I join v$session.taddr =
v$transaction.addr is incorrect? Mark suggested
v$session.addr = v$transaction.sess_addr - is this how
it should work?)
As far as waits is concerned (v$session_wait) - yes
165 waits on 'SQL*Net message from client'.
So how 165 can possibly be blocking 226?
Thanks,
Boris Dali.
--- Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote: >
> 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
>
>
> ----- Original Message -----
> From: "Boris Dali" <boris_dali@xxxxxxxx>
> To: <oracle-l@xxxxxxxxxxxxx>
> Sent: Monday, February 09, 2004 3:56 PM
> 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# = 38466;
>
> Last Row Row
> Row
> Call Wait Wait
> Wait
> Sid STATUS ET File# Row#
> Obj#
> ---- -------- -------- ---------- ----------
> ----------
> 165 INACTIVE 20094 3 26
> -1
> 226 ACTIVE 1716 3 26
> 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:
>
> 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='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
> 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=35
> ....
> 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=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.
>
>
______________________________________________________________________
>
> Post your free ad now! http://personals.yahoo.ca
>
=== 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
