Re: Deadlock & DBMS_ROWID weirdness.

Hi Norm!

Have happy New Year! I have run into that too, a long, long time ago, but not in the galaxy far, far away. In distant galaxies, they use SQL Server and that is known as "Redgate shift". Your problem is in the fact that dbms_rowid.rowid_object doesn't return object id, it returns data_object_id. Data object can change, for instance by executing "alter table <tbl> move;" Here is the documentation:

https://docs.oracle.com/database/121/ARPLS/d_rowid.htm#ARPLS67768

      ROWID_OBJECT Function

This function returns the_*data object number*_ for an extended|ROWID|. The function returns zero if the input|ROWID|is a restricted|ROWID|.

Here is the page that explains the difference on the AskConnor site:

https://asktom.oracle.com/pls/apex/asktom.search?tag=meaning-of-object-id-and-data-object-id-in-dba-objects

The column that you need to join with is DATA_OBJECT_ID, not OBJECT_ID.

Regards

On 1/3/2019 5:12 AM, Norman Dunbar wrote:
> Happy New Year everyone.
>
>
> It has been a while since I was last this puzzled, but here goes.
> Hopefully someone here will know what's going on.
>
>
> Environment:
>
> 64bit Oracle 11.2.0.4 Enterprise Edition
> on IBM/AIX RISC System/6000
>
> The table in question is not partitioned.
> The table in question is not compressed.
> The table has not been recreated in any way, since 2016.
> The table might have been "defragmented" at some point using
>
>     ALTER TABLE ... MOVE;
>
> Anything even slightly security conscious has been obfuscated.
>
>
>
> Problem Summary:
>
> Do the ROWID(s) mentioned in the header of a deadlock trace actually
> match up to the objects involved in the deadlock? Is this always the
> case?
>
>
> Background:
>
> I have an 'enq: TX - row lock contention' deadlock on a DELETE
> statement. I'm not bothered by the deadlock itself, that will be
> sorted by the developers, however the deadlock graph is as follows,
> apologies if the formatting goes awry:
>
>
> Deadlock graph:
>                        ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name          process session holds waits  process session
> holds waits
> TX-002a001b-003f1aa4      1200     119     X           1203 585      X
> TX-000d0021-002ab559      1203     585     X           1200 119      X
>
> session 119: DID 0001-04B0-00018CDC     session 585: DID
> 0001-04B3-000135DC
> session 585: DID 0001-04B3-000135DC     session 119: DID
> 0001-04B0-00018CDC
>
> Rows waited on:
>   Session 119: obj - rowid = 000F62D2 - AAEGjbAQAAApJ2CAAY
>   (dictionary objn - 1008338, file - 1024, block - 10788226, slot - 24)
>   Session 585: obj - rowid = 000F62D2 - AAEGjbAQAAApJ3rAAD
>   (dictionary objn - 1008338, file - 1024, block - 10788331, slot - 3)
>
>   ...
>
>     Current Wait Stack:
>      0: waiting for 'enq: TX - row lock contention'
>         name|mode=0x54580006, usn<<16 | slot=0xd0021, sequence=0x2ab559
>
>   ...
>
>
>
> Problem:
>
> Then OBJECT_ID, 1008338, given above is the correct one for the table
> in the DELETE statement that is deadlocking. However,
> DBMS_ROWID.ROWID_INFO, or, DBMS_ROWID.ROWID_OBJECT, give a completely
> different object number given the ROWIDs above - AAEGjbAQAAApJ2CAAY
> and AAEGjbAQAAApJ3rAAD, or indeed, for every rowid in the table.
>
> Querying  the database, I get the following:
>
>
> -- Retrieve the table's OBJECT_ID:
> select object_id
> from dba_objects
> where object_name = 'TABLE_NAME_IN_QUESTION'
> and object_type = 'TABLE';
>
> OBJECT_ID
> ---------
>   1008338
>
>
> -- Retrieve the OBJECT_ID from the ROWIDs:
> select dbms_rowid.rowid_object(chartorowid('AAEGjbAQAAApJ3rAAD')) as
> OBJECT_ID from dual;
>
> OBJECT_ID
> ---------
>   1075419
>
> select dbms_rowid.rowid_object(chartorowid('AAEGjbAQAAApJ2CAAY')) as
> OBJECT_ID from dual;
>
> OBJECT_ID
> ---------
>   1075419
>
>
> -- Retrieve the OBJECT_ID from the entire table:
> select dbms_rowid.rowid_object(rowid) as OBJECT_ID, count(*)
> from TABLE_NAME_IN_QUESTION
> group by dbms_rowid.rowid_object(rowid);
>
> OBJECT_ID COUNT(*)
> --------- -------
>   1075419  249214
>
> As far as DBMS_ROWID is concerned, every row in the table with
> OBJECT_ID =  1008338 has an OBJECT_ID of 1075419, which is not true.
> There is no object in the database which has this OBJECT_ID.
>
> Equally "amusing" is the fact that when I select from the table with
> the above ROWIDs, I get a completely different ROWID back which
> doesn't match the one I requested in the WHERE clause:
>
>
> select rowid as ri
> from TABLE_NAME_IN_QUESTION
> where rowid in ('AAEGjbAQAAApJ2CAAY', 'AAEGjbAQAAApJ3rAAD');
>
> RI
> ------------------
> AAEGjbAACAAJJ2CAAY
> AAEGjbAACAAJJ3rAAD
>        ^^  ^
> The markers should, hopefully, be pointing at the differing characters
> in the ROWIDs. If not, characters 8, 9 and 12 are different.
>
> Ok, I can cope with this if the rows in the table are relocated, I'm
> assuming that this would be what I saw? I ask for a specific ROWID and
> Oracle says "it has moved, here's the forwarding address".
>
> I see similar problems in other deadlock traces for this database.
> (Yes, there are a few that need sorting out - my last task of 2018 and
> my first of 2019!)
>
> As ever, I'm grateful for any advice that is offered, thanks.
>
>
> Cheers,
> Norm.
>

-- 
Mladen Gogala Database Consultant Tel: (347) 321-1217

--
http://www.freelists.org/webpage/oracle-l