CODEWITHSUNDEEP
oracle-databaselockingduplicates
Josh Smeaton
Josh Smeaton

Reputation: 48720

How to insert while avoiding unique constraints with oracle

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)

Upvotes: 1

Views: 4071

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

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.

Upvotes: 0

cha
cha

Reputation: 10411

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

Upvotes: 0

WW.
WW.

Reputation: 24291

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.

Upvotes: 3

Related Questions