We have several nightly jobs running inside an Oracle 11g R2 instance, not all of these jobs are under our control. Some of them are external data loads run by third parties. The jobs are implemented as PL/SQL packages and run using DBMS_SCHEDULER facilities. Some of these jobs operate on the same set of data, a table with user entries, e. g. updating personal data, removing retired users, adding newly joined users. Since the jobs mostly use bulk statements to run the updates, we have run into blocking locks several times now, having to kill single jobs to allow others to run through. What are good ways to prevent jobs from colliding with each other? I am thinking about things like: setting up a meta-job which knows about dependencies and coordinates the dependent jobs creating schedules which keep conflicting jobs as separate as possible coordinating jobs with the third parties to prevent conflicts between "external" and "internal" jobs not using bulk statements (updating everything at once with a single MERGE or UPDATE ) but instead update one by one committing the intermediate results Especially the last option seems a plausible approach to me in order to reduce the probability of blocking locks. But I know that performance suffers a lot when I switch our jobs from bulk updates to looping over cursors.

Reputation: 1683

Oracle bulk jobs, how to prevent locks

We have several nightly jobs running inside an Oracle 11g R2 instance, not all of these jobs are under our control. Some of them are external data loads run by third parties. The jobs are implemented as PL/SQL packages and run using DBMS_SCHEDULER facilities.

Some of these jobs operate on the same set of data, a table with user entries, e. g. updating personal data, removing retired users, adding newly joined users. Since the jobs mostly use bulk statements to run the updates, we have run into blocking locks several times now, having to kill single jobs to allow others to run through.

What are good ways to prevent jobs from colliding with each other?

I am thinking about things like:

setting up a meta-job which knows about dependencies and coordinates the dependent jobs
creating schedules which keep conflicting jobs as separate as possible
coordinating jobs with the third parties to prevent conflicts between "external" and "internal" jobs
not using bulk statements (updating everything at once with a single MERGE or UPDATE) but instead update one by one committing the intermediate results

Especially the last option seems a plausible approach to me in order to reduce the probability of blocking locks. But I know that performance suffers a lot when I switch our jobs from bulk updates to looping over cursors.

Upvotes: 2

Answers (3)

Mark J. Bobak

Reputation: 14423

This may be a good use of the DBMS_LOCK package. DBMS_LOCK allows you access to the same enqueue/locking model that Oracle uses internally.

You can establish an enqueue, and then multiple processes may take that enqueue in various lock modes. Locks will show up like any other enqueue, with type 'UL' (for user lock).

For example, suppose you have three processes that can all run concurrently, but then you have a process that needs to wait for all three of those processes to run, and needs to run by itself, and then it's followed by two more processes that can run concurrently once that process completes.

You could have the first three processes take the UL enqueue in 'S' (shared) mode, and they will all be able to run concurrently. Then run the process that needs to run by itself, but at the beginning of the code, have it take the UL enqueue in 'X' (exclusive) mode. That process will wait for the three processes holding enqueue in shared mode to complete. Now, you can also run the last two processes, again, with shared mode. They will queue behind the process that's requesting exclusive mode locks, and everything runs in the order you want.

That's a simple example. With more than one UL type lock, and multiple modes that locks can be held in, your processes and locking strategy may be arbitrarily complex.

Hope that helps.

Upvotes: 4