Skip to Main Content
  • Questions

  • enq: TX - row lock contention wait event for an UPDATE statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: March 18, 2016 - 4:53 am UTC

Last updated: May 04, 2018 - 9:49 am UTC

Version: 11.0

Viewed 10K+ times! This question is

You Asked

Hi,

We are facing this wait event "enq: TX - row lock contention" when an update statement is being executed.THis update has filter columns which are part of unique index.What I observed during analysis is table has PCTFREE=10 and PCTUSED=NULL in all_tables table.Is NULL for PCTUSED in anyway not correct setting for this ? Is it probable reason for this TX wait event ?

There are no other transaction from any other session observed

Thanks
Girish

and Chris said...

Are you sure there's no other transactions?

TX contention usually happens because there's an uncommitted update to the same rows.

But it could also be an uncommitted insert or update which has set the columns in the unique index to the same values trying.

PCTFREE and PCTUSED control whether Oracle will insert new rows in a block or not:

http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses007.htm#SQLRF30019

Also, if you're using ASSM then:

"This parameter is not useful and is ignored for objects with automatic segment-space management."

So the fact PCTUSED is null is probably OK.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Any resolution for this issue.... pls let's know

Raj, May 04, 2018 - 1:05 am UTC

@Girish,
Please let's know the resolution/fix that you've provided for this issue...?
Chris Saxon
May 04, 2018 - 9:49 am UTC

It's been two months, so you'd think so...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.