Home » SQL & PL/SQL » SQL & PL/SQL » Locked object in gv$locked_object without transaction in gv$transaction (Oracle 11g r2 , Red Had RAC)
Locked object in gv$locked_object without transaction in gv$transaction [message #663272] Sat, 27 May 2017 08:19 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,

I am trying to understand why a certain object ( table , object_id = 189035) can appear in gv$locked object:

Connected to:
SQL>
SQL>
SQL>
SQL> select 1 from gv$locked_object a where object_id = 189035;

         1
----------
         1
         1

SQL>




And when I build my query using "exists" to correlate to gv$transaction I also get record:

SQL> select 1
  2    from gv$locked_object a
  3   where exists (select 1
  4            from gv$transaction c
  5           where a.xidusn = c.xidusn
  6             and a.xidslot = c.xidslot)
  7     and a.object_id = 189035
  8     and exists
  9   (select 1 from dba_objects b where b.OBJECT_ID = a.object_id)
 10  ;

         1
----------
         1



However it *does not show* to have a corresponding record in GV$TRANSACTION when I *join* them:

SQL> select 1
  2    from gv$locked_object a, gv$transaction c, dba_objects b
  3   where a.xidusn = c.xidusn
  4     and a.xidslot = c.xidslot
  5     and a.object_id = 189035
  6     and b.OBJECT_ID = a.object_id;

no rows selected

SQL>

Now , this happens only with this table.
If I try to create a copy of that table (object_id = 241566) and I do the query on it, it works for every way I try:


SQL> lock table table1 in exclusive mode;

Table(s) Locked.

SQL> select 1 from table1 for update;

         1
----------
         1
         1
         1
         1
         1
         1
         1
         1

8 rows selected.

SQL>
SQL> select 1
  2    from gv$locked_object a, gv$transaction c, dba_objects b
  3   where a.xidusn = c.xidusn
  4     and a.xidslot = c.xidslot
  5     and a.object_id = 241566
  6     and b.OBJECT_ID = a.object_id;

         1
----------
         1

SQL>
SQL>
SQL> select 1
  2    from gv$locked_object a
  3   where exists (select 1
  4            from gv$transaction c
  5           where a.xidusn = c.xidusn
  6             and a.xidslot = c.xidslot)
  7     and a.object_id = 241566
  8     and exists
  9   (select 1 from dba_objects b where b.OBJECT_ID = a.object_id);

         1
----------
         1

SQL>
SQL>



How can I understand what is going on here ?
why isn't my join working , and why specific for this table ?



Many thanks in advance,
Andrey R

[Updated on: Sat, 27 May 2017 08:22]

Report message to a moderator

Re: Locked object in gv$locked_object without transaction in gv$transaction [message #663285 is a reply to message #663272] Sun, 28 May 2017 14:00 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,
Any inputs ?
Things I can check ?

Would be great if anyone can share his thoughts.
Thanks.

Andrey
Re: Locked object in gv$locked_object without transaction in gv$transaction [message #663622 is a reply to message #663285] Sun, 11 June 2017 11:56 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Someone ? Anyone ? Sad
Re: Locked object in gv$locked_object without transaction in gv$transaction [message #663642 is a reply to message #663622] Mon, 12 June 2017 05:37 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Your queries (SELECT 1 ....) do not project a lot of information, you know. What table is it? What type of table? What type of locks?

Also remember that there is no read consistency on v$ views. They are not protected by undo, so you can never be sure that you are actually getting meaningful information.
Re: Locked object in gv$locked_object without transaction in gv$transaction [message #663738 is a reply to message #663272] Thu, 15 June 2017 10:25 Go to previous message
Caffeine+
Messages: 14
Registered: February 2017
Junior Member
I'm unable to reproduce the issue in 11.2.0.4. Can you post the table's DDL? Here's my try at reproducing a test case:


SQL> drop table bogus
>> drop table bogus
Error at line 3
ORA-00942: table or view does not exist

SQL> create table bogus as select * from dba_objects
Table created.
SQL> select count(*) from bogus

  COUNT(*)
----------
    134719
1 row selected.
SQL> lock table bogus in exclusive mode
Lock complete.
SQL> select object_id,data_object_id from user_objects where object_name='BOGUS'

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
   2849099        2849099
1 row selected.
SQL> select * from gv$locked_object where object_id = 2849099

   INST_ID     XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS                  LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
         1          0          0          0    2849099       1722 ***** ****** 10100:3044                         6
1 row selected.
SQL> SELECT 1
  FROM gv$locked_object a
 WHERE     EXISTS
              (SELECT 1
                 FROM gv$transaction c
                WHERE a.xidusn = c.xidusn AND a.xidslot = c.xidslot)
       AND a.object_id = 2849099
       AND EXISTS
              (SELECT 1
                 FROM dba_objects b
                WHERE b.OBJECT_ID = a.object_id)
no rows selected.
Previous Topic: Help the newbie to do my first procedure
Next Topic: Oracle database link performance cum security issue
Goto Forum:
  


Current Time: Fri Apr 19 05:47:55 CDT 2024