Home » RDBMS Server » Performance Tuning » High enq: TX - row lock contention (Oracle 10.2 HPUX)
High enq: TX - row lock contention [message #320533] Thu, 15 May 2008 07:14 Go to next message
datamile
Messages: 2
Registered: May 2008
Junior Member
Hi,

I've been looking at the performance of a vendor product, and have seen high Enq TX values.

Setup.

A small record is insert using a sequence number, and then goes through of series of updates.

Several simultanous processes are inserting records, and updating and so action is very likely to be on the same data block, with many records in each block.

Before any update, the app issues

select * from table where key_id=value for update ;

and this is the statement with the High Eng TX.

Key_id has a unique key ( not sure if its defined as a constraint ), will need to check if there are any bitmap indexes on other columns but I don't think so.

Is the contention caused at

1) the block level,

2) The ITL within the block,

3) the indexing locking as the user has the option from the select to update the unique index ? If so should the select be changed to select * from table where key_id=value for update of non-key-fields

Thanks

Ian

Re: High enq: TX - row lock contention [message #320575 is a reply to message #320533] Thu, 15 May 2008 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, 
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

The SQL above should identify both the sessions involved & the objects involved with the WAITS.
Once you know who & what is involved, then you need to figure out if there is any way to reduce or eliminate the contention.
Re: High enq: TX - row lock contention [message #320656 is a reply to message #320533] Thu, 15 May 2008 15:17 Go to previous message
datamile
Messages: 2
Registered: May 2008
Junior Member
Thanks I'll try it.

I think the main issue is that the number of waits/locks, which are very rapid as the updates are rapid, rather then the time the lock is held.
Previous Topic: Number of table partitions
Next Topic: Performance Issue on a specific Table only
Goto Forum:
  


Current Time: Tue Dec 03 09:41:04 CST 2024