CODEWITHSUNDEEP
oracle-databaseplsqltriggerslocking
PKey
PKey

Reputation: 3841

Disable triggers and re-enable triggers but avoid table alteration in meantime

I have the following situation: A table (MyTable) should be processed (updates/inserts/deletes etc) by a batch process (a call to a myplsql() procedure).

During myplsql execution no one should touch MyTable - so MyTable is locked in exclusive mode by myplsql.

Now MyTable has a number of on insert,on update, on delete triggers defined but those are not needed while performing batch processing - moreover they slow down the batch process extremely.

So the solution is to disable the triggers before calling myplsql().

But how to avoid someone touching the MyTable just after alter table ... disable trigger is performed and before myplsql manages to lock the table, given that alter table performs implicit commit - so any lock acquired before that will be lost anyway?

Part of the problem is that I do not control the other code or the other user that could try to touch the Table.

In a few words I need to perform the following in a single shot: 

Lock MyTable 
Disable Triggers (somehow without loosing the lock)
Process MyTable
Enable Triggers
Unlock MyTable

One thought was to remove grants from the table - and render it unusable for other users.

But as it is turns out - that is not an option as the other processes/users perform their operations logged in as a table owner user.

Thanks.

Upvotes: 0

Views: 794

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

A slightly different approach is to keep the triggers enabled but reduce (if not quite entirely remove) their impact, by adding a when clause something like:

create or replace trigger ...
...
for each row
when (sys_context('userenv', 'client_info') is null
   or sys_context('userenv', 'client_info') != 'BATCH')
declare
...
begin
...
end;
/

Then in your procedure add a call at the start as your 'disable triggers' step:

dbms_application_info.set_client_info('BATCH');

and clear it again at the end, just in case the session is left alive and reused (so you might want to do this in an exception handler too):

dbms_application_info.set_client_info(null);

You could also use module, or action, or a combination. While that setting is in place the trigger will still be evaluated but won't fire, so any thing happening inside will skipped - the trigger body does not run, as the docs put it.

This isn't foolproof as there is nothing really stopping other users/applications making the same calls, but if you pick a more descriptive string and/or a combination of settings, it would have to be deliberate - and I think you're mostly worried about accidents not bad actors.

Quick speed test with a pointless trigger that does just slows things down a bit.

create table t42 (id number);

-- no trigger
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.050

create or replace trigger tr42 before insert on t42 for each row
declare
  dt date;
begin
  select sysdate into dt from dual;
end;
/

-- plain trigger
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.466

create or replace trigger tr42 before insert on t42 for each row
when (sys_context('userenv', 'client_info') is null
   or sys_context('userenv', 'client_info') != 'BATCH')
declare
  dt date;
begin
  select sysdate into dt from dual;
end;
/

-- userenv trigger, not set
insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.460

- userenv trigger, set to BATCH

exec dbms_application_info.set_client_info('BATCH');

insert into t42 (id) select level from dual connect by level <= 10000;

10,000 rows inserted.

Elapsed: 00:00:00.040

exec dbms_application_info.set_client_info(null);

There's a bit of variation from making remote calls, but I ran a few times and it's clear that running with a plain trigger is very similar to running with the constrained trigger without BATCH set, and both are much slower than running without a trigger or with the constrained trigger with BATCH set. In my testing there's an order of magnitude difference.

Upvotes: 2

Related Questions