Deadlock & DBMS_ROWID weirdness.

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

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

--

Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767

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