2 Replies Latest reply on Jul 27, 2014 11:04 PM by Mark D Powell

    enq: TX - row lock contention and v$lock question


      I have a question on enqueue and v$lock.

      normally,we use the following query to find the lock in database:


      select a.sid holdsid,b.sid waitsid,a.type,a.id1,a.id2,a.ctime from v$lock a,v$lock b

      where a.id1=b.id1 and a.id2=b.id2 and a.block=1 and b.request>0;


      but this time,I find a lot of "enq: TX – row lock contention" in instance,but don't find a session with block=1!

      why all session is block=0 but still can see  "enq: TX – row lock contention"?when will block=1 in v$lock?

      all those sessions are running "select for update".



      Henry Xiao

        • 1. Re: enq: TX - row lock contention and v$lock question

          try SQL below instead



          SELECT Decode(request, 0, 'Holder: ',

                                 'Waiter: ')

                 ||vl.sid sess,







          FROM   v$lock vl,

                 v$session vs

          WHERE  ( id1, id2, vl.TYPE ) IN (SELECT id1,



                                           FROM   v$lock

                                           WHERE  request > 0)

                 AND vl.sid = vs.sid

          ORDER  BY id1,




          • 2. Re: enq: TX - row lock contention and v$lock question
            Mark D Powell

            Henry, what version of Oracle and is this a RAC or non-RAC environment?  Are there an FK on the objects in question?  Are the FK indexed?  How does the select for update process in question work?

            - -

            What might be wrong is a little hard to say since we know knowing about your environment and the original situation is almost surely long resolved by now.

            - -

            HTH -- Mark D Powell --