Home » RDBMS Server » Performance Tuning » Bitmap causing deadlock (11.2.0.3)
Bitmap causing deadlock [message #604038] Mon, 23 December 2013 04:36 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts

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

Thanks to TOM [url=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
Re: Bitmap causing deadlock [message #604047 is a reply to message #604038] Mon, 23 December 2013 05:53 Go to previous messageGo to next message
Roachcoach
Messages: 1128
Registered: May 2010
Location: UK
Senior Member
Is the FK indexed?
Re: Bitmap causing deadlock [message #604052 is a reply to message #604047] Mon, 23 December 2013 06:30 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Nope ,FK is not indexed.

But as I understand Information in session waiting section pinpoint two objects both of them turns out to be BITMAP index, so its a case of deadlock due to bitmap index not of deadlock due to un-index FK , Please correct me if I am wrong

Regards
Rishwinger

[Updated on: Mon, 23 December 2013 06:33]

Report message to a moderator

Re: Bitmap causing deadlock [message #604068 is a reply to message #604052] Mon, 23 December 2013 08:46 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
does error still occur when non-bitmap indexes are involved?
Re: Bitmap causing deadlock [message #604069 is a reply to message #604068] Mon, 23 December 2013 09:10 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Quote:
does error still occur when non-bitmap indexes are involved?


It happened once in one of the testing environment.

Regards
Rishwinger



Re: Bitmap causing deadlock [message #604070 is a reply to message #604069] Mon, 23 December 2013 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
rishwinger wrote on Mon, 23 December 2013 07:10
Quote:
does error still occur when non-bitmap indexes are involved?


It happened once in one of the testing environment.

Regards
Rishwinger





>so its a case of deadlock due to bitmap index

how can bitmap index be the cause of error when they don't exist?


https://community.oracle.com/thread/2615426

[Updated on: Mon, 23 December 2013 09:16]

Report message to a moderator

Re: Bitmap causing deadlock [message #604144 is a reply to message #604038] Tue, 24 December 2013 17:29 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
for other readers not in the know, let us review first why a BITMAP index can lead to DEADLOCK.

A deadlock requires two transactions (fyi a single session can create multiple transactions using autonomous transactions and so can deadlock itself).

Each transaction is waiting on the other for a lock the other holds. The typical example is: Transaction one updates employee jones. Transaction two updates employee smith. Then transaction one tries to update employee smith but must wait because of transaction two. Transaction two tries to update employee jones but must wait on transaction one. So T1 waits on T2 and T2 waits on T1. They are blocking each other. This is deadlock and Oracle will pick one of the transactions and kill it. The picking is not random but we have no control over it so it is considered random.

This explanation of deadlock involves individual rows. This is because Oracle usually does row level locking. But there are situations where Oracle does BLOCK LEVEL LOCKING. In these situations, instead of waiting on a row level lock, transactions are waiting on block level locks. The kicker is that in order to get a block level lock a transaction only needs to lock any row in the block. Thus locking any row in the block is the same as locking all rows in the block for any situation where Oracle does block level locking.

BITMAP INDEXES DO BLOCK LEVEL LOCKING. Inserts and deletes are special cases for the bitmap index so UPDATING is the primary concern when using BITMAP indexes. Update a row in a bitmap index and you lock the block which means you lock all rows in the block until your transaction commits. But it gets deeper. BITMAP indexes compound the problem because they are a very compressed form of information. Thus there can be tens of thousands of rows in a bitmap index block. So locking the block can lock thousands or tens of thousands of rows at a time. This is why it is so easy to deadlock when updating tables with bitmap indexes.

----------------------------
As for the locking this may have something to do with your foreign keys.

When you insert a row into a child table, the parent row must be protected from being deleted or having its primary key changed (think rollback).

If you update the primary key of a parent row, Oracle must prevent child orphans with the old key, and the new key (think rollback).

If you delete a parent row, Oracle must prevent existing orphans and the creation of new orphans (think rollback).

Oracle does this magic using locks on index pages. If the affected index is a bitmap index then you are locking lots of rows with each change, not just one.

If a FK is not indexed then how would oracle provide the needed protections? Lock the entire table as I recall.

Of particular note, indexing foreign keys is necessary in systems where parent rows can be physically deleted or primary key can be changed. Otherwise incidence of deadlock goes way up.
----------------------------------------

At least this is how I recall it all working. Check your processes to see where you violate these rules. Maybe even try to duplicate the issue.

Additionally we need to see more of the update. Your update makes no sense. What is FK on the parent table?

Kevin
Re: Bitmap causing deadlock [message #604151 is a reply to message #604144] Wed, 25 December 2013 00:33 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
Is the "answer" the same for both OLTP & data warehouse databases?
should bitmap indexes be used in OLTP environment?
Re: Bitmap causing deadlock [message #604152 is a reply to message #604151] Wed, 25 December 2013 01:08 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
To BlackSwan, I am not sure what you mean by "is the answer the same" but for OLTP, no, BITMAP indexes were created for star schemas not OLTP data models. Other uses are possible, but these other uses are not the use case for which the feature was incarnated. Whenever any feature is used for use cases other than its intended use cases, there will be significant tradeoffs to be accounted for. There are such tradeoffs even when a feature is used for its proper use case.

The intended use case of the BITMAP index is the star schema. In this situation we are looking to do a bitmap merge of multiple indexes to find a small number of rows from our fact table. It is this special feature of BITMAP indexes, their ability to combine their results before going to the table, that makes them useful for the generalized problem of an analytic system which is that a query can be any arbitrary combination of its dimensions and all queries should be equally fast relative to the costing of dimensions used. It is not possible to provide this definition of query performance using BTREE indexes because we would need to create too many of them for even a modest star schema for things to be practical. But with BITMAP indexes, we only need one FK index on the fact table for each dimension reference. N dimenions requires only N BITMAP indexes, as opposed to potentially N! BTREE indexes to do the same thing from a performance perspective.

The significant tradeoff of BITMAP indexes is that updating a BITMAP index locks large numbers of rows and therefore significantly limits scalability and concurrency. A successful OLTP application requries scalability and concurrency to survive and meet its SLAs. So BITMAP indexes kill OLTP apps. Only a true expert would attempt using a BITMAP index in an OLTP system and I presume they would have a really good cause going for them that they can clearly explain to others. I have never done it. I have only seen DEADLOCK DETECTED when others have tried.

In a star schema, load processes will typically drop BITMAP indexes before loading and rebuild them after their loads. Some systems got smarter and use partitioned tables where the load process is mostly to create the next partition and add it to the table. These don't drop the indexes. They just do a partition swap with new index already attached to the new partition.

In EXADATA, analytic systems can choose to flatten their designs and rely on Partition Pruning, Storage Indexes, and SMARTSCAN to eliminate the I/O. Indexes are not built unless a clear case is defined for them. These designs have no dimension tables because all the dimensions are "degenerate".

I doubt there is any "NEAT TRICK" around this issue. If there was we would all know about it.

Nice to see some people thinking about such things though. Kevin

[Updated on: Sat, 08 March 2014 13:44] by Moderator

Report message to a moderator

Re: Bitmap causing deadlock [message #604186 is a reply to message #604152] Wed, 25 December 2013 23:51 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
We have an OLTP environment with high number of Bitmap Index(~60), would it be helpful if we convert all these Bitmap Indexes to B-tree?
Do we have any other option ,Please advise

Regards
Rishwinger
Re: Bitmap causing deadlock [message #604187 is a reply to message #604186] Thu, 26 December 2013 00:01 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
> I have only seen DEADLOCK DETECTED when others have tried.
+1
>would it be helpful if we convert all these Bitmap Indexes to B-tree?
It would not be helpful to me.
Why would you change something that is working for you?
Re: Bitmap causing deadlock [message #604208 is a reply to message #604187] Thu, 26 December 2013 05:15 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
BlackSwan makes an excellent observation as usual.

If you were having issues with BITMAP indexes in OLTP you would be seeing DEADLOCK DETECTED.

I would pose two questions to you:

1. what do you characterize as an OLTP app?
2. why did you decide to use BITMAP indexes in the first place? Was it a concious choice? If so what were you trying to achieve by it?

Kevin
Re: Bitmap causing deadlock [message #604366 is a reply to message #604152] Sun, 29 December 2013 07:59 Go to previous message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
If we are bulk loading data then we do not have an OLTP application anymore.

OLTP is by definition a system which supports a scalable concurrency for DML (insert/update/delete) with many users doing small, planned, transactions that use constraints and edits to provide a high level of data integrity and data quality.

BULK LOADING almost always has only a single loading process active at a time. This process can also take advantages of PARALLEL DML and COMMIT after each operation since the integrity and edits of the data have in theory already been done to the data (or in some cases will be done in a later step).

Thanks, Kevin

[Updated on: Sat, 08 March 2014 13:44] by Moderator

Report message to a moderator

Previous Topic: Performance Issue
Next Topic: Normally why count (*) taking so much of time when compare to select *
Goto Forum:
  


Current Time: Thu Apr 24 05:24:46 CDT 2014

Total time taken to generate the page: 0.05900 seconds