Re: 10g: parallel DML trigger race conditions?
From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 16 Feb 2011 05:08:44 -0800 (PST)
Message-ID: <25fced1d-ef94-415a-a6c5-f38081352509_at_p16g2000vbo.googlegroups.com>
On Feb 16, 7:34 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> Hi,
>
> are triggers executed in parallel for parallel DML?
> What happens, if the same record is updated _twice_ in a bulk update?
> Something like this:
> forall i reloads.first..reloads.last
> update account set money = money + reloads(i).amount where
> account.id = reloads.acct_id;
>
> Can it happen, that two "trigger before insert or update on account
> for each row" are executing in parallel for the same record?
> - if acct_id = X is there multiple times in table reloads.
>
> So they will find the same :old conditions.
> Or does oracle take care for sequential execution of trigger for same
> record?
>
> - thanks!
>
> rgds,
> Frank
Date: Wed, 16 Feb 2011 05:08:44 -0800 (PST)
Message-ID: <25fced1d-ef94-415a-a6c5-f38081352509_at_p16g2000vbo.googlegroups.com>
On Feb 16, 7:34 am, Frank Bergemann <FBergem..._at_web.de> wrote:
> Hi,
>
> are triggers executed in parallel for parallel DML?
> What happens, if the same record is updated _twice_ in a bulk update?
> Something like this:
> forall i reloads.first..reloads.last
> update account set money = money + reloads(i).amount where
> account.id = reloads.acct_id;
>
> Can it happen, that two "trigger before insert or update on account
> for each row" are executing in parallel for the same record?
> - if acct_id = X is there multiple times in table reloads.
>
> So they will find the same :old conditions.
> Or does oracle take care for sequential execution of trigger for same
> record?
>
> - thanks!
>
> rgds,
> Frank
Not directly answering your question but FORALL is not performing parallel DML, it is merely doing a bulk operation that minimizes the overhead of single row operations. I'm not aware of it being able to run in parallel DML mode.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Feb 16 2011 - 07:08:44 CST