Home » RDBMS Server » Performance Tuning » AWR top events:: enq: TX - row lock contention (Oracle 11.2.0.3 on HP-UX (64-bit))
AWR top events:: enq: TX - row lock contention [message #608509] Thu, 20 February 2014 06:43 Go to next message
hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA
Member
Hi,

I am working on OLTP databases
Oracle 11.2.0.3 on HP-UX (64-bit)

Most of the time it shows following as AWR TOP events -

enq: TX - row lock contention -- this shows high number of Avg Wait time(ms)
db file sequential read
db file parallel read

Could anyone help me with the troubleshooting steps / any scripts?
Thanks in advance
Re: AWR top events:: enq: TX - row lock contention [message #608512 is a reply to message #608509] Thu, 20 February 2014 06:49 Go to previous messageGo to next message
John Watson
Messages: 8644
Registered: January 2010
Location: Global Village
Senior Member
You need to provide a lot more information before nyone can begin to help. To start, can you upload the entire AWR report? Make sure it is for a sensible period, for example, for one hour during which there were performance problems.
Re: AWR top events:: enq: TX - row lock contention [message #608670 is a reply to message #608512] Fri, 21 February 2014 20:44 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am going to jump the gun a little and provide some background on row lock contention. If you really do have a problem with row lock contention there are three situations I can recall seeing it. It has to do of course with applications updating data. Indeed it requires two transactions to be updating related data. There three situations I remember are:

1. one transaction has updated/deleted a row that a second transaction also wants to update or delete.  The second transaction must wait for the first to commit or rollback.

2. two transactions have attempted to insert a row with the same key and there is an unique index on that key.  Just as with #1, the second transaction will have to wait for the first to commit or rollback.

3. you have a bitmap index and you have updated a row in your table and thus as a reult have locked the corresponding bitmap index block.  By the nature of bitmap indexes this means all rows with entries on that bitmap block are also locked even though you have not updated/deleted them.  Thus a second transaction can be trying to insert/update/delete to/from that block for the same or a different row yet still have to wait for the first transaction to commit or rollback.  Yes, it seems bitmap indexes have regressed a little to the block level locking days.  But this is how they work and is the main reason why you don't use bitmap indexes on a table that is DML active.  You take their benefit knowing their limitations.

4. there is also a similar scenario involving unindexed foreign keys and the need to check child tables in certain update/delete/insert situations.  But I do not recall if this shows up as tx row lock contention or some other type of wait.


So you should have a look at how your application is updating data and see if you can't split your workloads into non-overlapping sets of data that can be updated independently. Make sure not to change your transaction semantics in the process though.

Good luck, Kevin
Previous Topic: Bloom filter joins
Next Topic: Oracle Architecture
Goto Forum:
  


Current Time: Sun Dec 05 23:57:49 CST 2021