1

We have a process that aggregates some data and inserts the results into another table that we use for efficient querying. The problem we're facing is that we now have multiple aggregators running at roughly the same time.

We use the original records id as the primary key in this new table - a unique constraint. However, if two aggregation processes are running at the same time, one of them will error with a unique constraint violation.

Is there a way to specify some kind of locking mechanism which will make the second writer wait until the first is finished? Alternatively, is there a way to tell oracle to ignore that specific row and continue with the rest?

Unfortunately it's not practical to reduce the aggregation to a single process, as the following procedures rely on an up to date version of the data being available and those procedures do need to scale out.

Edit:

The following is my [redacted] query:

INSERT INTO
agg_table
SELECT
h.id, h.col, h.col2
FROM history h
JOIN call c
ON  c.callid = h.callid
WHERE
h.id > (SELECT coalesce(max(id),0) FROM agg_table)
Improve this question
5
  • Have you considered using statement level parallelism instead of multiple statements running concurrently? Commented Feb 18, 2013 at 5:42
  • Now that you've added the query, I don't understand what you gain from running two of them at the same time. Maybe you should figure out which ids need doing in one select, and then fire off parallel SQL statements for specific, non-overlapping ranges. Commented Feb 18, 2013 at 6:10
  • @WW. nothing is gained by running that statement in parallel. But the process that runs that sql needs to be parallel, and needs data as fresh as possible to do its job. Commented Feb 18, 2013 at 6:25
  • @jonearles statement level parallelism? Commented Feb 18, 2013 at 6:25
  • For example, using a parallel hint like insert /*+ parallel */. Then Oracle will do the parallelism for you. It's very easy to enable, but there are several tricky details, like locking, licensing (need EE), is it worth it (is the query large enough), etc. Commented Feb 18, 2013 at 14:54

3 Answers 3

Reset to default
3

It is possible run an INSERT statement with an error logging clause. The example from the Oracle docs is as follows:

INSERT INTO dw_empl
  SELECT employee_id, first_name, last_name, hire_date, salary, department_id 
  FROM employees
  WHERE hire_date > sysdate - 7
  LOG ERRORS INTO err_empl ('daily_load') REJECT LIMIT 25

Alternatively, you could try using a [MERGE][2] statement. You would be merging into the summary table with a select from the detail table. If a match is not found, you INSERT and if it is found you would UPDATE. I believe this solution will handle your concurrency issues, but you would need to test it.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yep, time to read up on how to use merge I think. Thanks for the pointer.
MERGE didn't help, I'm assuming because the transaction is too long. Will your LOG ERRORS solution just forget about the dupes and discard them?
0

have a look at FOR UPDATE clause. If you correctly write the SELECT statement with FOR UPDATE clause within a transaction before your update/insert statements you will be able to "lock" the required records

Improve this answer

Comments

0

Serialising the inserts is probably the best way, as there's no method that will get you round the problem of the multiple inserts being unable to see what each one is doing.

DBMS_Lock is probably the appropriate serialisation mechanism.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.