Oracle Scratchpad

September 9, 2011

Row Lock Waits

Filed under: Indexing,Locks,Oracle,Troubleshooting — Jonathan Lewis @ 6:19 pm BST 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.)


  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 BST 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 BST Sep 13,2012 | Reply

    • ssaj2,

      Sorry this reply is so late in arriving – I must have missed the comment when it first appeared.

      For anyone else who may need the same question answered: you can have unique constraints and primary key constraints protected by non-unique indexes. This issue is essentially about the uniqueness of the constraint – so the locks can appear even if the index is non-unique.

      Comment by Jonathan Lewis — April 20, 2016 @ 4:14 pm BST Apr 20,2016 | 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

  4. […] HW-Contention was on a LOB segment,but the TX-row lock contention was on index segment for a heavily inserted table. I was wondering why there should be  a row lock contention on a index rather than on a table.Googled for some answers and came across this post by Jonathan Lewis here. […]

    Pingback by Row lock waits on indexes | Oracle DBA thoughts — July 9, 2015 @ 3:27 am BST Jul 9,2015 | Reply

  5. i found row lock wait on table partition of STTM_CUST_ACCOUNT bt didn’t found any insert on this table

    Comment by Akshay patil — April 19, 2016 @ 1:47 pm BST Apr 19,2016 | Reply

    • Akshay,

      This note is about row lock waits on indexes – which are a little counter-intuitive – not row lock waits on tables which simply mean that you’ve got two sessions trying to update or delete the same row.

      Comment by Jonathan Lewis — April 20, 2016 @ 4:12 pm BST Apr 20,2016 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: