16 April 2008
Deadlocks caused by a bitmap index
Deadlocks caused by a bitmap index
One of our major Peoplesoft pay calculation processes started failing with deadlock errors:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0016001a-0003c58c 100 234 X 114 208 S
TX-0028002c-0000783c 114 208 X 100 234 S
Note that TX lock "X" and "S" are type 4 and type 6.
Both session were updating ONLY 1 employee each, and that was a different employee for each session, so this at the first glance looked like a bug. But, it wasn't.
From the following section, I converted "02220DB7" to 35786167 and found that they were waiting on the bitmap index.
Rows waited on:
Session 208: obj - rowid = 02220DB7 - ACIg23AAAAAAAAAAAA
(dictionary objn - 35786167, file - 0, block - 0, slot - 0)
Session 234: obj - rowid = 02220DB7 - ACIg23AAAAAAAAAAAA
(dictionary objn - 35786167, file - 0, block - 0, slot - 0)
The update they were running was updating the column defined in the bitmap index. That is when things started clicking.
Each entry in the bitmap index covers many rows. If 2 sessions attempt to update the same index fragment, one of them will have to wait. The wait can happen even in the case when sessions are updating different rows. Relevant locks are type 6 and 4, exactly as what we had.
We solved our problem by removing the index, as it was temporarily put there for some slow SQL. I tuned the SQL, dropped the index and... voila ! back to normal.
Keywords "bitmap index" "deadlock"
