Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Bitmap causing deadlock

rishwingerDec 23 2013 — edited Dec 23 2013

Hi Experts

I understand the basic problem why deadlocks can happen with bitmap indexes in concurrent environments.

Thanks to TOM http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:582867200346727043

But, could anyone please explain what exactly is happening in the following case?


Deadlock graph:

  ---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-001a000a-000a9be5 146 393 X 149 947 S

TX-000b0020-007c4e3e 149 947 X 146 393 S

session 393: DID 0001-0092-000070C6 session 947: DID 0001-0095-0000457B

session 947: DID 0001-0095-0000457B session 393: DID 0001-0092-000070C6

Rows waited on:

  Session 393: obj - rowid = 00087875 - AACHh1AAAAAAAAAAAA

  (dictionary objn - 555125, file - 0, block - 0, slot - 0)

  Session 947: obj - rowid = 0008785C - AACHhcAAAAAAAAAAAA

  (dictionary objn - 555100, file - 0, block - 0, slot - 0)

----- Information for the OTHER waiting sessions -----

Session 947:

  sid: 947 ser: 7443 audsid: 80871604 user: 267/xxxxxx

  flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

  flags2: (0x40009) -/-/INC

  pid: 149 O/S info: user: grid, term: UNKNOWN, ospid: 25682

  image: oracle@kkkkkkkk

  client details:

  O/S info: user: ggggg, term: unknown, ospid: 1234

  machine: ffffff program: JDBC Thin Client

  application name: JDBC Thin Client, hash value=2546894660

  current SQL:

 

  INSERT INTO child table

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=b936wq8mpp6y7) -----

  

  UPDATE parent table

  SET fk = :1 ,

  WHERE 

  PK=:4

  

  

===================================================

Session 393: obj - rowid = 00087875 - AACHh1AAAAAAAAAAAA

(dictionary objn - 555125, file - 0, block - 0, slot - 0)

Session 947: obj - rowid = 0008785C - AACHhcAAAAAAAAAAAA

(dictionary objn - 555100, file - 0, block - 0, slot - 0)

555125,555100 data_object_id correspond to Bitamp index on parent ,child table respectively

The point which confuses is

STEP-1 is an Insert in child table

STEP-2 is update on parent table

Different bitmap keys are being updated by above steps then why there is a deadlock ?

Thanks

Rishwinger

Comments

Srinath Menon-Oracle 
Refer Oracle Database Administrator's Guide to install XA transaction recovery views\synonyms
  using the script xaview.sql. Contact your DBA.

You will need to navigate to the following location and run the xaview.sql script :

With version 12, you will need to enable XA transactions via the xaview.sql script (in the PDB container if using a PDB database)

cd $ORACLE_HOME/javavm/install

sqlplus sys/password@SID as sysdba @initxa.sql    (if this exists in your version of the DB)

cd $ORACLE_HOME/rdbms/admin

sqlplus sys/password@SID as sysdba @xaview.sql

Note that the SID above is for the container, if your DB uses that.
1 person found this helpful
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 20 2014
Added on Dec 23 2013
#general-database-discussions
3 comments
2,839 views