Database Reference
In-Depth Information
Here, I will use an autonomous transaction in the database to have two concurrent transactions in a single
session. An autonomous transaction starts a “subtransaction” separate and distinct from any already established
transaction in the session. The autonomous transaction behaves as if it were in an entirely different session—for all
intents and purposes, the parent transaction is suspended. The autonomous transaction can be blocked by the parent
transaction (as we'll see) and, further, the autonomous transaction can't see uncommitted modifications made by the
parent transaction. For example:
EODA@ORA12CR1> create table t
2 ( processed_flag varchar2(1)
3 );
Table created.
EODA@ORA12CR1> create bitmap index
2 t_idx on t(processed_flag);
Index created.
EODA@ORA12CR1> insert into t values ( 'N' );
1 row created.
EODA@ORA12CR1> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values ( 'N' );
5 commit;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
See the “Setting up Your environment” section at the beginning of this topic for details on how to set your SQl
prompt to display environment information such as user name and database name.
Tip
Since I used an autonomous transaction and created a subtransaction, I received a deadlock—meaning my
second insert was blocked by my first insert. Had I used two separate sessions, no deadlock would have occurred.
Instead, the second insert would have just blocked and waited for the first transaction to commit or roll back. This
symptom is exactly what the project in question was facing—the blocking, serialization issue.
So we had an issue whereby not understanding the database feature (bitmap indexes) and how it worked doomed
the database to poor scalability from the start. To further compound the problem, there was no reason for the queuing
code to ever have been written. The database has built-in queuing capabilities and has had them since version 8.0 of
Oracle—which was released in 1997. This built-in queuing feature gives you the ability to have many producers (the
sessions that insert the N, the unprocessed records) concurrently put messages into an inbound queue and have many
consumers (the sessions that look for N records to process) concurrently receive these messages. That is, no special code
should have been written in order to implement a queue in the database. The developers should have used the built-in
feature. And they might have, except they were completely unaware of it.
 
 
Search WWH ::




Custom Search