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