0

My question is if there's an atomic insert-if-not-exist statement in Oracle Database? This is not part of ANSI SQL so each database vendor has his own way of doing this, if supported.

To qualify the statement must work under high concurrent load and never throw duplicate key error. The statement must either succeed (row inserted) or do nothing (PK value already exists).

I've done a bit of Google'ing and my temporary conclusion is that Oracle does not have support for this, so the only viable route is a try-insert-catch-and-ignore-dup-key-error. Really just wanted help in either verifying or rejecting this assumption.

2
  • Have you considered the MERGE command? It is the closest thing Oracle has to what you're describing. See here: docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/…
    – pmdba
    Aug 23, 2022 at 9:50
  • @pmdba. Sure but as you may know the MERGE command is not atomic. So it doesn't comply with my requirement. This is nothing unique to Oracle, the MERGE command is merely some syntactical sugar, it doesn't protect against concurrent INSERTs, although some databases allow to give some locking hints along with the MERGE .. which will achieve the goal, albeit clunky. If Oracle has such a feature then feel free to write it in an answer.
    – peterh
    Aug 23, 2022 at 10:44

1 Answer 1

1

If 2 transactions try to MERGE a new value, the second one will get an ORA-00001 after the first one commits.

Session 1:

SQL> create table t1 (id number not null, constraint t1_pk primary key(id));

Table created.

SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id) 
     when not matched then insert (id) values (s.id);

1 row merged.

SQL>

Session 2 (waits for Session 1):

SQL> merge into t1 using (select 1 as id from dual) s on (t1.id = s.id) 
     when not matched then insert (id) values (s.id);

Session 1:

SQL> commit;

Commit complete.

SQL>

Session 2:

merge into t1 using (select 1 as id from dual) s on (t1.id = s.id)
when not matched then insert (id) values (s.id)
*
ERROR at line 1:
ORA-00001: unique constraint (BP.T1_PK) violated


SQL>

However, Oracle provides the IGNORE_ROW_ON_DUPKEY_INDEX hint, which will suppress the ORA-00001 and the second transaction can finish without an error and it will not insert the offending row(s).

Session 1:

SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);

1 row created.

SQL>

Session 2 (waiting for Session 1):

SQL> insert /*+ ignore_row_on_dupkey_index(t1,t1_pk) */ into t1 values (2);

Session 1:

SQL> commit;

Commit complete.

SQL>

Session 2:

0 rows created.

SQL>
1
  • Thanks for sharing. Good one. I'm my case I don't know the name of the index (table is created by another party), but I do know that there's a PK and I know the column name. So I would use the form of the hint where you specify the column name(s), rather than the index name.
    – peterh
    Aug 24, 2022 at 16:56

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.