Re: Autonomous transactions and deadlocks

From: mohamed houri <mohamed.houri_at_gmail.com>
Date: Mon, 25 Mar 2013 15:24:11 +0100
Message-ID: <CAJu8R6guxK59-fjQfiD9K_ex_x4o=EsRuaMHKwfWP7juv1OvYQ_at_mail.gmail.com>

In my opinion, there are not many situations where autonomous transaction can be safely used. There is one place where I have used it. It is during a batch job treating several records, and, where I don't want to stop the batch process in case of error in the treatment of one record. In that case I can use the autonomous transaction in the exception part to log the error without raising the exception in order to let the job processing the next record.

And, even in such kind of situations, I am able, using rollback to savepoint appropriately, to log the error without using the autonomous transaction.

In your case you are using bitmap index in what seems to be an OLTP multi-concurrent application. You are enhancing the chance to see locks and deadlocks

http://hourim.wordpress.com/2011/03/14/deadlock-%E2%80%93-part-1-bitmap-index/

In addition to the use of bitmap indexes in an OLTP system, you are using autonomous transaction, which as I said before, opens its own transaction independently of the session it belongs to which has the effect of adding concurrency to your existing mutli-concurrent process.

You had better to expose what you want to achieve and get rid of those bitmap indexes if you are in a highly concurrent OLTP application.

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/3/25 Hans Forbrich <fuzzy.graybeard_at_gmail.com>

> Look in detail at how locking works when updating bitmap indexes. (See
> Richard Foote's blog, as a great resource for this.)
>
> If you are updating the bitmap column, you are shooting yourself in the
> foot as it is well known that updating a bitmap locks many, many, many
> rows. (Possibly, but not necessarily all rows in the table.) So you
> have one row locked, and you fire off an AT that wants to lock all sorts
> of rows, but is waiting for your first session to release the lock on
> that one row.
>
> Again, describe your requirement in detail if you want potential
> solutions or workarounds. Until then, we will be restricted to
> describing possible scenarios.
>
> /Hans
>
> On 25/03/2013 7:13 AM, rajugaru.vij_at_gmail.com wrote:
> > Version 10g R2..
> >
> > AT when working with bitmap index..
> >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Bien Respectueusement
Mohamed Houri


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 25 2013 - 15:24:11 CET