| High enq: TX - row lock contention [message #320533] |
Thu, 15 May 2008 07:14  |
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  |
 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
