Blocking lock!

From: wangbin <wangbin_at_start.com.au>
Date: 7 Aug 2002 19:06:39 -0700
Message-ID: <2d15bd69.0208071806.2f0b7931@posting.google.com>

Hi

I use this query to check the blocking locks. SELECT DISTINCT o.object_name,

                sh.username ||
                '(' ||
                sh.sid ||


                ')' Holder,

                qh.sql_text htext,
                sw.username ||
                '(' ||
                sw.sid ||


                ')' Waiter,

                qw.sql_text wtext,
                DECODE (
                   lh.lmode,
                   1, 'NULL',
                   2, 'row share',
                   3, 'row exclusive',
                   4, 'share',
                   5, 'share row exclusive',
                   6, 'exclusive'
                ) l_type
  FROM all_objects o, v$session sw, v$lock lw, v$sqlarea qw,
        v$session sh, v$lock lh, v$sqlarea qh
 WHERE lh.id1 = o.object_id
   AND lh.id1 = lw.id1
   AND sh.sid = lh.sid
   AND sw.sid = lw.sid
   AND sw.sql_address = qw.address
   AND sw.sql_hash_value = qw.hash_value
   AND sh.sql_address = qh.address
   AND sh.sql_hash_value = qh.hash_value
   AND sh.lockwait IS NULL
   AND sw.lockwait IS NOT NULL

   AND lh.type = 'TM'
   AND lw.type = 'TM'
/

It works fine when I test it. I set up a job in production. I cannot understand the first row of the following part. The second row is fine, which means WAITER is waiting for one row in table ROLE. I think Object Name should always appear in Waiter Text, since Waiter hungs for the HOLDER. But why in the first row I cannot find BU_EVENTLOG in Waiter Text?

Object                    Holder                Waiter
Name        HOLDER        Text       WAITER     Text         L_TYPE

----------- -------- -------------- -------- -------------

---

BU_EVENTLOG ...(21) SELECT * ...(75) SELECT * row exclusive

                     from Role               from Role
                     where  (                where  (
                     Title = :1)             Title = :1)
                     FOR UPDATE              FOR UPDATE
                
ROLE        ...(21)  SELECT  *      ...(75)  SELECT  *     row share
                     from Role               from Role
                     where  (                where  (
                     Title = :1)             Title = :1)
                     FOR UPDATE              FOR UPDATE       

Regards,
Bin Received on Wed Aug 07 2002 - 21:06:39 CDT