Oracle Scratchpad

September 9, 2011

Row Lock Waits

Filed under: Indexing,Locks,Oracle,Troubleshooting — Jonathan Lewis @ 6:19 pm GMT Sep 9,2011

Here’s one I keep forgetting – and spending 15 minutes trying to think of the answer before getting to the “deja vu” point again. I’ve finally decided that I’ve got to write the answer down because that will save me about 14 minutes the next time I forget.

Q. In a Statspack or AWR report there is a section titles “Segments by Row Lock Waits”. Why could an index be subject to a Row Lock Wait ?

A. Try inserting into a table from two different sessions (without committing) two rows with the same primary key. The second insert will wait on event enq: TX – row lock contention, and show up in v$lock with a lock request for a TX lock in mode 4. When you issue a commit or rollback on the first session, and the second statement errors or completes (depending on whether you commit or rollback the first session) it will increase the value for row lock waits in v$segstat (and v$segment_statistics) for the index by 1.

There are variations on the theme, of course, but the key feature is uniqueness with one session waiting for another session to commit or rollback on a conflicting value. This includes cases of foreign key constraint checking such as inserting a child for a parent that has been deleted but not committed (and there’s an interesting anomaly with that scenario which – in 10g, at least – reports more row lock waits on the parent PK than you might expect.)

3 Comments »

  1. Very nice explanation… your small notes are always very helpful and easy to understand.

    :)) I wonder how will it save your 14 minutes time next time, you might also forget that you have written it down somewhere.. (where, when….etc..)

    Comment by gkp — September 10, 2011 @ 11:45 am GMT Sep 10,2011 | Reply

  2. Thanks for the nice explanation. As you mentioned the contention is on Primary Key. Does it help if we keep the index as non-primary?

    Comment by ssaj2 — September 13, 2012 @ 7:27 pm GMT Sep 13,2012 | Reply

  3. [...] Jonathan Lewis’s post or this one [...]

    Pingback by Real-Time segments statistics « bdt's oracle blog — December 10, 2012 @ 6:28 am GMT Dec 10,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,298 other followers