Re: row level (transactional) locking problem
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 9 Feb 2004 15:09:23 -0500 (EST)
One follow-up question, Jonathan.
If not IOTs, nor bitmap indexes - what could be the
other reasons for mode=4 lock requests from the
blocked sessions trying to do an update (I know for
inserts it can be things like pk constraint
enforcement, but I can't think of any reason other
than ITL shortage if the sql waiting/blocked is an
update)?
Here's an example from utllockt output (165 blocks say
441 requesting lock mode=4):
WAIT_SES LTYPE REQUEST HELD LOCK_ID1 LOCK_ID2
-------- ----- ------- ----- -------- --------
....
165 None
205 Trans Share Exclu 262161 45045
434 Trans Share Exclu 262161 45045
66 Trans Exclusi Exclu 1114185 46270
441 Trans Share Exclu 262161 45045
226 Trans Exclusi Exclu 262161 45045
....
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:
