| Bitmap causing deadlock [message #604038] |
Mon, 23 December 2013 04:36  |
 |
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 #604052 is a reply to message #604047] |
Mon, 23 December 2013 06:30  |
|
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 #604144 is a reply to message #604038] |
Tue, 24 December 2013 17:29 |
 |
Kevin Meade
Messages: 2103
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 #604152 is a reply to message #604151] |
Wed, 25 December 2013 01:08 |
|
Kevin Meade
Messages: 2103
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 #604366 is a reply to message #604152] |
Sun, 29 December 2013 07:59 |
|
Kevin Meade
Messages: 2103
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
