2 Replies Latest reply: Jul 27, 2014 6:04 PM by Mark D Powell RSS

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

    wavelet678

      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".

       

      Thanks,

      Henry Xiao

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

          try SQL below instead

           

           

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

                                 'Waiter: ')

                 ||vl.sid sess,

                 status,

                 id1,

                 id2,

                 lmode,

                 request,

                 vl.TYPE

          FROM   v$lock vl,

                 v$session vs

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

                                                  id2,

                                                  TYPE

                                           FROM   v$lock

                                           WHERE  request > 0)

                 AND vl.sid = vs.sid

          ORDER  BY id1,

                    request

           

          /

          • 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 --