Re: Are triggers serialized?

On Sat, 25 Oct 2003 14:37:52 GMT, "Mark Wayne" <spam_at_earthlink.net> wrote:

>I'm just learning some PL SQL. If multiple processes are inserting into a
>table, will multiple instances of my trigger execute, possibly
>simultaneously?
>
>Thanks,
>mark
>
>

Hi Mark,

Trigges are serialized in the sense that 2 triggers cannot update the same rows simultanously. So things won't get messed up, because Oracle ensures *read consistency*, e.g. :

Process 1 (P1)inserts into a table, which causes a trigger to fire and update several rows in this and other tables.

process 2 (P2) inserts into that table, the trigger fires again and updates several rows in this and other tables, possibly same rows that were affected by P1.

If P1 has not committed before P2 starts its update, P2 must wait for P1 to commit in order to him to update. If P2 just needs to read the rows P1 is updating, it fetches the before-update image in rollback-segments.

But a deadlock between P1 and P2 may occur. If the trigger updates several tables /several rows, deadlocks become more likely. And the more simultaneous trigger-firing events happen, the more likely deadlocks.

That's one of more major drawbacks with triggers.

At first, they do things behind the back. That means the developer constantly needs to withhold his ordinary code with the trigger code. It can be tedious and increases the likelihood of errors.

At second, they don't scale well : Both at the system level (e.g. deadlocks) and at the application level. When an application grows in functionality, the triggers must handle more and more specific cases, and the interaction between triggers and other code becomes more tedious.

Not that triggers are just bad. Just use them with care.

• Kenneth Koenraadt